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.
