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: