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, with the 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)

pmtloan01

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)

pmtloan03

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.

pmtloan04

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.

pmtloan05

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.

_________________

1 comment to Calculate Loan Payments with PMT Function

  • OmarF

    A non-zero future value and type of "1" are how you calculate lease payments. The future value is the lease residual, and lease payments are always due at the start of the month.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>