Question

With no taxes or inflation (Spreadsheet 21.1), what would be your retirement annuity if you decrease the ROR by 0.2% Retirement annuity

Answer 1

Answer:

Retirement Annuity 182,858

Working:

If RoR decreases by 0.2 %, new RoR will be = 6% - 0.2% = 5.8% = 0.058

8,587 Retirement Years Income Growth Saving Rate ROR 0.07 0.15 0.058 Age Income Savings Cumulative Savings Consumption 50,000 7,500 7,500 42,500 53,500 8,025 15,960 45,475 57,245 25,472 48,658 61,252 9,188 36,138 52,064 65,540 9,831 48,065 55,709 70,128 10,519 61,371 59,608 75,037 11,255 76,187 63,781 80,289 12,043 92,649 68,246 85,909 12,886 110,909 73,023 91,923 13,788 131,130 78,135 98,358 14,754 153,489 83,604 105,243 15,786 178,178 89,456 112,610 16,891 205,404 95,718 120,492 18,074 235,391 102,418 128,927 19,339 268,382 109,588 137,952 20,693 304,641 117,259 147,608 22,141 344,452 125,467 157,941 23,691 388,121 134,250 168,997 25,349 435,982 143,647 180,826 27,124 488,393 153,702 193,484 29,023 545,742 164,462 207,028 31,054 608,449 175,974 221,520 33,228 676,967 188,292 237,026 35,554 751,785 201,473 253,618 38,043 833,432 215,576 271,372 40,706 922,476 230,666 290,368 43,555 1,019,535 246,812 310,693 46,604 1,125,272 264,089 332,442 49,866 1,240,404 282,576 355,713 53,357 1,365,705 302,356 380,613 57,092 1,502,007 323,521 407,256 61,088 1,650,212 346,167 435,764 65,365 1,811,289 370,399 466,267 69,940 1,986,284 396,327 64 498,906 74,836 2,176,324 424,070 533,829 80,074 2,382,625 453,755 Total 7,445,673 1,116,851 Retirement Annuity | 182,858 65

Above excel with 'show formula' is as below (in 2 images):

C 1 Retirement Years Income Growth Saving Rate 2 25 0.07 0.15 3 Age Income Savings 4 30 50000 =B4*$C$2 5 31 =B4*(1+$B$2) =B5*$C$2 6 32 =B5*(1+$B$2) =B6*$C$2 7 33 =B6*(1+$B$2) =B7*$C$2 8 34 =B7*(1+$B$2) =B8*$C$2 9 35 =B8*(1+$B$2) =B9*$C$2 10 36 =B9*(1+$B$2) =B10*$C$2 11 37 =B10*(1+$B$2) =B11*$C$2 12 38 =B11*(1+$B$2) =B12*$C$2 13 39 =B12*(1+$B$2) =B13*$C$2 14 40 =B13*(1+$B$2) =B14*$C$2 15 41 =B14*(1+$B$2) =B15*$C$2 16 42 =B15*(1+$B$2) =B16*$C$2 17 43 =B16*(1+$B$2) =B17*$C$2 18 44 =B17*(1+$B$2) =B18*$C$2 19 45 =B18*(1+$B$2) =B19*$C$2 20 46 =B19*(1+$B$2) =B20*$C$2 21 47 =B20*(1+$B$2) =B21*$C$2 22 48 =B21*(1+$B$2) =B22*$C$2 23 49 =B22*(1+$B$2) |=B23*$C$2 24 50 =B23*(1+$B$2) |=B24*$C$2 =B24*(1+$B$2) =B25*$C$2 D ROR 0.06-0.2% Cumulative Savings =C4 =D4*(1+$D$2)+C5 =D5*(1+$D$2)+C6 =D6*(1+$D$2)+C7 =D7*(1+$D$2)+C8 =D8*(1+$D$2)+C9 =D9*(1+$D$2)+C10 =D10*(1+$D$2)+C11 =D11*(1+$D$2)+C12 =D12*(1+$D$2)+C13 =D13*(1+$D$2)+C14 =D14*(1+$D$2)+C15 =D15*(1+$D$2)+C16 =D16*(1+$D$2)+C17 =D17*(1+$D$2)+C18 =D18*(1+$D$2)+C19 =D19*(1+$D$2)+C20 =D20*(1+$D$2)+C21 =D21*(1+$D$2)+C22 =D22*(1+$D$2)+C23 =D23*(1+$D$2)+C24 =D24*(1+$D$2)+C25 Consumption =B4-C4 =B5-C5 =B6-C6 =B7-C7 =B8-C8 =B9-09 =B10-C10 =B11-C11 =B12-C12 =B13-C13 =B14-C14 =B16-C16 =B17-C17 =B18-C18 =B19-C19 =B20-C20 =B21-C21 =B22-C22 =B23-C23 =B24-C24 =B25-C25

25 51 26 52 27 53 29 55 30 56 31 57 32 58 33 59 34 60 =B24*(1+$B$2) =B25*$C$2 =D24*(1+$D$2)+C25 =B25-C25 =B25*(1+$B$2) =B26*$C$2 =D25*(1+$D$2)+C26 =B26-C26 =B26*(1+$B$2) |=B27*$C$2 =D26*(1+$D$2)+C27 =B27-C27 =B27*(1+$B$2) =B28*$C$2 =D27*(1+$D$2)+C28 =B28-C28 =B28*(1+$B$2) =B29*$C$2 =D28*(1+$D$2)+C29 =B29-C29 =B29*(1+$B$2) =B30*$C$2 =D29*(1+$D$2)+C30 =B30-C30 =B30*(1+$B$2) |=B31*$C$2 =D30*(1+$D$2)+C31 =B31-C31 =B31*(1+$B$2) =B32*$C$2 =D31*(1+$D$2)+C32 =B32-C32 =B32*(1+$B$2) =B33*$C$2 =D32*(1+$D$2)+C33 =B33-C33 =B33*(1+$B$2) =B34*$C$2 =D33*(1+$D$2)+C34 =B34-C34 =B34*(1+$B$2) |=B35*$C$2 =D34*(1+$D$2)+C35 =B35-C35 =B35*(1+$B$2) =B36*$C$2 =D35*(1+$D$2)+C36 =B36-C36 =B36*(1+$B$2)=B37*$C$2 =D36*(1+$D$2)+C37 =B37-C37 =B37*(1+$B$2) =B38*$C$2 =D37*(1+$D$2)+C38 =B38-C38 =B38*(1+$B$2) |=B39*$C$2 =D38*(1+$D$2)+C39 \=B39-C39 =SUM(B4:B39) |=B40*$C$2 Retirement Annuity =PMT($D$2,$A$2,-$D$39,0,0) 36 62 37 63 38 64 39 65 40 Total