Surprising (to me) SS start age spreadsheet results.

[Spock]

I built a new spreadsheet to determine when to start SS benefits and the results were surprising enough that I thought I'd post a summary here to see if y'all see a hole in the method I used. The delta in annual benefits between start at age 64 and waiting until age 70 is ~$20K/year... so you would think waiting until age 70 would "win".

Method:
1. Annual benefit = spousal benefit + my benefit. Source of benefit amounts is opensocialsecurity.com “what if” section for January of each year from now until age 70
2. Amount collected = Annual Benefit * years between age at death and benefit start age. Ex. Benefit * (death age – start age)
3. Identify best year to start for a given death age: Max Amount collected for each age at death is compared across each benefit start year to identify the start year with the max amount collected.
Assumptions:
1. Both parties die the same year. (car accident?) SSA life expectancy tables suggest Spouse and I will die the same year...
I'll fiddle with a knob for adjusting "amount collected" to account the death of one spouse at different times. But right now multiple layers of nested IF statements in a spreadsheet cell is complicated.
2. Ran thru death at age 90
3. Flat benefit amounts... no COLA adjustments.
4. Assume benefit $ are spent and not invested... so amounts from early start years are not earning compounded returns.

Results:

Start Age​	# years start age=max amount collected at age of death​	Age at death where each start year provided max accumulated benefits.​
64​	15​	Age 64 thru 78​
65​	7​	Age 79 thru 85​
66​	1​	Age 86​
67​	0​	​
68​	0​	​
69​	0​	​
70​	4​	Age 87 thru 90​

Additional observations:
The next observation was comparing how much was "lost" between starting age 64 vs 65 for the death years where start age=65 profied Max benefits.
The average max benefit by starting at age 64 vs. the 7 "start at age 65" years was 98.9% of the age 65 benefits.
The max benefit lost was 2%... start age 64 provided 98% of the benefits received by waiting until age 65.
The curve starts to get more significant out past age 88. At age 90, benefits from Start at 64 are 94.35% of Start at 70.

I've got other spreadsheets... but this one suggests starting now. At this point I'm waiting to see if the "tax free SS" happens to remove the Roth conversion complications.

Any see something big that I didn't account for?

 

[SecondCor521]

Why not just use opensocialsecurity's analysis, since it addresses your assumptions #1, #2, and #3?

I think your assumption #1 is pretty unlikely. Opensocialsecurity also does the math for the survivor receiving the higher benefit for a while, which, depending on the duration, can affect the claiming strategy quite a bit I think.

Even if you spend SS instead of invest it, there is still some amount of value in getting the dollars sooner - if I offer you $100 now or $100 five years from now, which would you choose?

I'm 55 and single. My current plan is to wait until 70. The opensocialsecurity analysis suggests that's best, but only by a few percent and it depends on the discount rate chosen. The more important reason in my case is to do more Roth conversions in my 60s.

As you probably know, the "when to take SS?" debate/discussion is endless.

 

[Timeisprecious]

So where's your data posted? I can't find it.

 

[Sunset]

OP are you married ?

My view is if you don't know you will die early, and you are married, and the larger SS recipient, then take it at age 70.
As odds are someone will live past 85 and benefit.

 

[Spock]

Why not just use opensocialsecurity's analysis, since it addresses your assumptions #1, #2, and #3?

I think your assumption #1 is pretty unlikely. Opensocialsecurity also does the math for the survivor receiving the higher benefit for a while, which, depending on the duration, can affect the claiming strategy quite a bit I think.

Even if you spend SS instead of invest it, there is still some amount of value in getting the dollars sooner - if I offer you $100 now or $100 five years from now, which would you choose?

I'm 55 and single. My current plan is to wait until 70. The opensocialsecurity analysis suggests that's best, but only by a few percent and it depends on the discount rate chosen. The more important reason in my case is to do more Roth conversions in my 60s.

As you probably know, the "when to take SS?" debate/discussion is endless.

This exercise was to give a different view... opensocialsecurity spits out an absolute max benefit number as "best" without any context. (the actuarial life expectancy table (from SSA!) also has different values (~4 years) than the SSA longevity calculator... though its not the tools fault the SSA provides inconsistent info).

The view the spreadsheet is providing (if accurate) shows more context... yes age 70 pays 20K per year more, but the 2% difference in max benefits vs. "now" is insignificant given the other slop in the assumptions.

I'm not looking for a debate on when to start as the variables are unique to each situation. I'm looking for feedback on this method of comparing the results of various start ages vs. expected death ages.

 

[pb4uski]

I think the 2017 CSO tables are more reliable than the SS tables. Or alternatively, your could use the Actuaries Longevity Calculator at https://www.longevityillustrator.org/ or the NWML calculator at Lifespan Calculator – Test Your Life Expectancy || NM.

But to be honest, I think you are overthinking it and just using opensocialsecurity.com is better. OSS calculates the expected present value of every possible claiming age from now until you are 70. For a married couple, it does it for every combination of possible claiming ages. So for a 62 year old couple, that is something like 9,216 different possible claiming scenarios.

