PMT Function
- PMT
- The Excel PMT function calculates the payment for a loan based on constant
payments and a constant interest rate.
Syntax: PMT(rate, nper, pv, fv, type)
Rate is the interest rate per period for the loan.
Nper is the total number of payments.
Pv is the present value or principal.
Fv is the future value and is assumed to be zero if omitted.
Type indicates when payments are due. Omitted or zero if payments are made at the end of the period, 1 if at the beginning.
Function in cell B10: =-PMT(B12/B14,B15,B11)
The negative following the equal sign is used to negate the functions normal negative value so that the payment displays as a positive number. Because the rate argument requires the interest rate for the period, we must divide the annual rate by the number of periods per year, so the rate argument is B12/B14. The second argument is B15, the total number of periods for the life of the loan. The third argument is the amount of the loan. Because this loan will be paid down to zero and payments are made at the end of the month, the last two arguments are not required.
-
Function in cell B18: =-PMT(B20/B22,B23,B19,B24)
Rate, nper, and pv are as above, but because this loan has a lump sum payment due at the end of the loan, we have added the fv argument to contain that value. -
Function in cell B27: =-PMT(B29/B31,B32,0,B28)
This time we are depositing money to reach a savings goal, so the pv is 0 and the fv is the amount we want to have in savings at the end of the given number of years, in this case, the value in cell B28.
Function in cell B=-PMT(((1+(B37/2))^2)^(1/12)-1,B40,B36)
Canadians have this complicated compounded semi-annually thing so we can't just divide by the number of periods but have to use this fancier calculation for the rate argument:
((1+(B37/2))^2)^(1/12)-1
Compared to our first loan calculation, you can see that it actually saves them a bit, so is probably worth the extra work.
Download the sample workbook functionsamples.xls or for 2007 users functionsamples07.xls.
