Effect of Social Security when Retiring before Benefits Begin

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
User avatar
#Cruncher
Posts: 2648
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: 5435
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: 4495
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: 2648
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: 4495
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: 2648
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

Tamales
Posts: 1244
Joined: Sat Jul 05, 2014 10:47 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by Tamales » Wed Jul 25, 2018 10:56 pm

#Cruncher wrote:
Wed Jul 11, 2018 12:32 pm

[*]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))
#cruncher, thanks for sharing your spreadsheet info, and this is some interesting work. I missed it when you originally posted it and just happened across it while doing a forum search.

I’m trying to go through the mechanics of how it works (focusing on your first post for now, although the 2nd modifications seem straightforward). I’m not sure I’m following the formula in “I. Spending with SS.” In particular, what’s the purpose of using the MIN() function? I follow the first PMT() argument of the MIN function, but not the second one, or why you’d need to choose between the smaller of the two. Could you elaborate?

User avatar
siamond
Posts: 4154
Joined: Mon May 28, 2012 5:50 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by siamond » Wed Jul 25, 2018 11:14 pm

#Cruncher wrote:
Wed Jul 11, 2018 12:32 pm
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 until a designated "Die age".
Yes, indeed, I do exactly that with my own early retirement situation, and I helped a few friends do something similar. Having reviewed diverse situations though, it became clear that the issue is much larger than the future stream of SS though. It's really about the future cash flows at large, including:
- SS benefits
- pensions (which are often not or only partly inflation-adjusted)
- a few more years of work from one partner, or some part-time work
- one-time lump sums like inheritance, house downsizing, and more
- etc.

All of those are basically extra income, but (large) extra expenses should also be considered:
- long-term care costs down the road if self-insured
- a few years of transition with some late children still at home and/or in college
- one-time events like mega wedding of daddy's daughter
- etc.

The same principle apply, calculate the present value of the future cash flows (income and expenses), and compute a combined portfolio balance. The Excel NPV() function is very handy for such math. One needs to be very careful about such approach though, because the sequence-of-return risk can hit double hard. The future cash flows (notably income) are, well, in the future, and in the mean time, one has to spend more of their regular portfolio, so this kind of approach only works if the retirees have pretty solid savings in their regular portfolio, and can truly mitigate the risk of prematurely depleting it. Using a variable withdrawal strategy seems like a quasi-must to me under such circumstances...

ryman554
Posts: 1081
Joined: Sun Jan 12, 2014 9:44 pm

Re: Effect of Social Security when Retiring before Benefits Begin

Post by ryman554 » Thu Jul 26, 2018 8:36 am

I could go through the math and work out present value calculations, but it seems is if these methods need to assume a growth rate of the portfolio.. what happens when such growth rate is not achieved, or is overachieved?

I prefer a different approach, using a sustainable withdrawal rate. I know roughly how much money I'm going to need. I also know roughly what kind of a portfolio I need to sustain it. If I retire before SS kicks in, my required portfolio size increases by the amount needed to cover the planned SS yearly amount (whether 62 or 70 -- 62 almost always comes out ahead!) for those years that I do not collect. Since I can buy TIPS at 0% real, I assume this will more or less agree with the SS COLA.

So, all I need to do is calculate the amount of SS I will get per year, multiply that by the years I will need to cover before the SS actually kicks in, and add that to the sustainable withdrawal rate portfolio size. Presto -- there's my number needed to early retire. I don't need to make any assumptions regarding return rates or do any complex excel calculations. I just look up my PIA on mySS and do a SWR calculation. It has the added advantage that the portfolio risk and AA pre-SS are the same post-SS with using the TIPS "risk-free" money. And it's all done with today's (real) dollars.

Yes, it's more conservative than the OP and relying on market returns (which will usually be higher than TIPS), but it also smooths out the AA in retirement.

User avatar
#Cruncher
Posts: 2648
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 » Thu Jul 26, 2018 11:02 am

Tamales wrote:
Wed Jul 25, 2018 10:56 pm
I’m not sure I’m following the formula in “I. Spending with SS.” In particular, what’s the purpose of using the MIN() function? I follow the first PMT() argument of the MIN function, but not the second one, or why you’d need to choose between the smaller of the two. Could you elaborate?
The first instance of the PMT function, Tamales, calculates how much one can spend and have exactly $0 remaining at the "Die Age". The second instance calculates how much one can spend and have exactly $0 remaining at the age SS commences.

My first pass used only the first instance. But then I noticed in some cases column J (FV at [claim age]) was negative. This meant that even though mathematically one would still have $0 at the Die Age, one would go bankrupt before SS kicked in. Since this is not a reasonable scenario I had to modify the formula to not allow a negative net worth before beginning SS.

Note that one may be able to increase spending by starting SS earlier (assumption in cell B6). For example, with a claim age of 70, the table in the original post shows for retirement at age 40, one can only spend $16,082. But if one claims at 67, this goes up to $17,261.

siamond wrote:
Wed Jul 25, 2018 11:14 pm
Having reviewed diverse situations though, it became clear that the issue is much larger than the future stream of SS though. It's really about the future cash flows at large, including:
...
- pensions (which are often not or only partly inflation-adjusted)
Quite so, siamond. My little spreadsheet certainly isn't a substitute for comprehensive analysis. But I do believe it's more accurate than the even simpler approaches mentioned by David Jay and ryman554. A non-CPI-indexed pension in particular complicates the analysis since this requires making an inflation assumption to convert it to a real dollar equivalent.