For each scenario OSS 1) calculates what you would receive in each future month based on your PIA and the discount or premium factor for that scenario and 2) mulplies that cash flow by the probability of your being alive that month to receive it based on the mortality table that you select (or based on age at death if that is what you choose). That is the expected cash flow. OSS then present values the cash flows using the real discount rate that you specify to get a single number representing the expected present value for that scenario. Then, the scenario with the highest expected present value is deemed the optimal claiming strategy.

Now another approach that I built a spreadsheet for was to compare the benefit at age 62 vs the benefit at age 70 and the year by year differential cash flows and then calculate the differential cash flows. Then I did IRRs on the differential cash flows from 62 to 100. The IRR is the interest rate at which the scenarios would be equal. So for example, if the money that you use to delay SS would earn a 3% real return, then you would breakeven at ~84-1/2. You obviously can create many different claiming scenarios, calculate the differential cash flows and compute the real IRRs. The numbers below are based on $1,000 PIA and FRA of 67. If your PIA is $4,000 then each cash flows would be 4x but the IRRs would not be any different.

In our case, the longevity calculators suggest that one or the other of us will live into our 90s, so its a pretty easy decision to delay given that we have retirement savings and can afford to delay.

Age​	n​	Claim at 62​	Claim at 70​	Diff​	IRR​
62​	0​	​	​	​	​
63​	1​	8,400​	​	-8,400​	​
64​	2​	8,400​	​	-8,400​	​
65​	3​	8,400​	​	-8,400​	​
66​	4​	8,400​	​	-8,400​	​
67​	5​	8,400​	​	-8,400​	​
68​	6​	8,400​	​	-8,400​	​
69​	7​	8,400​	​	-8,400​	​
70​	8​	8,400​	​	-8,400​	​
71​	9​	8,400​	14,880​	6,480​	​
72​	10​	8,400​	14,880​	6,480​	​
73​	11​	8,400​	14,880​	6,480​	​
74​	12​	8,400​	14,880​	6,480​	​
75​	13​	8,400​	14,880​	6,480​	​
76​	14​	8,400​	14,880​	6,480​	​
77​	15​	8,400​	14,880​	6,480​	​
78​	16​	8,400​	14,880​	6,480​	​
79​	17​	8,400​	14,880​	6,480​	-1.7%​
80​	18​	8,400​	14,880​	6,480​	-0.4%​
81​	19​	8,400​	14,880​	6,480​	0.6%​
82​	20​	8,400​	14,880​	6,480​	1.5%​
83​	21​	8,400​	14,880​	6,480​	2.2%​
84​	22​	8,400​	14,880​	6,480​	2.8%​
85​	23​	8,400​	14,880​	6,480​	3.3%​
86​	24​	8,400​	14,880​	6,480​	3.8%​
87​	25​	8,400​	14,880​	6,480​	4.2%​
88​	26​	8,400​	14,880​	6,480​	4.5%​
89​	27​	8,400​	14,880​	6,480​	4.8%​
90​	28​	8,400​	14,880​	6,480​	5.1%​
91​	29​	8,400​	14,880​	6,480​	5.3%​
92​	30​	8,400​	14,880​	6,480​	5.5%​
93​	31​	8,400​	14,880​	6,480​	5.7%​
94​	32​	8,400​	14,880​	6,480​	5.8%​
95​	33​	8,400​	14,880​	6,480​	6.0%​
96​	34​	8,400​	14,880​	6,480​	6.1%​
97​	34​	8,400​	14,880​	6,480​	6.2%​
98​	34​	8,400​	14,880​	6,480​	6.3%​
99​	34​	8,400​	14,880​	6,480​	6.4%​
100​	34​	8,400​	14,880​	6,480​	6.5%​

 

[rembrandt]

Here we go again...

 

[SevenUp]

opensocialsecurity spits out an absolute max benefit number as "best" without any context.

Depends what is meant by "context". Have you looked at the "Test an alternative claiming strategy color-coded graph" it generates?

 

[SecondCor521]

This exercise was to give a different view... opensocialsecurity spits out an absolute max benefit number as "best" without any context.

It does provide context in terms of a heat map or graph under "Test an alternative claiming strategy" which show the relative range or variability in how "best" compared to other claiming ages.

(the actuarial life expectancy table (from SSA!) also has different values (~4 years) than the SSA longevity calculator... though its not the tools fault the SSA provides inconsistent info).

There are multiple life expectancy tables around. Opensocialsecurity lets you choose among several if you click the tiny little checkbox at the very top of the page and then check the fourth option down "Mortality table".

The view the spreadsheet is providing (if accurate) shows more context... yes age 70 pays 20K per year more, but the 2% difference in max benefits vs. "now" is insignificant given the other slop in the assumptions.

Again, opensocialsecurity provides the same context as noted above.

Everyone gets to make their own assumptions, including assumptions about the slop in those assumptions. I think in my own case it doesn't really matter, but I might as well take the 2% edge unless I have reason to believe or assume otherwise. Since I don't have any such reason (and have Roth conversions as a reason to go with the assumptions), I go with the 2% edge.

