Calculate Loan Payments with PMT Function

Even if you’re not a financial wizard, you’ve probably had to figure out a loan payment at least once in your life. Fortunately, Excel makes it easy to calculate loan payments, with the PMT function.

Excel PMT Function

Here is the argument syntax for the Excel PMT function:

• PMT(rate,nper,pv,[fv],[type])

If you have used this function, you probably just used the first three arguments – rate, nper, pv – and ignored the optional ones – fv and type.

In the example below, I entered the annual rate (rate), number of payments (nper), and loan amount (pv) on the worksheet, then referred to those cells in the PMT formula.

=PMT(C2/12,C3,C4)

What is the Future Value?

Do you ever use the 4th argument – future value (fv)? That’s the amount that you want as a balance when the payments are finished. In most cases, you want the balance to be zero, and if you omit the fv argument, that is the assumption.

However, maybe you borrowed $10,000 from a very generous relative. Instead of paying back the full amount, Grandma said that you only have to pay back $8000. In that case, the future value would be –2000.

When I add that to the formula, the monthly payment is reduced by almost $40.

=PMT(C2/12,C3,C4,-2000)

It’s not the same thing as paying back an $8000 loan though – you’ve borrowed $10000, and are paying interest on that amount. In the screen shot below you can see that an $8000 loan, with zero future value, would have lower payments.

The Type Argument

The final argument is type, and if you omit it, the payment is assumed to be at the end of the period. If you are paying at the beginning of the period, use a 1 as the type argument.

In the original example, paying for a $10000 loan over 48 months, at 5%, it would shave about $1 off the monthly payments.

Video: PMT Function

To see how to set up a simple PMT function, watch this short video. You can download the sample file from my Contextures website.

_________________