ryman554 wrote:
Thu Jul 26, 2018 8:36 am
If I retire before SS kicks in, my required portfolio size increases by the amount needed to cover the planned SS yearly amount … for those years that I do not collect. Since I can buy TIPS at 0% real, I assume this will more or less agree with the SS COLA.
That's certainly simpler, ryman554. But these days even TIPS return more than 0% in real terms, so you're overstating the amount you need.

To make it easier to try my approach, I've put rows 1-15 into a code block. To use, Select All, Copy, and Paste at cell A1 of a blank Excel sheet. [*] Then format the cells and copy row 15 down for as many years as desired. First, here is the spreadsheet in my original post that calculates how much one can spend:

Code: Select all

Bend point 1	895
Bend point 2	5397
Annual salary	118500
Age start work	30
Normal Returement Age	67
Age claim benefits	70
Die age	100
Starting portfolio value	0
Annual savings	30000
Real return before retire	0.04
Real return after retire	0.02
Years	Age			Annual	PV	Savings	Spending		
Work	Retire	AIME	PIA	="SS @ "&B6	of SS	Grow to	w/o SS	with SS	="FV @ "&B6
10	=B$4+A14	=MIN(35,A14)*B$3/35/12	=90%*MIN($B$1,C14)+32%*MAX(0,MIN($B$2,$C14)-$B$1)+15%*MAX(0,C14-$B$2)	=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(B$11,B$7-B$6,-E14,0,0)/(1+B$11)^(B$6-B14)	=FV(B$10,A14,-B$9,-B$8,0)	=PMT(B$11,B$7-B14,-G14,0,0)	=MIN(PMT(B$11,B$7-B14,-F14-G14,0,0),PMT(B$11,MAX(1,B$6-B14),-G14,0,0))	=FV(B$11,B$6-B14,I14,-G14,0)
=A14+1	=B$4+A15	=MIN(35,A15)*B$3/35/12	=90%*MIN($B$1,C15)+32%*MAX(0,MIN($B$2,$C15)-$B$1)+15%*MAX(0,C15-$B$2)	=12*D15*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(B$11,B$7-B$6,-E15,0,0)/(1+B$11)^(B$6-B15)	=FV(B$10,A15,-B$9,-B$8,0)	=PMT(B$11,B$7-B15,-G15,0,0)	=MIN(PMT(B$11,B$7-B15,-F15-G15,0,0),PMT(B$11,MAX(1,B$6-B15),-G15,0,0))	=FV(B$11,B$6-B15,I15,-G15,0)
And here is the same thing for the spreadsheet in this post that calculates how much one needs to save:

Code: Select all

Bend point 1	895
Bend point 2	5397
Annual salary	118500
Age start work	30
Normal Returement Age	67
Age claim benefits	70
Die age	100
Starting portfolio value	0
Annual spending	60000
Real return before retire	0.04
Real return after retire	0.02
Years	Age			Annual	PV	Portfolio Needed		Annual Savings Needed		
Work	Retire	AIME	PIA	="SS @ "&B6	of SS	w/o SS	with SS	w/o SS	with SS	Diff
10	=B$4+A14	=MIN(35,A14)*B$3/35/12	=90%*MIN($B$1,C14)+32%*MAX(0,MIN($B$2,$C14)-$B$1)+15%*MAX(0,C14-$B$2)	=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(B$11,B$7-B$6,-E14,0,0)/(1+B$11)^(B$6-B14)	=-PV(B$11,B$7-B14,B$9,0,0)	=G14-F14	=-PMT($B$10,$A14,-$B$8,G14,0)	=-PMT($B$10,$A14,-$B$8,H14,0)	=I14-J14
=A14+1	=B$4+A15	=MIN(35,A15)*B$3/35/12	=90%*MIN($B$1,C15)+32%*MAX(0,MIN($B$2,$C15)-$B$1)+15%*MAX(0,C15-$B$2)	=12*D15*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(B$11,B$7-B$6,-E15,0,0)/(1+B$11)^(B$6-B15)	=-PV(B$11,B$7-B15,B$9,0,0)	=G15-F15	=-PMT($B$10,$A15,-$B$8,G15,0)	=-PMT($B$10,$A15,-$B$8,H15,0)	=I15-J15
* I'm not sure this will work for all browsers and versions of Excel. I tested it with the Edge and Chrome browsers and Excel 2016 on Windows. To paste I chose "Paste Special" and "Text".

marcopolo
Posts: 1030
Joined: Sat Dec 03, 2016 10:22 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by marcopolo » Thu Jul 26, 2018 11:32 am

Interesting approach.
Would be extremely helpful to also incorporate Medicare at 65. For earlier retirees, the years prior to age 65 can involve a significant expense for health insurance. The difference between that and what will still need to be paid for Medicare could be thought of as a Medicare benefit similar to social sec benefit.
Once in a while you get shown the light, in the strangest of places if you look at it right.

User avatar
siamond
Posts: 4154
Joined: Mon May 28, 2012 5:50 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by siamond » Thu Jul 26, 2018 12:21 pm