I'm not looking for a debate on when to start as the variables are unique to each situation. I'm looking for feedback on this method of comparing the results of various start ages vs. expected death ages.

Didn't mean to start a debate (it'll probably happen anyway if history of this board is any guide). I thought I did provide feedback on your method.

 

[sengsational]

I didn't look real closely at the OP, but what was the rate of return you used on the money you got from taking SS early?

What surprised me when I did the calculations is that I could get less from SS, yet be able to spend more in retirement by taking it early and getting a return that's typical of equities. The default return for the calculators that I've seen basically don't give you much above inflation. But if you're rich enough to consider waiting until 70, you might be rich enough to have a good percentage of your portfolio invested in equities.

If the calculator (or your spreadsheet) lets you turn up the ROR on the early SS funds, try it. A 7% real return is a fairly conservative estimate, based on various 10 year spans going back 50 years. I know "they" say the "right" ROR to use is treasuries, but that wouldn't be where I'd put the dough.

 

[pb4uski]

The "right" discount rate to use is the expected teal return on the assets that will be used if you delay SS for the years that you are delaying. If your AA is 100/0 then an estimated real return on equities for the years 62 to 70 is most appropriate. Based on long term trends 7% real would seem in the ball park and would almost always drive one to claim at 62. If one was 62 right now I would trim that 7% for valuation metrics to normalize as I and many others expect to happen in the near term.

OYOH, if one is following a bond tent strategy or otherwise using fixed income for spending while delaying SS the a real return for fixed income would be more appropriate.

Once you've decided the real rate to be used, of the decision is between 62 or 70, then it is a simple matter of tracing the IRRs in the table in post #6 to find the "breakeven" age. Then it is a matter of your health prospects, family longevity and " how lucky do you feel"?

 

[Exchme]

The "right" discount rate to use is the expected teal return on the assets that will be used if you delay SS for the years that you are delaying. If your AA is 100/0 then an estimated real return on equities for the years 62 to 70 is most appropriate. Based on long term trends 7% real would seem in the ball park and would almost always drive one to claim at 62. If one was 62 right now I would trim that 7% for valuation metrics to normalize as I and many others expect to happen in the near term.

OYOH, if one is following a bond tent strategy or otherwise using fixed income for spending while delaying SS the a real return for fixed income would be more appropriate.

Once you've decided the real rate to be used, of the decision is between 62 or 70, then it is a simple matter of tracing the IRRs in the table in post #6 to find the "breakeven" age. Then it is a matter of your health prospects, family longevity and " how lucky do you feel"?

Exactly! Opensocialsecurity.com uses the return of long term TIPS for the IRR since those are the closest product available to SS benefits. Anyone except the person with no bonds can level out their risk by spending down their bonds while waiting.

Another, sometimes dominant, reason to wait is Roth Conversions. The period of time prior to claiming SS is perfect for conversions while you let the SS benefit grow.

 

[Time2]

And sometimes you delay SS income so you can maximize Roth conversions and to get the younger pretty wife a bigger check, to share with her new boyfriend after you die.

 

[pb4uski]

I think of it more to have more money to drown her sorrows after I die.

 

[MichaelB]

I think of it more to have more money to drown her sorrows after I die.

Or celebrate?

 

[pb4uski]

No way!

 

[Midpack]

Did the OP ever disclose what his/her longevity expectations are? That’s the primary determinant for when to claim Soc Sec. Some of us know if we’re likely to live (well) under or over Soc Sec average age based on current/projected health, family history or other.

As others have noted, I’d be more comfortable using Open Social Security: Free, Open-Source Social Security Calculator and looking at results for various mortality (5 options plus any assumed age at death). If your spreadsheet is significantly different than OSS, I’d make sure I understand exactly why before making a final claiming date decision.

And OSS does not “spit out an absolute max benefit number as "best" without any context. You can look at the graph at the bottom and see every possible option by clicking on any point of interest.

 

[walkinwood]

Are any of these spreadsheet calculations taking taxes into account? After all, the amount you have to invest, if taking early, is reduced by the taxes you pay.

Then there is the longevity insurance aspect of delaying SS. What if your portfolio returns are towards the lower end of projections? I am skeptical of any calculation that uses a fixed percentage for future returns unless the portfolio is 100% TIPS. Models using historical returns or monte-carlo make me feel more comfortable especially if I can see the range of returns, not just the median.

Questions, Questions - I don't have a good answer except to rely on a program that can take all these aspects into consideration. I have only used one, Pralana Online/Gold, but there are others available.

 

[Koolau]

I guess I just don't understand the spread sheet OP presents - especially without example data.

For me, "maximizing" the SS income over a lifetime is not my key concern.

To me the major "argument" for early SS is "you need the money now." The greatest argument for waiting until 70 is "you want the maximum survivor benefit."

Returning you now...

 

[TheWizard]

Here we go again...

Indeed...

 

[Lawrencewendall]

Unless things have changed, empower.com lets you create a free account and allows one to create multiple retirement scenarios and compare them against each other. I even created several scenarios for my Wife when I kick the bucket at different ages.