Question

Consider a person who begins contributing to a retirement plan at age 25 and contributes for 40 years until retirement at age 65. For the first ten years, she contributes $3,000 per year. She increases the contribution rate to $5,000 per year in years 11 through 20. This is followed by increases to $10,000 per year in years 21 through 30 and to $15,000 per year for the last ten years. This money earns a 9 percent return.

Compute the annual payment she would receive over the next 40 years if the wealth was converted to an annuity payment at 8 percent. (Do not round intermediate calculations and round your final answer to 2 decimal places.)

PLEASE SHOW STEP BY STEP HOW YOU ENTERED THE FORMULAS INTO EXCEL! I WOULD LIKE TO LEARN HOW TO DO THIS, AND I HAVE TO SUBMIT AN EXCEL SHEET WITH THE ANSWERS.

Thank you :)

Answer 1

Value of retirement plan at age of 65:

r = interest rate i.e. 9%

Contribution is to be made at the beginning of year, therefore t= time period i.e. (65 –age +1)

Age	Contribution (C)	Future value factor (F) = (1+r)t	FV =(C)*(F)
26	3000	31.41	94228.26
27	3000	28.82	86447.95
28	3000	26.44	79310.04
29	3000	24.25	72761.51
30	3000	22.25	66753.68
31	3000	20.41	61241.90
32	3000	18.73	56185.23
33	3000	17.18	51546.09
34	3000	15.76	47289.99
35	3000	14.46	43385.31
36	5000	13.27	66338.39
37	5000	12.17	60860.91
38	5000	11.17	55835.70
39	5000	10.25	51225.41
40	5000	9.40	46995.79
41	5000	8.62	43115.40
42	5000	7.91	39555.42
43	5000	7.26	36289.37
44	5000	6.66	33293.00
45	5000	6.11	30544.04
46	10000	5.60	56044.11
47	10000	5.14	51416.61
48	10000	4.72	47171.20
49	10000	4.33	43276.33
50	10000	3.97	39703.06
51	10000	3.64	36424.82
52	10000	3.34	33417.27
53	10000	3.07	30658.05
54	10000	2.81	28126.65
55	10000	2.58	25804.26
56	15000	2.37	35510.46
57	15000	2.17	32578.40
58	15000	1.99	29888.44
59	15000	1.83	27420.59
60	15000	1.68	25156.50
61	15000	1.54	23079.36
62	15000	1.41	21173.72
63	15000	1.30	19425.44
64	15000	1.19	17821.50
65	15000	1.09	16350.00

Value of retirement plan at age of 65: Sum of FV = $1,763,650.15

Retirement value	1,763,650.15
Rate of interest	8%
Period	40 years

Annuity factor can be calculated using below formula:

Annuity factor = (1/r)*{1-(1/ (1+r)^t)}

Where, r = interest rate

t = total number of payments

Annual payment = Retirement value / Annuity factor

Annual Payment= $1,763,650.15 / (1/8%)*{1-(1/ (1+8%)⁴⁰)}

Annual Payment = $147899.99