Effect of Social Security when Retiring before Benefits Begin

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
User avatar
#Cruncher
Posts: 2581
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Effect of Social Security when Retiring before Benefits Begin

Post by #Cruncher » Wed Jul 11, 2018 12:32 pm

If one plans to begin collecting Social Security benefits at the same time one retires, it's relatively easy to determine how much SS will affect the savings needed to fund a desired level of spending. Simply subtract the SS benefit from desired spending. The remaining amount is all that need be funded. E.g., if one needs $60,000 per year and SS is $35,000, one only needs to fund the residual $25,000. With the 4% rule of thumb safe withdrawal rate (SWR) this means one would need a portfolio balance of $625,000 (25000 / 0.04). [This can also be seen on the bottom row of the table below where one retires at age 70, the same age SS is selected to begin. The "Annual SS @ 70" ($43,417) plus the "Spending w/o SS" ($127,286) equals the "Spending with SS" ($170,704).]

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%
And here is the example:

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
Given the assumptions in cells B1:B11 if one wants at least $60,000 per year to spend, one must work until age 60 if SS is ignored ($61,507). But if SS is included one can retire five years sooner at age 55 ($63,312). Withdrawing that amount from the $1,249,377 portfolio balance will leave one with $586,612 when the $37,120 SS benefit kicks in 15 years later at age 70. From that point on one need only withdraw the residual $26,192 from the portfolio each year (63312 - 37120). This will cause the portfolio balance to fall to $0 at age 100, the assumed "Die age".

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.)
  1. 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.
  2. Age Retire
    =B$4+A14
  3. AIME (Average Indexed Monthly Earnings)
    =MIN(35,A14)*B$3/35/12
  4. 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)
  5. 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)
  6. 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)
  7. Savings Grow to Value of Starting portfolio and annual savings at Retire Age
    =FV(B$10,A14,-B$9,-B$8,0)
  8. 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)
  9. 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))
  10. 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)
Notes:

User avatar
David Jay
Posts: 5170
Joined: Mon Mar 30, 2015 5:54 am
Location: Michigan

Re: Effect of Social Security when Retiring before Benefits Begin

Post by David Jay » Wed Jul 11, 2018 12:46 pm

Thanks.

For simplicity, I have always used 100% of expenses from retirement to start of SS (i.e. I need to have the actual dollars for those years in my portfolio), then 4% after start of SS. I will use your work to build something more capable.
Prediction is very difficult, especially about the future - Niels Bohr | To get the "risk premium", you really do have to take the risk - nisiprius

User avatar
Peter Foley
Posts: 4376
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

Re: Effect of Social Security when Retiring before Benefits Begin

Post by Peter Foley » Wed Jul 11, 2018 12:59 pm

Interesting work. I appreciate your efforts with this spreadsheet and number crunching you have done.

