SPREADSHEET 21.2 A real retirement plan 1 2 3 4 5 A Retirement Years 25 Age 30 31 35 45 55 65 Total 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 1.00 1.03 1.16 1.56 2.09 2.81 D Savings Rate 0.15 Saving 7,500 8,025 10,519 20,693 40,706 80,074 1,116,851 E ROR 0.06 Cumulative Savings 7.500 15.975 61.658 308.859 943,477 2.457,518 Real Annuity F TROR 0.0291 Consumption 42.500 44,150 51.419 75.264 110.167 161.257 49,668 9 19 29 39 40 3 4 5 39 A Retirement Years 25 Age 30 31 65 B Income Growth Rate of Inflation 0.07 0.03 Income Deflator 50000 1 =B4*(1+$BS2) =C4*(1+$C$2) =B38*(1+$BS2)=C38*(1+$C$2) =SUM(B4:B39) D Savings Rate 0.15 Savings =B4*$D$2 =B5*$D$2 =B39*$D$2 E ROR 0.06 Cumulative Savings =D4 =E4*(1+$E$2)+D5 =E38*(1+$E$2)+D39 F TROR =(E2-C2/(1+C2) rConsumption =(B4-D4)/C4 1 =(B5-D5)/C5 =(B39-D39)/C39 =PMT(SF$2,SAS2,- SE$39/SC$39,0,0) 40 Total =SUM(D4:D39) Real Annuity 7 Required: a-1. With a 3.0% inflation rate Spreadsheet 21.2), by how much would your retirement annuity grow if you increase the savings rate by 1.1%? (Round your percentage answer to 2 decimal places.) 15 points Growth in retirement annuity 1.10% eBook Print References a-2. Is the benefit greater in the face of inflation? Yes O No SPREADSHEET 21.2 A real retirement plan 1 2 3 4 5 A Retirement Years 25 Age 30 31 35 45 55 65 Total 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 1.00 1.03 1.16 1.56 2.09 2.81 D Savings Rate 0.15 Saving 7,500 8,025 10,519 20,693 40,706 80,074 1,116,851 E ROR 0.06 Cumulative Savings 7.500 15.975 61.658 308.859 943,477 2.457,518 Real Annuity F TROR 0.0291 Consumption 42.500 44,150 51.419 75.264 110.167 161.257 49,668 9 19 29 39 40 3 4 5 39 A Retirement Years 25 Age 30 31 65 B Income Growth Rate of Inflation 0.07 0.03 Income Deflator 50000 1 =B4*(1+$BS2) =C4*(1+$C$2) =B38*(1+$BS2)=C38*(1+$C$2) =SUM(B4:B39) D Savings Rate 0.15 Savings =B4*$D$2 =B5*$D$2 =B39*$D$2 E ROR 0.06 Cumulative Savings =D4 =E4*(1+$E$2)+D5 =E38*(1+$E$2)+D39 F TROR =(E2-C2/(1+C2) rConsumption =(B4-D4)/C4 1 =(B5-D5)/C5 =(B39-D39)/C39 =PMT(SF$2,SAS2,- SE$39/SC$39,0,0) 40 Total =SUM(D4:D39) Real Annuity 7 Required: a-1. With a 3.0% inflation rate Spreadsheet 21.2), by how much would your retirement annuity grow if you increase the savings rate by 1.1%? (Round your percentage answer to 2 decimal places.) 15 points Growth in retirement annuity 1.10% eBook Print References a-2. Is the benefit greater in the face of inflation? Yes O No