If you project a rate of inflation of 4%, what nominal ROR on investments would you need to maintain the same real retirement annuity as in Spreadsheet 20.2?

Transcribed Image Text:

SPREADSHEET 20.2 A real retirement plan excel Please visit us at www.mhhe.com/bkm 1 2 3 4 5 39 40 A B C 1 Retirement Years Income growth Rate of Inflation 2 0.03 Deflator 3 4 5 9 19 29 39 40 A Retirement Years 25 Age 30 31 65 Total 25 Age 30 31 35 45 55 65 Total 0.07 Income B Income Growth 0.07 Income 50,000 53,500 70,128 137,952 271,372 533.829 7,445,673 C Rate of Inflation 0.03 Deflator D Savings Rate 0.15 Savings 50000 =B4*$D$2 =B4*(1+$B$2) =C4*(1+$C$2) =B5*$D$2 =B38*(1+$B$2) =C38*(1+$C$2) =B39*$D$2 =SUM(B4:B39) 1 1.00 1.03 1.16 1.56 2.09 2.81 D Savings rate 0.15 Saving F E ROR TROR 0.06 0.0291 Cumulative Savings rConsumption 7,500 15.975 61,658 308,859 943,477 7,500 8,025 10,519 20.693 40,706 80.0741 1,116,851 2,457,518 Real Annuity E ROR 0.06 Cumulative Savings =D4 =SUM(D4:D39) Real Annuity 42,500 44,150 51,419 75,264 110,167 161,257 49,668 F TROR rConsumption =(E2-C2)/(1+C2) =(B4-D4V/C4 (B5-D5)/C5 =E4*(1+$E$2)+D5 =E38*(1+$E$2)+D39 =(B39-D39)/C39 | =PMT($F$2,$A$2,-$E$39/$C$39,0,0)