Microsoft Office Tutorials and References

In Depth Information

**Using the PMT Function**

where
rate
is the interest rate for each payment period,
nper
is the total number of

payment periods required to pay off the loan, and
pv is the present value of the loan or

the amount that needs to be borrowed. For Diane and Glenn, the present value of the

proposed loan is $215,000.

The PMT function has two optional arguments:
fv and
type
. The
fv argument is the

future value of the loan. Because the intent with most loans is to pay them off

completely, the future value is equal to 0 by default. The
type
argument speciﬁ es when the

interest is charged on the loan, either at the end of the payment period (
type
=0), which is

the default, or at the beginning of the payment period (
type
=1).

For most mortgages, the payment period is one month. This means that Diane and

Glenn must make a payment on the loan every month, and interest on the loan is

compounded every month. The annual interest rate on home loans in Diane and Glenn’s area

is 6.0 percent. To determine the interest rate per month, you divide the annual interest

rate by 12. For Diane and Glenn, the interest rate each month is 6 percent divided by 12,

or about 0.5 percent per month.

Diane and Glenn want to pay off their home loan in 20 years, or 240 months (20 years

multiplied by 12 months per year). Putting all of this information together, you can

calculate the monthly payment for the couple’s home loan with the following formula:

=PMT(0.06/12, 20*12, 215000)

This formula returns a value of −$1,540.33. The value is negative because the payment is

considered an expense, which Excel treats as a negative value. If you want to display this

value as a positive number in a worksheet, enter a minus sign directly before the PMT

function as follows:

=−PMT(0.06/12, 20*12, 215000)

Based on these calculations, Diane and Glenn would have to pay the bank $1,540.33

every month for 20 years before the loan and the interest are completely paid. Right

now, the couple is spending about $1,050 per month on rent. So this home loan would

be a signiﬁ cant increase over their current expenses.

Diane wants you to calculate the monthly payment for a home loan under other

possible scenarios. You’ll start by examining the costs associated with a $200,000 loan.

To create a loan payment worksheet:

◗

1.
Go to the
Loan Analysis
worksheet. You will do the loan calculations in this

worksheet.

◗

2.
In cell B3, enter
6.00%
. This is the annual interest rate for the loan.

◗

3.
In cell B4, enter
for the number of payments per year.
12

◗

4.
In cell B5, enter the formula
=B3/B4
to calculate the interest per period, which is

0.50% per month.

◗

5.
In cell B6, enter
for the length of the mortgage in years.
20

◗

6.
In cell B7, enter the formula
=B4*B6
to calculate the total number of monthly

payments, which is 240.

◗

7.
In cell B8, enter
$200,000
for the amount of the loan. All the values for the loan

are entered.