Using Excel to Calculate Annuity

An annuity refers to a series of equal cash flows that occur periodically such as monthly or annually. For example, an investment that gives you fixed monthly payments for a specified period. There are two types of annuities, namely, regular annuities and annuities due. In a regular annuity, the first cash flow occurs at the end of the first period, and in an annuity due, the first cash flow occurs at the beginning (at time 0).

We can use time value of money functions in Excel to calculate both regular annuity and annuity due.

The common variables in these formulas are:

  • rate is the periodic interest rate
  • nper is the number of payments
  • pv is the initial principal or the present value
  • fv refers to future value.
  • type is whether the annuity is a regular or an annuity due. Use 0 for regular annuities, and 1 for annuity due. By default type is 0.
  • Any variable given in squared brackets is optional.

There are five key functions to calculate different aspects of an annuity:

=PMT( rate, nper, pv, [fv], [type])This function calculates the periodic payments in an annuity.
=PV( rate, nper, pmt, [fv], [type])This function calculates the present value of an annuity, once we have the periodic payments.
=FV( rate, nper, pmt, [pv], [type])This function calculates the present value of an annuity, once we have the periodic payments.
=NPER( rate, pmt, pv, [fv], [type])This function calculates the number of payments in an annuity.
=RATE( nper, pmt, pv, [fv], [type],[guess])This function calculates the interest rate earned in the annuity. Guess is your guess for what the rate will. By default its value is 0.10.

Example 1

Let’s take an example to understand this. Let’s say that an investment pays you $100 per month for the next 10 years. If this annuity is actually paying 8% interest, how much principal do you have to pay for this.

Rate = 8% per annum or 8%/12 per month

PMT = $100

Nper = 10*12 = 120 months

The principal can be calculated using the PV formula:

\=PV(8%/12,120,100)

\=$8,242

So, if you make an investment of $8,242 in this annuity, it will pay you $100 per month for the next 10 years.

Note that we did not fill the valuye for [type] in the formula. This assumed that it’s a regular annuity. If it was an annuity due, the calculation would be:

\=PV(8%/12,120,100,0,0)

\=$8,297

Example 2

Let’s take another example. Let’s say you require $100,000 after five years for your daughter’s marriage. You know that you can earn a rate of 9% on your investments. You much money should you save and invest every month, so that at the end of 5 years it becomes $100,000.

Rate = 9% per annum or 9%/12 per month

Nper = 5*12 = 60 months

Future Value (FV) = $100,000

We can use the PMT function to calculate the monthly payments/investments.

\=PMT(9%/12, 60, 0, 100,000)

\=$1,325.84

You should invest $1,325.84 every month for the next 5 years at 9% interest rate to have $100,000 at the end of the period.

Membership
Learn the skills required to excel in data science and data analytics covering R, Python, machine learning, and AI.
I WANT TO JOIN
JOIN 30,000 DATA PROFESSIONALS

Free Guides - Getting Started with R and Python

Enter your name and email address below and we will email you the guides for R programming and Python.

Saylient AI Logo

Take the Next Step in Your Data Career

Join our membership for lifetime unlimited access to all our data analytics and data science learning content and resources.