For business users of Microsoft Excel Free guides and templates

Excel Financial Functions

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.  

by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

“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…


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

Excel's PMT function

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)

(You can download a workbook with working examples of these functions here.)

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.

(Again, you can download a workbook with working examples of the five functions here.)

Tags: #excel, #Annuity Functions, #PMT, #PMT function, #RATE, #RATE function, #NPER, #NPER function, #PV, #PV function, #FV, #FV function

How to Use Excel Formulas to Calculate a Term-Loan Amortization Schedule

Find Future and Present Values from Scheduled Cash Flows in Excel

Free Excel Dashboards

Charley's SwipeFile charts