The Excel PMT function calculates the payment for a loan with a fixed interest rate. The function also assumes constant payments will be made. The PMT function is an Excel financial function.
=PMT(rate, nper, pv, [fv], [type])
rate = the interest rate
nper = the number of periods (term of the loan, e.g., years, months)
pv = present value, the loan or principle amount
fv = [optional] the future value of the loan. If left blank it will default to 0.
Note: the rate and nper inputs should be aligned, meaning that if the nper is entered in years, the rate should be an annual interest rate.
The PMT function returns the loan payment amount for a fixed rate loan with constant payments. The payment amount return is for the period defined by the nper argument.
For example, if the nper is in years, then the payment will be an annual payment calculation. If the nper is in months, then the payment will a monthly payment.
In the example above the PMT function calculates the annual payment for a loan with the terms listed in cells C5, C7, and C9. In this example, the loan amount is $3,000 for 4 years at a 5% fixed interest rate.
=PMT(C5, C7, -C9)
Note: A minus sign “-” is entered before the pv argument (C9) because the present value is negative (a loan). If a minus sign is not entered the PMT function will still work but the result will return a negative payment.
The result returned is $846, which is the annual payment.