One weakness to the approach you propose is that not all withdrawals from "savings needed" are equal. Assuming retirement at age 60 and a need for a couple of $100,000 per year. (I'm cherry pricking the numbers for illustration purposes.)

1. Withdrawal of $100K per year from a Roth would be sufficient.
2. Withdrawal only from taxable may or may not incur some capital gains.*
3. Withdrawals from a tax deferred account would certainly entail some taxes.
4. Carefully planned withdrawals from a mix of the accounts listed above could possibly avoid most federal income taxes.

* With some planning all capital gains might be tax free. However, that might entail holding more cash and bonds in taxable than is usually advised.

A potential solution to the above would be to calculate tax discounted savings.

User avatar
#Cruncher
Posts: 2581
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Effect of Social Security when Retiring before Benefits Begin

Post by #Cruncher » Wed Jul 11, 2018 4:38 pm

David Jay wrote:
Wed Jul 11, 2018 12:46 pm
For simplicity, I have always used 100% of expenses from retirement to start of SS (i.e. I need to have the actual dollars for those years in my portfolio), then 4% after start of SS.
Yes, that method is certainly simpler, David. But it understates the amount of supported spending given a positive real portfolio growth rate. For example, my example shows you can spend $63,312 per year from retirement at age 55. With the assumed 2% growth rate, the $1,249,377 portfolio balance at age 55 will fall to $586,612 at age 70 when SS begins.
586,612 = FV(2%, 15, 63312, -1249377, 0)
This balance will be just enough to fund the residual spending after SS (63312 - 37120) for 30 more years until age 100.
0 = FV(2%, 30, 63312 - 37120, -586612, 0)

A little algebra shows that your method would support only $54,434 of spending, almost $9,000 less than mine.

Code: Select all

432,867 = balance at 70      = 1249377 - 15 * 54434
 54,434 = spending w 4% rule =  432867 * 4% + 37120
Edited 11:50 PM to add the following:
I just realized, David, that your method solves for the portfolio balance given the spending; while mine does the reverse, solving for the spending given the portfolio balance. But my method can be recast to do what you want. Take the example case of retiring at age 55 with $37,120 SS to begin at age 70. Assume you want to be able to spend $60,000 per year from age 55 to 100.
  • Capitalize the SS the same way as in the spreadsheet
    617,711 = -PV(2%, 100 - 70, 37120, 0, 0) / 1.02 ^ (70 - 55)
  • Then solve for the necessary portfolio balance as follows:
    1,151,700 = -PV(2%, 100 - 55, 60000, 0, 0) - 617711
  • (But your method would say you require a portfolio balance $320,000 higher.)
    1,472,000 = (60000 - 37120) / 0.04 + 15 * 60000

Peter Foley wrote:
Wed Jul 11, 2018 12:59 pm
One weakness to the approach you propose is that not all withdrawals from "savings needed" are equal.
That's certainly true, Peter. As you point out, the three different sources can each incur different income taxes. This, plus the complicated way that the taxable portion of SS is determined, is the reason I decided to ignore income taxes. It would have been more than I could handle.
Peter Foley in same post wrote:A potential solution to the above would be to calculate tax discounted savings.
Sounds intriguing. Could you explain what this means?
Last edited by #Cruncher on Wed Jul 11, 2018 10:52 pm, edited 2 times in total.

User avatar
Peter Foley
Posts: 4376
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

Re: Effect of Social Security when Retiring before Benefits Begin

Post by Peter Foley » Wed Jul 11, 2018 4:58 pm

#Cruncher

My terminology is probably imprecise. By tax discounted savings I meant to take one's tax deferred savings and discount it by one's combined federal and state tax rate. Because there is a bit of guesswork here one could use either the tax rate as calculated by tax software or one's marginal tax rate.

Example: If one were in the 12% federal marginal tax bracket prior to receiving SS benefits, a tax deferred account of $1,000,000 would be valued at $880,000. In the example you present with a portfolio balance of $625,000, if $500,000 were in a tax deferred account, one would need a balance of $125,000 + $568,000 ($500K/.88). There is no need to change your spreadsheet, one just has to understand the output with respect to where they have their retirement savings.

Some individuals calculate and manage their portfolio asset allocation based on this type of approach.

There are so many factors involved that I personally wouldn't bother doing this with a taxable account with substantial long term capital gains.

User avatar
#Cruncher
Posts: 2581
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Effect of Social Security when Retiring before Benefits Begin

Post by #Cruncher » Fri Jul 13, 2018 11:11 am

My first post showed the effect of SS benefits on the amount of spending supported by a given amount of savings. This one shows the reverse perspective: how much SS reduces the savings needed to support a given amount of spending.

Code: Select all

    Col A  Col B   Col C  Col D   Col E    Col F      Col G      Col H    Col I    Col J   Col K
    Years    Age                 Annual       PV  --Portfolio Needed--   -Annual Savings Needed-
Row  Work Retire    AIME    PIA SS @ 70    of SS     w/o SS    with SS   w/o SS  with SS    Diff

Code: Select all

 14    10     40   2,821  1,422  21,159  261,616  2,085,653  1,824,038  173,716  151,926  21,790 
 15    11     41   3,104  1,512  22,502  283,791  2,067,366  1,783,575  153,293  132,250  21,043 
 16    12     42   3,386  1,603  23,846  306,749  2,048,714  1,741,965  136,346  115,932  20,415 
 17    13     43   3,668  1,693  25,189  330,512  2,029,688  1,699,176  122,073  102,195  19,878 
 18    14     44   3,950  1,783  26,533  355,102  2,010,282  1,655,179  109,900   90,487  19,413 
 19    15     45   4,232  1,873  27,876  380,544  1,990,487  1,609,943   99,407   80,402  19,005
 
 20    16     46   4,514  1,964  29,219  406,862  1,970,297  1,563,435   90,279   71,637  18,642 
 21    17     47   4,796  2,054  30,563  434,080  1,949,703  1,515,623   82,275   63,957  18,318 
 22    18     48   5,079  2,144  31,906  462,224  1,928,697  1,466,473   75,206   57,183  18,024 
 23    19     49   5,361  2,235  33,250  491,320  1,907,271  1,415,951   68,926   51,171  17,756 
 24    20     50   5,643  2,283  33,971  512,022  1,885,416  1,373,395   63,316   46,121  17,195
 
 25    21     51   5,925  2,325  34,601  531,943  1,863,125  1,331,181   58,279   41,639  16,639 
 26    22     52   6,207  2,368  35,231  552,457  1,840,387  1,287,930   53,737   37,606  16,131 
 27    23     53   6,489  2,410  35,861  573,579  1,817,195  1,243,616   49,626   33,962  15,664 
 28    24     54   6,771  2,452  36,490  595,325  1,793,539  1,198,214   45,891   30,659  15,232 
 29    25     55   7,054  2,495  37,120  617,711  1,769,410  1,151,699   42,487   27,655  14,832 <===
 
 30    26     56   7,336  2,537  37,750  640,754  1,744,798  1,104,044   39,376   24,915  14,460 
 31    27     57   7,618  2,579  38,380  664,472  1,719,694  1,055,222   36,524   22,411  14,112 
 32    28     58   7,900  2,622  39,009  688,882  1,694,088  1,005,205   33,904   20,117  13,787 
 33    29     59   8,182  2,664  39,639  714,003  1,667,969    953,966   31,491   18,011  13,480 
 34    30     60   8,464  2,706  40,269  739,853  1,641,329    901,475   29,265   16,073  13,192
 
 35    31     61   8,746  2,749  40,898  766,452  1,614,155    847,703   27,207   14,288  12,919 
 36    32     62   9,029  2,791  41,528  793,819  1,586,438    792,620   25,301   12,641  12,660 
 37    33     63   9,311  2,833  42,158  821,974  1,558,167    736,194   23,534   11,119  12,415 
 38    34     64   9,593  2,876  42,788  850,937  1,529,331    678,394   21,892    9,711  12,181 
 39    35     65   9,875  2,918  43,417  880,730  1,499,917    619,187   20,365    8,407  11,958
 
 40    36     66   9,875  2,918  43,417  898,345  1,469,916    571,571   18,943    7,366  11,577 
 41    37     67   9,875  2,918  43,417  916,312  1,439,314    523,002   17,617    6,401  11,215 
 42    38     68   9,875  2,918  43,417  934,638  1,408,100    473,462   16,379    5,507  10,872 
 43    39     69   9,875  2,918  43,417  953,331  1,376,262    422,932   15,223    4,678  10,545 
 44    40     70   9,875  2,918  43,417  972,397  1,343,787    371,390   14,141    3,908  10,233
For example, to retire at age 55 without considering social security, one needs a portfolio of $1,769,410 to fund $60,000 of annual spending until age 100. To build this balance requires saving $42,487 per year for 25 years. But if one has SS indexed wages of $118,500 during those 25 years one will be entitled to a $37,120 monthly benefit beginning at age 70. When this is included only a $1,151,699 portfolio is needed to support the $60,000 spending. To build this smaller portfolio requires saving only $27,655 per year, $14,832 less than is required when SS is ignored.

Here are the assumptions. Only row 9 differs from the first post. Instead of assumed savings per year before retiring, it now has assumed spending per year after retiring.

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 spending             $60,000
 10  Real return before retire     4.00%
 11  Real return after retire      2.00%
Here are the formulas in row 14 to copy down to the last row. The ones in columns A:F are unchanged from my first post.
  1. 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.
  2. Age Retire
    =B$4+A14
  3. AIME : (Average Indexed Monthly Earnings)
    =MIN(35,A14)*B$3/35/12
  4. 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)
  5. 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)
  6. 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)
  7. Portfolio Needed w/o SS : Balance required to retire if SS benefits are ignored
    =-PV(B$11,B$7-B14,B$9,0,0)
  8. Portfolio Needed with SS : Balance required to retire if SS benefits are included
    =G14-F14
  9. Annual Savings Needed w/o SS : Annual amount that will grow to Portfolio Needed w/o SS
    =-PMT($B$10,$A14,-$B$8,G14,0)
  10. Annual Savings Needed with SS : Annual amount that will grow to Portfolio Needed with SS
    =-PMT($B$10,$A14,-$B$8,H14,0)
  11. Annual Savings Needed Diff : Reduction in annual savings when SS is included
    =I14-J14

Post Reply