Vertex42 The Excel Nexus

An ordinary, or deferred, annuity consists of a series of equal payments made at the end of each period. If you deposit $100 at the end of each year for three years in a savings account that pays 5 percent interest per year, how much will you have at the end of three years? To answer this question, we must find the future value of the annuity, FVAn. Each payment is compounded out to the end of Period n, and the sum of the compounded payments is the future value of the annuity, FVAn.

Time Line:

100 105

Here we show the regular time line as the top portion of the diagram, but we also show how each cash flow is compounded to produce the value FVAn in the lower portion of the diagram.

Equation:

The first line of Equation 2-4 represents the application of Equation 2-1 to each individual payment of the annuity. In other words, each term is the compounded amount of a single payment, with the superscript in each term indicating the number of periods during which the payment earns interest. For example, because the first annuity payment was made at the end of Period 1, interest would be earned in Periods 2 through n only, so compounding would be for n — 1 periods rather than n periods. Compounding for the second payment would be for Period 3 through Period n, or n — 2 periods, and so on. The last payment is made at the end of the annuity's life, so there is no time for interest to be earned.

The second line of Equation 2-4 is just a shorthand version of the first form, but the third line is different—it is found by applying the algebra of geometric progressions. This form of Equation 2-4 is especially useful when no financial calculator is available. Finally, the fourth line shows the payment multiplied by the Future Value Interest Factor for an Annuity (FV!FAi,n), which is the shorthand version of the formula.

1. NUMERICAL SOLUTION:

The lower section of the time line shows the numerical solution, which involves using the first line of Equation 2-4. The future value of each cash flow is found, and those FVs are summed to find the FV of the annuity, $315.25. If a long annuity were being evaluated, this process would be quite tedious, and in that case you probably would use the form of Equation 2-4 found on the third line:

$100

2. FINANCIAL CALCULATOR SOLUTION

Output:

Note that in annuity problems, the PMT key is used in conjunction with the N and I keys, plus either the PV or the FV key, depending on whether you are trying to find the PV or the FV of the annuity. In our example, you want the FV, so press the FV key to get the answer, $315.25. Since there is no initial payment, we input PV = 0.

3. SPREADSHEET SOLUTION

A |
B |
C |
D |
E | |

1 |
Interest rate |
0.05 | |||

2 |
Time |
0 |
1 |
2 |
3 |

3 |
Cash flow |
100 |
100 |
100 | |

4 |
Future value |
315.25 |

Most spreadsheets have a built-in function for finding the future value of an annuity. In Excel, we could put the cursor on Cell E4, then click the function wizard, Financial, FV, and OK to get the FV dialog box. Then, we would enter .05 or B1 for Rate, 3 or E2 for Nper, and -100 for Pmt. (Like the financial calculator approach, the payment is entered as a negative number to show that it is a cash outflow.) We would leave Pv blank because there is no initial payment, and we would leave Type blank to signify that payments come at the end of the periods. Then, when we clicked OK, we would get the FV of the annuity, $315.25. Note that it isn't necessary to show the time line, since the FV function doesn't require you to input a range of cash flows. Still, the time line is useful to help visualize the problem.

Was this article helpful?

Do you have annuity you dont want? Discover When is it Time to Sell Your Annuity? What can I do? Where can I get the money I need? I have an annuity, but I dont know that I can sell it. Is there a good time to sell my annuity? I already have a home improvement loan, but it was used before the roof needed replacing.

## Post a comment