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, 20052014
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…
=pmt(
…in a cell and then pressed Ctrl+Shift+A, which gave me the
results shown here:
And then, when I pressed Enter, Excel returned this formula
to the 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 10year 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…
 What do you owe today?
 What’s the periodic interest rate?
 What’s the number of remaining periods?
 What’s the ending value? (This typically is zero for a
loan.)
 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...
 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.)
 What’s the new periodic interest rate?
 What’s the number of remaining periods for your new savings
plan?
 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.)
 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


Related
How to Use Excel Formulas to Calculate a TermLoan Amortization Schedule
Find Future and Present Values
from Scheduled Cash Flows in Excel