#Cruncher wrote:
Thu Jul 26, 2018 11:02 am
My little spreadsheet certainly isn't a substitute for comprehensive analysis. But I do believe it's more accurate than the even simpler approaches mentioned by David Jay and ryman554. A non-CPI-indexed pension in particular complicates the analysis since this requires making an inflation assumption to convert it to a real dollar equivalent.
Yes, real life is complicated, and such approach is indeed much more accurate. And more intellectually satisfying. This topic gave me some headaches and anguish for a while with my own early retirement planning until I realized that it's a simple present value computation, as you explained.

Pensions do add a bit of complexity (my wife's state pension is party adjusted for inflation, party not, oh joy). This is where incremental computations with Excel (or some equivalent) really help though, it's fairly trivial to compute the (lack of) inflation adjustment one year at a time, and then use the NPV() function on the corresponding series of cash flow annual outcomes. It's more complicated to explain than to do with a spreadsheet, frankly!

The way I do it is to run the NPV() to derive a portfolio-equivalent number from the future cash flows. Then use the reverse PMT() function with the same terms (e.g. rate, #periods) to get to an annualized (extra) withdrawal for the early retirement years. Rinse and repeat at the beginning of every year of your early retirement. Interestingly enough, the outcome is much less sensitive to the rate being used than one might think.

The double whammy on sequence of return risk does bug me though. If we're in the middle of a deep crisis by the time I turn 66, I will probably cancel any plan of delaying SS until 70 (knowing that the post-tax present value of delaying SS isn't that great anyway)... And if a deep crisis occurs before, I'll probably tighten the belt more than the NPV/PMT math dictates.

Tamales
Posts: 1244
Joined: Sat Jul 05, 2014 10:47 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by Tamales » Fri Jul 27, 2018 9:45 am

Thanks #cruncher. I want to understand the logic underpinning the capitalization of one's lifetime social security benefit.
In the formula "F. PV of SS" which is
=PV(B$11, B$7-B$6, -E14, 0, 0) / (1+B$11)^(B$6-B14)

...you have an equation of the form A/B. It looks like the numerator, A, is calculating the total cumulative SS benefit from claim age until death age (using the current year’s estimate of the annual SS benefit at the claim age as the baseline, adjusted for +/- FRA), growing at 2% (the user-selected real return after retiring) annually from the year of first claim through year of death. And I suppose a person could just use their PIA estimate from their annual SSA statement instead, and adjust for +/- FRA, and multiply x 12, in place of E14 in the formula.

And the denominator looks like it is a multiplier factor that represents (user-selected) 2% annual growth for the years from when you stop working until you first claim SS (the “gap” years), so with this in the denominator it has the effect of peeling off 2% per year from the numerator, back from the year you first claim, to the year you stop working.

So the net result is that your lifetime SS benefit is put in terms of its present value.
>>>>>

Now let's set that aside for a moment and say we have someone in their 50's, let's say 55, who is planning to retire at 60 and claim SS at 70 and die at 100. They have a progression through those phases.

So they begin the progression with some portfolio value at the time they retire at 60.

Then from age 60-70 (the "gap years") they have only that portfolio to draw annual expenses from, but the portfolio also accrues gains at the user-selected rate of 2% annually during the retirement years. So they end up with some "depleted portfolio value" due to the gap years, at their claim age of 70.

Now at age 70 they begin drawing SS benefits, until they die at 100. Going forward from here their baseline is their depleted portfolio value after the gap years. Each year going forward they have:
-Annual expenses
+Annual income of their SS benefit
+Annual gains for the remaining portfolio.

If we set up a spreadsheet for calculating what I just described, similar to #cruncher's format but focused on someone in their 50's looking to retire before they claim SS, it would look like this:

First, the user data-entry portion:
In cell B2, enter your age on your birthday this year (55 in the example)
and B3=annual savings now until you retire from work (let's say $70,000 per year)
and B4=Normal retirement age (let's say 67)
and B5=age you want to first claim SS benefits (let's say 70)
and B6=death age (let's say 100)
and B7=Current portfolio value (let's say $1M)
and B8=annual spending (let's say $60,000)
and B9=real return before retire (let's say 4%)
and B10=real return during retire (let's say 2%)
and B11=your current full retirement age SS benefit, taken from your latest SSA benefit statement (let's say $2900).

and we have a couple helper-calculations:
B12=B11*12 {this is just an annual version of your SSA benefit number}
B13=$B$11*IF(B5<B4,1-(5/900)*MIN(36,(B4-B5)*12)-(5/1200)*MAX(0,(B4-B5)*12-36),1+(8/1200)*(B5-B4)*12)
{this is just adjusting your SSA benefit for your selected claim age, resulting in a monthly number}
B14=$B$13*12 {this is just an annual version of B13}

Now we create the calculation table.
In A16 we add a label: Age to retire from work.
And in A17 we put 50, A18=51, A19=52, etc, on to A37=70.

Then in B16 we add a label: portfolio value when you retire from work.
And B17=FV($B$9,A17-$B$2,-$B$3,-$B$7,0)
and drag the fill handle down to B37.

Then in C16 we add a label: Depleted portfolio value after gap years.
And C17=-FV($B$10,$B$5-A17,-$B$8,B17,0)
and drag the fill handle down to B37.

Then in D16 we add a label: SS kicks in, portfolio value at die age.
And D17=FV($B$10,$B$6-$B$5,($B$8-$B$14),-C17,0)
and drag the fill handle down to D37.

So the result is you end up with a table from A16:D37, including a header row. You'd ignore the rows with retirement ages less or equal to your current age.

And let's say this person was wanting to retire at age 60, so they'd look at the row corresponding to age 60 retire from work. The calculation results for the age 60 retirement age row, including all the user-entered parameters listed above, would be (unless I have an error in the formulas):

[in Row 27]
retire at age 60
Portfolio value at retire: $1,595,795
Depleted portfolio value at begin SS: $1,288,283
Portfolio value at death age 100: $1,650,054

First, I'd greatly appreciate if someone could re-create the spreadsheet described above and check my formulas. Let me know if there are any errors.

But circling back to the original topic of capitalizing your lifetime SS benefit and arriving at a lump-sum present value, it seems if I were to include that in the portfolio value at the time you retire from work and carrying it forward from there, it would be like including a tidy sum of money you don't actually have, and accruing gains on it over your lifetime. That didn't make sense to me, so I didn't include it. Maybe someone could explain the logic on why you'd include it and how it's mathematically valid to do so.

User avatar
#Cruncher
Posts: 2648
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 27, 2018 3:09 pm

Tamales wrote:
Fri Jul 27, 2018 9:45 am
and B11=your current full retirement age SS benefit, taken from your latest SSA benefit statement (let's say $2900).
Just be careful here. The full retirement age (FRA) benefit SSA reports assumes you continue to earn your current wage until FRA. If you retire early and don't have those wages, your benefit at FRA may be less. My two spreadsheets take this into account by calculating the FRA benefit (i.e., the PIA) based on the number of years from start of work until retirement.

Tamales in same post wrote:[in Row 27]
retire at age 60
Portfolio value at retire: $1,595,795
Depleted portfolio value at begin SS: $1,288,283
Portfolio value at death age 100: $1,650,054
First, I'd greatly appreciate if someone could re-create the spreadsheet described above and check my formulas. Let me know if there are any errors.
Yes, I confirmed these figures. Your calculation is correct, Tamales. For other readers here is what your figures represent:

Code: Select all

1,595,795 = amount that $1,000K + savings       grows to in  5 years @ 4%
1,288,283 = amount that $1,595K - spending      falls to in 10 years @ 2%
1,650,054 = amount that $1,288K - spending + SS grows to in 30 years @ 2%
The last number (i.e., the remaining portfolio balance at age 100) can also be expressed as the sum of three numbers:

Code: Select all

 3,523,580  = Future value of portfolio = 1595795 *  1.02 ^ 40
(3,624,119) = Future value of spending  =  -60000 * (1.02 ^ 40 - 1) / 0.02 [1]
 1,750,594  = Future value of SS        =   43152 * (1.02 ^ 30 - 1) / 0.02 [1]
 ---------
 1,650,054  = portfolio balance at age 100
Tamales in same post wrote:But circling back to the original topic of capitalizing your lifetime SS benefit and arriving at a lump-sum present value, it seems if I were to include that in the portfolio value at the time you retire from work and carrying it forward from there, it would be like including a tidy sum of money you don't actually have, and accruing gains on it over your lifetime. That didn't make sense to me, so I didn't include it. Maybe someone could explain the logic on why you'd include it and how it's mathematically valid to do so. (underline added)
I can see why this doesn't make sense to you. None the less it works out mathematically. Above I show how the growth of SS produces $1,750,594 of the $1,650,054 portfolio value at age 100. The present value of this at age 60 is $792,827 (1,750,594 / 1.02 ^ 40). If we grow this for 40 years, obviously we get the same $1,750,594 future value. This is what my spreadsheets do.

We can also see this algebraically as follows where
FV$1 is future value of $1 per year
PV$1 is present value of $1 per year

Code: Select all

a  FV$1  for 30 yr: (1.02 ^ 30 - 1)     / 0.02 [1]
b  PV$1  for 30 yr: (1 - 1 / 1.02 ^ 30) / 0.02 [2]
c  PV(b) for 10 yr: (1 - 1 / 1.02 ^ 30) / 0.02 / 1.02 ^ 10
d  FV(c) for 40 yr: (1 - 1 / 1.02 ^ 30) / 0.02 / 1.02 ^ 10 * 1.02 ^ 40
e  reduces to       (1 - 1 / 1.02 ^ 30) / 0.02 * 1.02 ^ 30
f  reduces to (a)   (1.02 ^ 30 - 1)     / 0.02
  • Line a represents what each $1 of SS grows to in 30 years.
  • Line c is the formula my spreadsheets implicitly use to get the present value of each $1 of delayed SS benefits.
  • Line d shows this after growing for 40 years.
  • Line f shows how line d is equivalent to line a.

  1. Uses formula for future value of annuity as shown here. The Excel FV function uses this to calculate the future value of the Pmt parameter.
  2. Uses formula for present value of an annuity as shown here. The Excel PV function uses this to calculate the present value of the Pmt parameter.

Tamales
Posts: 1244
Joined: Sat Jul 05, 2014 10:47 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by Tamales » Sat Jul 28, 2018 12:11 am

Thanks #Cruncher, for the explanation. And for the reminder that the SSA statement's estimate presumes you work until FRA.

gips
Posts: 457
Joined: Mon May 13, 2013 5:42 pm

Re: Effect of Social Security when Retiring before Benefits Begin

Post by gips » Sat Jul 28, 2018 10:02 am

nice work! I'm 62, in retirement now and from time-to-time, wonder how much deep thought has been given to the conventional wisdom of delaying ss till 70.5 (or whatever the max age is). off the top of my head, factors to consider include:
- age and life expectancy of recipient and spouse
- benefit amount of recipient and spouse
- anticipated expenses
- prediction of real returns
- non-zero probability of reduced benefits
- asset allocation
- placement of assets in taxable withdrawals (ira/sep) vs. non-taxable
- fed and state tax bracket
- cap gains rate
- eligibility for aca subsidies

I'm sure I've missed a bunch. is there a calculator that covers most of this?

User avatar
LadyGeek
Site Admin
Posts: 47397
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Effect of Social Security when Retiring before Benefits Begin

Post by LadyGeek » Sat Jul 28, 2018 8:37 pm

This thread is now in the Personal Finance (Not Investing) forum (Social Security).

I have added this thread to the wiki: Social Security: member contributions
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

JoeRetire
Posts: 1488
Joined: Tue Jan 16, 2018 2:44 pm

Re: Effect of Social Security when Retiring before Benefits Begin

Post by JoeRetire » Sun Jul 29, 2018 7:10 am

gips wrote:
Sat Jul 28, 2018 10:02 am
nice work! I'm 62, in retirement now and from time-to-time, wonder how much deep thought has been given to the conventional wisdom of delaying ss till 70.5 (or whatever the max age is).
Nobody advocates delaying benefits past 70.

Why would you wonder about deep thought if you have already made your decision?
is there a calculator that covers most of this?
https://maximizemysocialsecurity.com/
https://opensocialsecurity.com/

GAAP
Posts: 578
Joined: Fri Apr 08, 2016 12:41 pm

Re: Effect of Social Security when Retiring before Benefits Begin

Post by GAAP » Tue Jul 31, 2018 11:20 am

siamond wrote:
Thu Jul 26, 2018 12:21 pm

Pensions do add a bit of complexity (my wife's state pension is party adjusted for inflation, party not, oh joy). This is where incremental computations with Excel (or some equivalent) really help though, it's fairly trivial to compute the (lack of) inflation adjustment one year at a time, and then use the NPV() function on the corresponding series of cash flow annual outcomes. It's more complicated to explain than to do with a spreadsheet, frankly!
Don't want to derail the thread, but you can also use NPV to figure out when to claim that nominal pension, depending on the current inflation rate. It may make sense to delay it now, but claim sooner if the inflation rate jumps.

User avatar
LadyGeek
Site Admin
Posts: 47397
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Effect of Social Security when Retiring before Benefits Begin

Post by LadyGeek » Wed Aug 01, 2018 8:38 pm

#Cruncher wrote:
Thu Jul 26, 2018 11:02 am
...And here is the same thing for the spreadsheet in this post that calculates how much one needs to save:

Code: Select all

Bend point 1	895
Bend point 2	5397
Annual salary	118500
Age start work	30
Normal Returement Age	67
Age claim benefits	70
Die age	100
Starting portfolio value	0
Annual spending	60000
Real return before retire	0.04
Real return after retire	0.02
Years	Age			Annual	PV	Portfolio Needed		Annual Savings Needed		
Work	Retire	AIME	PIA	="SS @ "&B6	of SS	w/o SS	with SS	w/o SS	with SS	Diff
10	=B$4+A14	=MIN(35,A14)*B$3/35/12	=90%*MIN($B$1,C14)+32%*MAX(0,MIN($B$2,$C14)-$B$1)+15%*MAX(0,C14-$B$2)	=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(B$11,B$7-B$6,-E14,0,0)/(1+B$11)^(B$6-B14)	=-PV(B$11,B$7-B14,B$9,0,0)	=G14-F14	=-PMT($B$10,$A14,-$B$8,G14,0)	=-PMT($B$10,$A14,-$B$8,H14,0)	=I14-J14
=A14+1	=B$4+A15	=MIN(35,A15)*B$3/35/12	=90%*MIN($B$1,C15)+32%*MAX(0,MIN($B$2,$C15)-$B$1)+15%*MAX(0,C15-$B$2)	=12*D15*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(B$11,B$7-B$6,-E15,0,0)/(1+B$11)^(B$6-B15)	=-PV(B$11,B$7-B15,B$9,0,0)	=G15-F15	=-PMT($B$10,$A15,-$B$8,G15,0)	=-PMT($B$10,$A15,-$B$8,H15,0)	=I15-J15
* I'm not sure this will work for all browsers and versions of Excel. I tested it with the Edge and Chrome browsers and Excel 2016 on Windows. To paste I chose "Paste Special" and "Text".
The spreadsheet code block is in tab delimited format - the tab character (0x09) is separating the cells. This is the same as a Comma Separated Value (.csv) format except the tab character is used instead of a comma. You can't see the tab characters, but they are present.

The copy-n-paste directly into LibreOffice Calc doesn't work. Use this method instead:

To create the spreadsheet, Select All, Copy, and Paste the code block into a text editor. I'm using a Linux text editor, but it should be equivalent to notepad on MS Windows. Save the text to a file.

In LibreOffice Calc, File --> Open, the text import wizard appears. Separator Options --> Separated by --> Tab. OK.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
LadyGeek
Site Admin
Posts: 47397
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Effect of Social Security when Retiring before Benefits Begin

Post by LadyGeek » Wed Aug 01, 2018 8:39 pm

LadyGeek wrote:
Sat Jul 28, 2018 8:37 pm
This thread is now in the Personal Finance (Not Investing) forum (Social Security).

I have added this thread to the wiki: Social Security: member contributions
The wiki article has been revised to add a "Calculators" section. See: Social Security: member contributions
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Reelescape1
Posts: 4
Joined: Fri Jun 15, 2018 6:47 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by Reelescape1 » Thu Aug 02, 2018 10:48 am

I've been lurking, reading, and trying to learn and absorb as much as possible here. I thank you all for the contributions and words of wisdom.

Question: Since there are a lot of variables with the first spreadsheet; salary over time, etc. If I correct G data field at target age (entering a negative B8) to actual amount (or close) are the numbers still valid? The PIA is ~ $100 off but I'm thinking the rest is very close (?)

Bend point 1 $895.00
Bend point 2 $5,397.00
Annual salary
Age start work 28
Normal Returement Age 67
Age claim benefits 62
Die age 85
Starting portfolio value -$58,000.00
Annual savings $16,000.00
Real return before retire 4.00%
Real return after retire 2.00%
Years Age AIME PIA Annual PV Savings Spending Spending FV @ 67
Work Retire SS @ 62 of SS Grow to w/o SS with SS

27 55 $5,142.86 $2,164.81 $18,184.44 $289,577.47 $586,112.05 $26,169.86 $39,099.47 $382,581.98
28 56 $5,333.33 $2,225.77 $18,696.44 $303,685.41 $625,556.54 $28,636.95 $42,539.17 $436,136.04
29 57 $5,523.81 $2,265.16 $19,027.36 $315,241.67 $666,578.80 $31,322.32 $46,135.42 $495,866.27

User avatar
#Cruncher
Posts: 2648
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 » Thu Aug 02, 2018 1:59 pm

Reelescape1 wrote:
Thu Aug 02, 2018 10:48 am
If I correct G data field at target age (entering a negative B8) to actual amount (or close) are the numbers still valid?
Yes, they should still be valid. If I understand you correctly, Reelescape1, you entered -$58,000 as the "Starting portfolio value" so as to make the "Savings Grow To" be $586,000 at age 55, an amount you've arrived at by other means. Even though you leave it blank in your post, I see that you entered $80,000 as the "Annual Salary" to make the AIME be $5,143 at age 55. If these are correct and the 2% real growth rate holds true, the spending figures should be good. E.g., including SS you can spend $39,100 per year from age 55 to 85 and have a $0 remaining balance.

Code: Select all

382,585 = balance age 62 = FV(2%,  7, 39099,         -586112, 0)
      6 = balance age 85 = FV(2%, 23, 39099 - 18184, -382585, 0)
Here are your figures put inside a code block to make them easier to read:

Code: Select all

Row  Col A                                 Col B
  1  Bend point 1                           $895 
  2  Bend point 2                         $5,397 
  3  Annual salary                       $80,000 
  4  Age start work                           28 
  5  Normal Returement Age                    67
  6  Age claim benefits                       62 
  7  Die age                                  85 
  8  Starting portfolio value           $(58,000)
  9  Annual savings                      $16,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 @ 62    of SS    Grow to  w/o SS  with SS    FV @ 62
---  ---- ------   -----  ----- -------  -------    -------  ------   ------    -------
 31    27     55   5,143  2,165  18,184  289,578    586,112  26,170   39,099    382,582 
 32    28     56   5,333  2,226  18,696  303,686    625,557  28,637   42,539    436,136 
 33    29     57   5,524  2,265  19,027  315,242    666,579  31,322   46,135    495,866

Reelescape1
Posts: 4
Joined: Fri Jun 15, 2018 6:47 am

Re: Effect of Social Security when Retiring before Benefits Begin

Post by Reelescape1 » Fri Aug 03, 2018 5:23 am

Thanks Cruncher, that helps.

User avatar
#Cruncher
Posts: 2648
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 » Tue Sep 18, 2018 8:27 pm

I'm going to discuss a new subject. But it shares one thing with the topic of this thread: how it can be useful to capitalize expected future Social Security benefits. The context here is one often discussed on the forum (some might say too often :wink: ): whether to begin collecting SS at age 62 or to delay -- possibly to age 70.

Many posters strongly favor taking SS as early as possible. Some [1] give as a reason that they plan to invest the benefits received during the eight years from age 62 to 70 in stocks and earn a hefty real return. They are correct that if the return is high enough (generally between 7% and 8% in real terms [2]), the greater SS benefit at age 70 will never be able to make portfolio value "overtake" what it would be from starting SS at 62.

But this argument overlooks the relation between the capitalized value of the starting-at-62 and the starting-at-70 SS benefits and their effect on the proportion of stocks in one's portfolio. As time passes the capitalized value of the starting-at-70 benefits grows larger compared to the starting-at-62 benefits. If one includes capitalized value of SS in one's fixed income allocation, this means that by delaying to age 70, one can invest proportionally more in stocks and still maintain one's desired asset allocation.

But before showing this progression over time, let me show an example of how this capitalized SS affects investment in bonds and stocks. Assume
  • Collect $20,000 per year SS if start at age 62 or $35,200 (20000 * 132 / 75) if start at age 70.
  • Expect to collect SS until life expectancy shown in SSA 2015 Period Life Table. For a 62 year old man this is 20.00 years.
  • Future SS benefits are capitalized by discounting the expected benefits at 2%.
  • Portfolio value when retire at age 62 is $1,000,000 excluding capitalized SS.
  • One wants to maintain a 50:50 ratio between fixed income (including capitalized SS) and stocks.
Here is the portfolio breakdown at age 62:

Code: Select all

                   Start at 62   Start at 70
                   -----------   -----------
Capitalized SS        327,029       317,714  
Bonds                 336,486       341,143  
                      -------       -------
Total fixed income    663,515       658,857
Stocks                663,514       658,857
                    ---------     ---------
Bonds + Stocks      1,000,000     1,000,000
Calculation of capitalized SS using the Excel PV function:

Code: Select all

327,029 = -PV(2%, 20,     20000, 0, 0)
317,714 = -PV(2%, 20 - 8, 35200, 0, 0) / 1.02 ^ (70 - 62)
Now let's see how these figures progress each year after age 62 given the following five additional assumptions:

Code: Select all

Row  Col A                            Col B
  1  Spending                        70,000
  2  Stock growth                     7.00%
  3  Fixed Income growth              2.00%
  4  Man (1) / Woman (2)                  1
  5  Fixed income percent            50.00%
           Life Exp Yrs    Capitalized SS       Portfolio Value          Bond Value           Stock Value
           ------------   -----------------   --------------------    -----------------    -----------------
  7  Start                   62       70          62         70          62       70          62        70
  8  SS Benefit            20,000    35,200
    ColA   Col B  Col C    Col D     Col E      Col F      Col G       Col H     Col I      Col J     Col K
Row  Age    Man   Woman

Code: Select all

  9   62   20.00  22.81   327,029   317,714   1,000,000  1,000,000    336,486   341,143    663,514   658,857
 10   63   19.27  21.99   317,230   330,510   1,003,176    982,943    342,973   326,216    660,203   656,726 [*]
 11   64   18.53  21.17   307,151   343,415   1,006,249    965,438    349,549   311,012    656,700   654,426
 12   65   17.81  20.36   297,201   357,161   1,009,209    947,468    356,004   295,154    653,205   652,314
 13   66   17.09  19.55   287,109   371,280   1,012,054    929,033    362,472   278,877    649,581   650,157
 14   67   16.38  18.76   277,015   386,034   1,014,774    910,122    368,879   262,044    645,895   648,078
 15   68   15.68  17.98   266,923   401,442   1,017,364    890,728    375,220   244,643    642,144   646,085
 16   69   14.98  17.20   256,691   417,266   1,019,819    870,847    381,564   226,790    638,255   644,057
 17   70   14.30  16.44   246,614   434,041   1,022,128    850,467    387,757   208,213    634,371   642,254

 18   71   13.63  15.69   236,552   416,331   1,024,289    864,789    393,869   224,229    630,420   640,560
 19   72   12.97  14.96   226,508   398,654   1,026,296    879,312    399,894   240,329    626,402   638,983
 20   73   12.33  14.24   216,643   381,291   1,028,142    894,048    405,749   256,378    622,392   637,669
 21   74   11.70  13.54   206,809   363,983   1,029,824    909,012    411,508   272,515    618,316   636,498
 22   75   11.08  12.85   197,010   346,738   1,031,336    924,217    417,163   288,740    614,173   635,478
 23   76   10.48  12.17   187,412   329,846   1,032,672    939,676    422,630   304,915    610,042   634,761
 24   77    9.89  11.51   177,863   313,039   1,033,827    955,407    427,982   321,184    605,845   634,223
 25   78    9.33  10.86   168,695   296,903   1,034,796    971,426    433,051   337,262    601,746   634,165
 26   79    8.77  10.24   159,425   280,588   1,035,579    987,763    438,077   353,588    597,502   634,176
 27   80    8.24   9.63   150,556   264,979   1,036,166  1,004,427    442,805   369,724    593,361   634,703

 28   81    7.72   9.04   141,764   249,505   1,036,557  1,021,451    447,397   385,973    589,161   635,478
 29   82    7.23   8.48   133,396   234,777  [1,036,747][1,038,854]   451,675   402,039    585,071   636,815 <==
 30   83    6.75   7.93   125,119   220,210   1,036,735  1,056,672    455,808   418,231    580,927   638,441
 31   84    6.30   7.41   117,288   206,427   1,036,516  1,074,927    459,614   434,250    576,902   640,677
 32   85    5.87   6.91   109,740   193,142   1,036,092  1,093,659    463,176   450,259    572,916   643,401
 33   86    5.45   6.43   102,304   180,056   1,035,459  1,112,903    466,577   466,423    568,882   646,479
 34   87    5.06   5.98    95,345   167,807   1,034,612  1,132,685    469,634   482,439    564,979   650,246
 35   88    4.69   5.54    88,692   156,098   1,033,554  1,153,051    472,431   498,476    561,123   654,574
 36   89    4.35   5.14    82,536   145,263   1,032,281  1,174,040    474,873   514,389    557,408   659,651
 37   90    4.03   4.76    76,703   134,998   1,030,797  1,195,704    477,047   530,353    553,750   665,351
* Example calculation of portfolio values at age 63 for the two scenarios:

Code: Select all

start at 62: 1,003,176 = 336486 * 1.02 + 663514 * 1.07 + 20000 - 70000
start at 70:   982,943 = 341143 * 1.02 + 658857 * 1.07         - 70000
By age 70 the capitalized value of the start-at-70 SS benefits is $187,000 more ($434K vs $247K) than that of the start-at-62 benefits. This allows $8,000 more ($642K vs $634K) to be invested in stocks for the start-at-70 scenario, even though the total portfolio value has fallen $172,000 ($850K vs $1,022K) to cover the additional $20,000 withdrawal from the portfolio each year to cover the missing SS. By age 82 the total portfolio value of the start-at-70 scenario has matched the start-at-62's. This is a quite different outcome than the simple invest-$20K-per-year-in-stocks analysis which concludes the start-at-70 scenario takes until extreme old age (if ever) to "catch up" with start-at-62.

To repeat this analysis but with different assumptions follow these steps:
  • Select All, Copy, and Paste [3] the following at cell A1 of a blank Excel sheet:

    Code: Select all

    Spending	70000
    Stock growth	0.07
    Fixed Income growth	0.02
    Man (1) / Woman (2)	1
    Fixed income percent	0.5
    	Life Expectancy		Capitalized SS		Portfolio Value		Bond Value		Stock Value	
    Start	Man	Woman	62	70	=$D7	=$E7	=$D7	=$E7	=$D7	=$E7
    SS Benefit			20000	35200
    62	20	22.81	=-PV($B$3,INDEX($B9:$C9,1,$B$4)-MAX(0,D$7-$A9),D$8,0,0)/(1+$B$3)^MAX(0,D$7-$A9)	=-PV($B$3,INDEX($B9:$C9,1,$B$4)-MAX(0,E$7-$A9),E$8,0,0)/(1+$B$3)^MAX(0,E$7-$A9)	1000000	=F9	=(D9+F9)*$B$5-D9	=(E9+G9)*$B$5-E9	=F9-H9	=G9-I9
    =A9+1	19.27	21.99	=-PV($B$3,INDEX($B10:$C10,1,$B$4)-MAX(0,D$7-$A10),D$8,0,0)/(1+$B$3)^MAX(0,D$7-$A10)	=-PV($B$3,INDEX($B10:$C10,1,$B$4)-MAX(0,E$7-$A10),E$8,0,0)/(1+$B$3)^MAX(0,E$7-$A10)	=H9*(1+$B$3)+J9*(1+$B$2)+IF($A10>D$7,D$8,0)-$B$1	=I9*(1+$B$3)+K9*(1+$B$2)+IF($A10>E$7,E$8,0)-$B$1	=(D10+F10)*$B$5-D10	=(E10+G10)*$B$5-E10	=F10-H10	=G10-I10
    =A10+1	18.53	21.17
    =A11+1	17.81	20.36
    =A12+1	17.09	19.55
    =A13+1	16.38	18.76
    =A14+1	15.68	17.98
    =A15+1	14.98	17.2
    =A16+1	14.3	16.44
    =A17+1	13.63	15.69
    =A18+1	12.97	14.96
    =A19+1	12.33	14.24
    =A20+1	11.7	13.54
    =A21+1	11.08	12.85
    =A22+1	10.48	12.17
    =A23+1	9.89	11.51
    =A24+1	9.33	10.86
    =A25+1	8.77	10.24
    =A26+1	8.24	9.63
    =A27+1	7.72	9.04
    =A28+1	7.23	8.48
    =A29+1	6.75	7.93
    =A30+1	6.3	7.41
    =A31+1	5.87	6.91
    =A32+1	5.45	6.43
    =A33+1	5.06	5.98
    =A34+1	4.69	5.54
    =A35+1	4.35	5.14
    =A36+1	4.03	4.76
    =A37+1	3.73	4.41
    =A38+1	3.46	4.09
    =A39+1	3.21	3.8
    =A40+1	2.99	3.54
    =A41+1	2.8	3.3
    =A42+1	2.63	3.09
    =A43+1	2.48	2.9
    =A44+1	2.34	2.73
    =A45+1	2.22	2.57
    =A46+1	2.11	2.42
    	2	2.27
    	1.89	2.14
    	1.79	2
    	1.69	1.88
    	1.59	1.76
    	1.5	1.64
    	1.41	1.53
    	1.33	1.43
    	1.25	1.33
    	1.17	1.24
    	1.1	1.15
    	1.03	1.06
    	0.96	0.98
    	0.89	0.9
    	0.83	0.83
    	0.77	0.77
    	0.71	0.71
    	0.66	0.66
    	0.61	0.61
  • Format for readability.
  • Copy cells D10:K10 down to row 37.
  • Change assumptions in cells B1:B5, D7:E8, and F9 as desired. This includes changing cell B4 from "1" to "2" to use the Woman life expectancies in column C.
  1. The most recent post I noticed that argues this way Is this one in the thread, Delaying SS? How Long It Will Take for It to Pay Off?.
  2. See my post, Re: Time to look at SS [When should I take Social Security?]. It shows that with a 7.32% real return, delaying until age 70 will never "catch up" with starting SS at 62.
    7.32% = (132 / 75) ^ ( 1 / (70 - 62)) - 1
  3. If you have trouble pasting, try "Paste Special" and "Text".

Post Reply