Excel’s Five Annuity Functions

Most loans and many investments are annuities, which are payments made at fixed intervals over time. Here's how to use Excel to calculate any of the five key unknowns for any annuity.

53050

Most loans and many investments are annuities, which are payments made at fixed intervals over time. Here's how to use Excel to calculate any of the five key unknowns for any annuity.“Help!” the message said. “I know the payment, interest rate, and current balance of a loan, and I need to calculate the number of months it will take to pay it off. How do I do it in Excel?”

Honestly, I haven’t calculated the number of periods for quite a while. So here’s how I quickly found the worksheet functions for all five of Excel’s annuity calculations, including the one that my visitor needed…

Most anyone who works with loans and investments in Excel knows about the PMT function. So that’s where I started.

I typed…

=pmt(

…in a cell and then pressed Ctrl+Shift+A, which gave me the results shown here:

A quick way to see the arguments for Excel's PMT function: rate, nper, pv, fv, type.

And then, when I pressed Enter, Excel returned this formula to the cell:

Excel's PMT function entered in a cell

(Excel displayed the #NAME? error value because the names of the five arguments look to Excel like range names that haven’t been defined.)

Here’s what each argument means in this formula…

  • rate is the periodic interest rate. So if the annual interest rate is 6% and you make monthly loan payments, the periodic rate is 6% divided by 12, or .005.
  • nper is the number of periods. So if a 10-year loan has monthly payments, the nper argument would be 10 times 12, or 120 periods.
  • pv is the present value of the loan. So if you want to borrow $12,345.67, or if that’s what you currently owe, that’s your pv.
  • fv is the ending value of the loan. This typically is zero for a loan.
  • type is a code that indicates when payments are due. If you omit the type argument, or enter 0, it indicates that payments are due at the end of each period, which is typical. If you enter 1, it indicates that payments are due at the beginning of each period.

Similarly, if you want to know how much you should save monthly at an annual interest rate of 3% to accumulate $5,000 in 7 years, your formula would look something like this…

=PMT(3%/12, 7*12, 0, 5000)

…which gives you an answer of -$53.57. (Excel displays this number as a negative amount to indicate that you must pay the amount to receive the FV balance of $5,000.)

The PMT function won’t directly answer the question that my reader asked. But it does answer the question indirectly. This is because the names of the first four arguments for the PMT function also are the names of functions that calculate those values if you know the other four values.

In short, here are the five annuity functions:

  • =PMT(rate,nper,pv,fv,type)
  • =RATE(nper,pmt,pv,fv,type,guess)
  • =NPER(rate,pmt,pv,fv,type)
  • =PV(rate,nper,pmt,fv,type)
  • =FV(rate,nper,pmt,pv,type)

So if my reader currently owes $4,567.89 on a loan with an annual interest rate of 5.25%, and makes monthly payments of $251.07, he could calculate the remaining number of months using this formula:

=NPER(0.0525/12, -251.07, 4567.89, 0)

This formula returns a tiny amount greater than 19 periods, which represents the answer he was looking for.

In closing, notice that this formula uses an insight that is always useful to keep in mind about typical annuity calculations like this…

If you were to get a new loan for the current balance due on an existing loan, for the number of months remaining on the existing loan, and for the same interest rate, your payments would be the same as your payments on the existing loan.

Therefore, for an existing loan, the original amount and original number of periods on the loan don’t matter to your Excel calculations. The only things that matter are…

  1. What do you owe today?
  2. What’s the periodic interest rate?
  3. What’s the number of remaining periods?
  4. What’s the ending value? (This typically is zero for a loan.)
  5. What’s the periodic payment amount?

Similarly, if you’ve been making periodic deposits in a savings account, and you must change your savings plan, the details of your past plan don’t matter. Instead, the only things that matter to your new calculation are…

  1. What’s your current savings balance? (This is your PV, which you enter as a negative amount because you’re paying the amount into your new savings plan.)
  2. What’s the new periodic interest rate?
  3. What’s the number of remaining periods for your new savings plan?
  4. What ending amount do you plan to achieve? (This is your FV, which you enter as a positive amount because you’re taking it from your savings plan and putting it into your pocket.)
  5. What’s the periodic payment amount?

And if you know Excel’s five key annuity functions, you can answer either of these sets of questions if you know the other four values.