Annuity models

This lab concerns annuity payments. The goal is to build and use an annuity calculator. An account balance sheet is constructed to check the formula results.

Problem 1. A sample situation involving the Future Value of an Ordinary Annuity:
Fred Jones plans to deposit $1000 at the end of each quarter into an account that pays 8% annually, compounded quarterly. What will be the value of the account at the end of the 4th quarter (4th payment)? (If the deposit is made at the beginning of each quarter instead of at the end, this would be an Annuity Due problem.)
The answer to the question above is given by the future value of an ordinary annuity. A template to compute this value is constructed in steps (a)-(c). Be sure to distinquish between labels, input data, and the formulas that do the calculations.

a. Start with a clean worksheet and enter the following data. Steps (b) and (c) complete the template. Note that each of a series of deposits is called a "payment" (denoted 'Pmt' in cell A4).

  A B C D E F
1 Problem #1        
2 Annuity problem      
3            
4 Pmt 1000   period     Ending-balance
5 Rate/yr 0.08   1     =B4  
6 Period/yr 4   2     =E5+E5*$B$9+$B$4
7 Tot#Period 4   3    
8       4    
9 Rate/period =B5/B6  
10 Total amt  

b. The formula in cell E5 copies the deposit at the end of Quarter #1 from cell B4; the formula in E6 adds to the balance (E5) the interest on the balance together with another payment. Copy the formula in E6 to cells E7..E8 in order to determine the balance at the end of the remaining quarters.

c. In cell B10 we want to compute the total amount in cell E8 directly. Enter the formula =FV(B9,B7,-B4) in cell B10 and check that it gives the same value found in cell E8. NOTE the negative sign in the third argument. This is because Excel treats payments (out) as a negative cash flow. Try the formula without the negative sign and observe the difference.

Algebra practice: In cell F8 enter the formula for the Future Value of an Ordinary Annuity found in your notes or in the text using cells B4, B7 and B9. The result should be the same as found in cell E8. Why do you think the built-in formula FV is provided?

Discussion: In a textbox explain what information is given by the different approaches used to compute the values in cells B10 and E8. SAVE and PRINT the worksheet.

For Problem 2 you should start with a copy of the range A1..E10 on another Sheet.

PROBLEM 2. Repeat Problem 1 for five years (20 payments) instead of one year. Note that, as in Problem 1, you should get the same answer in three different ways (in three different cells).

Discussion: What do you observe about the new value in cell B10 and the bottom value in column E? Print the worksheet.

Now you are ready to become a financial consultant! Assume all annuities below are ordinary annuities. You should use the "annuity calculator" template (in the cells A4..B10) by coping the template to a new area, changing the input data, and adding appropriate explanations.

PROBLEM 3. a. Peter, who is 23 years old, intends to make monthly contributions of $200 into a tax-deferred retirement plan until he retires. The plan earns interest at an 9% annual rate, compounded monthly. What will be the future value (ordinary annuity) of Peter's retirement plan at age 65? Write your answer in sentence form. Include a copy of the template used to support your conclusion.

b. Peter's friend Paul waits until age 35 to begin contributing to his retirement plan. He contributes $250 a month into his retirement account that also pays interest at 9% a year compounded monthly . What will be the future value (ordinary annuity) of Paul's retirement plan at age 65? Write your answer in sentence form. Include a copy of the template used to support your conclusion.

c. How much more will Peter have in his retirement plan at age 65 than Paul will have? What is the basic economic lesson to be learned from these calculations?

PROBLEM 4. a. When Dick is 25 years old he starts making a $250 a month contribution into an IRA for 10 years. He then discontinues the contributions and allows the account to accumulate interest for the next 30 years. Find the total amount that Dick accumulates assuming that the annuity and the account both earn interest at 8% compounded monthly. Write your answer in sentence form.

b. Jane plans to make monthly contributions into an IRA for 30 years beginning at age 35. Assume her account earns interest at a 8% annual rate compounded monthly. Find the amount that Jane must contribute monthly in order to accumulate the same total amount that Dick will obtain. In a sentence or two give your answer and tell how you found it. Support your conclusion with a template that shows the calculations. Use the Excel Solver to find Jane's monthly contribution.

c. Write a short paragraph that contrasts the investment strategies of Dick and Jane. For example, compare the differences in contributions with the differences in total accumulation. Which strategy would you recommend? Why?