But the situation is more complicated when retiring before starting SS. In addition to funding the residual requirement after SS begins, one must fund the entire spending requirement for the period until starting SS. I've constructed a spreadsheet that shows one way to do this. The key is adding to the accumulated savings the capitalized value of the future stream of SS benefits. One can then calculate on this combined balance how much one can spend so that the portfolio will last unti a designated "Die age". Here are my assumptions for the spreadsheet example:
Code: Select all
Row Col A Col B
1 SS bend point 90% to 32% $895
2 SS bend point 32% to 15% $5,397
3 Annual salary $118,500
4 Age start work 30
5 Normal Returement Age 67
6 Age claim benefits 70
7 Die age 100
8 Starting portfolio value $0
9 Annual savings $30,000
10 Real return before retire 4.00%
11 Real return after retire 2.00%
Code: Select all
Col A Col B Col C Col D Col E Col F Col G Col H Col I Col J
Years Age Annual PV Savings -- Spending ---
Row Work Retire AIME PIA SS @ 70 of SS Grow to w/o SS with SS FV @ 70
Code: Select all
14 10 40 2,821 1,422 21,159 261,616 360,183 10,362 16,082 0
15 11 41 3,104 1,512 22,502 283,791 404,591 11,742 18,521 0
16 12 42 3,386 1,603 23,846 306,749 450,774 13,202 21,182 0
17 13 43 3,668 1,693 25,189 330,512 498,805 14,745 24,089 0
18 14 44 3,950 1,783 26,533 355,102 548,757 16,379 26,977 9,957
19 15 45 4,232 1,873 27,876 380,544 600,708 18,107 29,578 38,125
20 16 46 4,514 1,964 29,219 406,862 654,736 19,938 32,328 69,622
21 17 47 4,796 2,054 30,563 434,080 710,925 21,878 35,236 104,668
22 18 48 5,079 2,144 31,906 462,224 769,362 23,934 38,314 143,498
23 19 49 5,361 2,235 33,250 491,320 830,137 26,115 41,571 186,369
24 20 50 5,643 2,283 33,971 512,022 893,342 28,429 44,723 240,804
25 21 51 5,925 2,325 34,601 531,943 959,076 30,886 48,017 300,464
26 22 52 6,207 2,368 35,231 552,457 1,027,439 33,496 51,508 364,541
27 23 53 6,489 2,410 35,861 573,579 1,098,537 36,271 55,210 433,354
28 24 54 6,771 2,452 36,490 595,325 1,172,478 39,223 59,139 507,252
29 25 55 7,054 2,495 37,120 617,711 1,249,377 42,366 [63,312] 586,612 <===
30 26 56 7,336 2,537 37,750 640,754 1,329,352 45,714 67,748 671,852
31 27 57 7,618 2,579 38,380 664,472 1,412,526 49,283 72,466 763,424
32 28 58 7,900 2,622 39,009 688,882 1,499,027 53,091 77,490 861,829
33 29 59 8,182 2,664 39,639 714,003 1,588,989 57,159 82,843 967,615
34 30 60 8,464 2,706 40,269 739,853 1,682,548 [61,507] 88,553 1,081,389 <===
35 31 61 8,746 2,749 40,898 766,452 1,779,850 66,159 94,649 1,203,820
36 32 62 9,029 2,791 41,528 793,819 1,881,044 71,142 101,165 1,335,648
37 33 63 9,311 2,833 42,158 821,974 1,986,286 76,485 108,137 1,477,697
38 34 64 9,593 2,876 42,788 850,937 2,095,737 82,222 115,606 1,630,881
39 35 65 9,875 2,918 43,417 880,730 2,209,567 88,388 123,619 1,796,224
40 36 66 9,875 2,918 43,417 898,345 2,327,949 95,024 131,693 1,977,060
41 37 67 9,875 2,918 43,417 916,312 2,451,067 102,176 140,374 2,171,491
42 38 68 9,875 2,918 43,417 934,638 2,579,110 109,897 149,723 2,380,866
43 39 69 9,875 2,918 43,417 953,331 2,712,274 118,245 159,807 2,606,713
44 40 70 9,875 2,918 43,417 972,397 2,850,765 127,286 170,704 2,850,765
For those who wish to construct the spreadsheet to use with differenct assumptions, here are the formulas to enter in row 14 and copy down to the bottom row. (Some of the formulas use the Excel FV, PMT, and PV functions.)
- Years Work (not a formula) Enter the possible number of years to consider working beginning in row 14. Ten is the minimum to qualify for SS benefits.
- Age Retire
=B$4+A14 - AIME (Average Indexed Monthly Earnings)
=MIN(35,A14)*B$3/35/12 - PIA (Primary Insurance Amount) The monthly benefit if claim at Normal Retirement Age (NRA)
=90%*MIN($B$1,C14)+32%*MAX(0,MIN($B$2,$C14)-$B$1)+15%*MAX(0,C14-$B$2) - Annual SS @ [age claim] The PIA decreased or increased if one claims before or after NRA
=12*D14*IF(B$6<B$5,1-(5/900)*MIN(36,(B$5-B$6)*12)-(5/1200)*MAX(0,(B$5-B$6)*12-36),1+(8/1200)*(B$6-B$5)*12) - PV of SS Present value at Retire Age of stream of future SS benefits
=PV(B$11,B$7-B$6,-E14,0,0)/(1+B$11)^(B$6-B14) - Savings Grow to Value of Starting portfolio and annual savings at Retire Age
=FV(B$10,A14,-B$9,-B$8,0) - Spending w/o SS Amount that could be spent every year starting at Retire age if SS benefits are ignored
=PMT(B$11,B$7-B14,-G14,0,0) - Spending with SS Amount that can be spent every year starting at Retire age until the Die age if SS benefits are included.
=MIN(PMT(B$11,B$7-B14,-F14-G14,0,0),PMT(B$11,MAX(1,B$6-B14),-G14,0,0)) - FV @ [age claim] Future value of portfolio at age begin collecting SS. If this is zero it means that the Spending with SS amount is less than the SS benefit and the portfolio balance will increase after SS begins.
=FV(B$11,B$6-B14,I14,-G14,0)
- The spreadsheet does all calculations in "real" terms. i.e., in constant dollars. So for example, the investment returns are assumed to be real returns.
- As a convenience the spreadsheet estimates the amount of the SS benefit. To do this it makes some simplifying assumptions.
- One earns the same amount in constant dollars every year while working. (Do not enter more than $118,500 since I think that is the maximum taxable earnings consistent with the bend points.)
- The indexes used to adjust past SS earnings will correspond to the change in inflation.
Bend points
Calculating AIME (Average Indexed Monthly Earnings)
Calculating PIA (Primary Insurance Amount) using AIME and bend points
Maximum taxable earnings
Normal Retirement Age (NRA) and reduction/addition to PIA for claiming SS before/after NRA