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 $800 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	800		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 the different approaches used to compute the values in cells B10 and E8. SAVE and PRINT the ANNUITY 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. 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 35 years old, intends to make monthly contributions of $400 into a fixed account until he retires at age 65 -- that's 360 months. The account earns interest at an 8% annual rate, compounded monthly. What will be the future value (ordinary annuity) of Peter's account when he retires? Write your answer in sentence form. Include a copy of the template used to support your conclusion.

b. Peter's friend Paul hears about Peter's good deal; he starts to contribute $400 a month into the same plan, but Paul starts one month later than Peter. How much less will Paul earn just because he only contributed for 359 months? Write your answer in sentence form.

c. Patty started contributing to the retirement account at age 25 and plans to contribute $200 monthly for 40 years (480 months). What is the future value of her account? Include a copy of the template used to support your conclusion. Write a short paragraph that describes Peter's and Patty's return on their investments. Who has the best deal?

PROBLEM 4. a. When Dick is 25 years old he starts making a $400 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 6% 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 6% 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.

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?