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
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