Question

Using Excel Please

5. Growing Annuity Assume you want to retire in 35 years and save a $15,000 at the end of the first year. Assume you will earn 7% annually on your investment and you expect 2% inflation before retirement. After you retired, you expect to live for another 30 years. Assume you can earn a nominal annual rate of 4% and you expect inflation to be 3% during retirement. a) Calculate how much money you are accumulating by the time you retire if you increase your annual savings by the inflation rate. b) Calculate your real annual withdrawals over the next 30 years so as to maintain a constant standard of living. Assume the first withdrawal is made at the beginning of the year.

Answer 1

a) Assuming Inflation of 7% , Inflation = 2%, First year savings = $15,000. Future Value for first year savings at the end of year 35 is $15,000*(1+7%)^(35-1) = $ 149,672. Calculating Future values similarly for all the years, total money accumulated at the time of retirement = $2,603,008

Year #	Savings	Future Value
Year 1	15,000	149,672
Year 2	15,300	142,678
Year 3	15,606	136,011
Year 4	15,918	129,655
Year 5	16,236	123,596
Year 6	16,561	117,821
Year 7	16,892	112,315
Year 8	17,230	107,067
Year 9	17,575	102,064
Year 10	17,926	97,294
Year 11	18,285	92,748
Year 12	18,651	88,414
Year 13	19,024	84,282
Year 14	19,404	80,344
Year 15	19,792	76,589
Year 16	20,188	73,011
Year 17	20,592	69,599
Year 18	21,004	66,347
Year 19	21,424	63,246
Year 20	21,852	60,291
Year 21	22,289	57,473
Year 22	22,735	54,788
Year 23	23,190	52,228
Year 24	23,653	49,787
Year 25	24,127	47,461
Year 26	24,609	45,243
Year 27	25,101	43,129
Year 28	25,603	41,113
Year 29	26,115	39,192
Year 30	26,638	37,361
Year 31	27,170	35,615
Year 32	27,714	33,951
Year 33	28,268	32,364
Year 34	28,833	30,852
Year 35	29,410	29,410
Total		2,603,008

b) As Real annual Rate of return ~ Nominal Annual Rate - Inflation i.e. real rate = 4%-3% = 1%. Present Value at the time of retirement = @2,630,008. Years = 30. Savings at the end of retirement life (FV) = 0. With the formula shown below, real value of annual withdrawals is calculated as $99,863.(Note:Adjustment for present value is taken as the withdrawal starts at the beginning of the year).

- X Fox --PMT(K5, K6, K7,K4)/(1+K5) L M NO K 3 4 FV 5 Rate 6 Years 7 PV 1% 30 2,603,008 8 9 Annual Consumption 99,863