Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

Hi all, I've updated the SS Benefit Spreadsheet.

[Edit: There is a draft of version 2: https://1drv.ms/x/s!Atj-cegEXidXx2pDBKnWaRLTXMe6 as announced later in this thread]

The current 2019 version is Version 1, and is available for download here: https://1drv.ms/x/s!Atj-cegEXidXx07Xr-4ypoXhDx8S

It takes into account the recently released Sept 2018 CPI numbers, as well as the new wage data for 2017. I've spot tested it against anypia version 2019.1 and it matches the output within \$1 for the cases I've tried (it's off, rarely, by a \$1 due to one particular rounding rule I'm too lazy to fix).

As always, let me know if you find any errors, and whether you find it useful.

Last edited by neurosphere on Sat Jan 05, 2019 4:27 pm, edited 2 times in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

Below is an example of the main output, which is a graph which displays your annual SS benefit as a function of retirement age and claiming date for a given set of earnings you've entered (actual or theoretical).

The top (grey) line is the benefit if you wait to age 70, while the bottom (light blue) line is age 62.

The example below assumed a person born in 1972, who started work at age 25, and assumes they will earn at least the maximum earnings until retirement. Suppose one wanted to know the benefit if he/she stopped working at age 53, and then claimed at age 69? The x-axis represents the assumed "stop-work" date (e.g. retirement). So go to 53 on the x-axis and look up to the line which represents a claiming age of 69 (top line is age 70, so it's the brown line just below it). You'll see that the benefit is just under \$40,000.

You can also see 3 hitches or elbows in the data. The first about about age 27 represents the first "bend point", the second at age 43 is the second bend point. The 3rd elbow at age 59 is where the earner has 35 years of earnings (and therefore all zero earning years have been eliminated from the calculations).

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

I've created an update, which I'm calling version 2 ("v2") DRAFT (because I've not kicked all the tires yet to make sure I didn't break something.
[edit on Nov 13, the link below contains some fixes after an error discovered as discussed below]
https://1drv.ms/x/s!Atj-cegEXidXx2pDBKnWaRLTXMe6

Changes include:

-- Taxes paid**: I've added a tab which keep track of your total/cumulative amount of employee SS taxes paid. But this only includes the "OASI" portion of the tax, not the amounts which SS attributes or allocates for "DI" and "HI". The listed dollar amounts for taxes paid is in terms of Sept 2018 dollars.

-- I increased the total range of ages one can have earnings. That is, the calculator now allows one as young as 18 years old to enter earnings data through 'very old'.

-- The y-axis on the benefit graph now scales to maximize the available chart area used. I had reasons for not scaling it earlier.

-- There is now a legend for each claiming age on the chart (e.g. age 62 to 70)

-- For anyone who wishes to enter non-zero assumptions for future wage and price inflation, I've moved that to a standalone tab.

As always, let me know if there are any questions! I'll make this the "official" update if no one finds any problems with it.

Neurosphere

** Note that SS is an INSURANCE program, and not an investment or retirement program. That is, it's not "fair" to directly compare the amount of tax you paid vs your expected/assumed benefit. For example, SS provides a benefit for your non-working spouse. This benefit was promised to you from your earliest earnings (assuming you eventually qualified for an old-age benefit). If you never had a spouse who earned any benefit on your record, you still HAD that potential benefit available, during a time when you theoretically may have or could have ended up with a spouse who could have had a benefit based only on your record. You may not put much value on that kind of insurance, but it has SOME value. Consider the data on taxes paid to be a form of education or amusement.
Last edited by neurosphere on Tue Nov 13, 2018 10:47 am, edited 2 times in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

UnclePennybags
Posts: 54
Joined: Fri May 09, 2014 10:50 am

I compared the results from this spreadsheet to my output from the prior version and it appears correct (which is to say that it matches the official website). The contribution section is interesting. Does the OASI portion only include employee contributions or does is also include the portion contributed by either the employer or through self-employment tax?

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote:
Thu Nov 08, 2018 11:39 am
I compared the results from this spreadsheet to my output from the prior version and it appears correct (which is to say that it matches the official website). The contribution section is interesting. Does the OASI portion only include employee contributions or does is also include the portion contributed by either the employer or through self-employment tax?
Employee contributions only.

Employer contributions (whether self or 'other') are not included. In theory, you could double the tax to get an estimate which includes the employer contribution, but recognizing that self-employed get a tax deduction for that part, and there are cases where the employer part can be more than simply double the employee tax if one has more than one employer and total earnings between the two of them exceed the max taxable earnings.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

Silk McCue
Posts: 1760
Joined: Thu Feb 25, 2016 7:11 pm

Sorry if it is an obvious answer or already provided that I have missed, but I downloaded and saved to my PC and the file is password protected when I try to make changes to the Inflation Adjustments tab.

Cheers

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

Silk McCue wrote:
Thu Nov 08, 2018 2:59 pm
Sorry if it is an obvious answer or already provided that I have missed, but I downloaded and saved to my PC and the file is password protected when I try to make changes to the Inflation Adjustments tab.
You didn't miss anything. You can change the peach colored boxes, which will change the assumptions for all future years. Currently you cannot change each assumption individually. I didn't figure people wanted to get that specific, and once you If you'd like to change each year individually, I can probably do that in a future update. Although, your question reminds me that now that the inflation assumptions are in a new tab, I can more easily provided instructions/info about the table right there on that page.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

Silk McCue
Posts: 1760
Joined: Thu Feb 25, 2016 7:11 pm

neurosphere wrote:
Thu Nov 08, 2018 3:05 pm
Silk McCue wrote:
Thu Nov 08, 2018 2:59 pm
Sorry if it is an obvious answer or already provided that I have missed, but I downloaded and saved to my PC and the file is password protected when I try to make changes to the Inflation Adjustments tab.
You didn't miss anything. You can change the peach colored boxes, which will change the assumptions for all future years. Currently you cannot change each assumption individually. I didn't figure people wanted to get that specific, and once you If you'd like to change each year individually, I can probably do that in a future update. Although, your question reminds me that now that the inflation assumptions are in a new tab, I can more easily provided instructions/info about the table right there on that page.
Thanks. I was was just moving to fast and focused on what I couldn't do and missed what I could do. That's a lot like life sometimes isn't it?

Cheers

UnclePennybags
Posts: 54
Joined: Fri May 09, 2014 10:50 am

Just started playing with some future year assumptions this afternoon and noticed that you have the SSWB set quite low for the next 13 years. Can you explain why that is? The indexing also looks a bit off to me as well.

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote:
Mon Nov 12, 2018 1:34 pm
Just started playing with some future year assumptions this afternoon and noticed that you have the SSWB set quite low for the next 13 years. Can you explain why that is? The indexing also looks a bit off to me as well.
I can't see your image. Can you give me an example of the problems you are seeing? Perhaps your image contained the example.

The SSWB is \$132,900 for 2019. If you keep the default assumptions of no increase in wage inflation, it will stay \$132,900 for all future years. This is the same assumption (i.e. no wage inflation) that SS uses in their online calculators and the their personalized benefit estimates. If you'd like to set your own wage inflation estimate, you can do so on the "inflation assumptions" tab.

As for the indexing being "off", can you give an example? I.e. give me a specific calendar year and an amount of earnings, and tell me what my calculator has as the output for indexed wages, vs what you think it should be instead?

J295
Posts: 1824
Joined: Sun Jan 01, 2012 11:40 pm

I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.

Bacchus01
Posts: 2225
Joined: Mon Dec 24, 2012 9:35 pm

neurosphere wrote:
Tue Nov 13, 2018 1:42 am
UnclePennybags wrote:
Mon Nov 12, 2018 1:34 pm
Just started playing with some future year assumptions this afternoon and noticed that you have the SSWB set quite low for the next 13 years. Can you explain why that is? The indexing also looks a bit off to me as well.
I can't see your image. Can you give me an example of the problems you are seeing? Perhaps your image contained the example.

The SSWB is \$132,900 for 2019. If you keep the default assumptions of no increase in wage inflation, it will stay \$132,900 for all future years. This is the same assumption (i.e. no wage inflation) that SS uses in their online calculators and the their personalized benefit estimates. If you'd like to set your own wage inflation estimate, you can do so on the "inflation assumptions" tab.

As for the indexing being "off", can you give an example? I.e. give me a specific calendar year and an amount of earnings, and tell me what my calculator has as the output for indexed wages, vs what you think it should be instead?
The \$132900 caught me off guard as well. I read and realize how it’s calc’d. I’m curious what people are actually assuming for their calculations? While that is the SSA method, it sure doesn’t seem practical.

UnclePennybags
Posts: 54
Joined: Fri May 09, 2014 10:50 am

Let me try linking the image a different way and see if that works:

Basically for 2020-2032, I'm seeing a SSWB < 132,900. It starts at 94,200 and gradually rises to hit 132,900 in 2033. Also, somehow those years are indexed to numbers > SSWB for 2020-2030. I haven't changed the Inflation estimates.

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

Bacchus01 wrote:
Tue Nov 13, 2018 7:49 am
The \$132900 caught me off guard as well. I read and realize how it’s calc’d. I’m curious what people are actually assuming for their calculations? While that is the SSA method, it sure doesn’t seem practical.
When you say "not practical" I assume you are referring to the SS default assumptions for inflation metrics when providing estimates? Which is to say they assume zero for wage and price inflation? It's very practical actually, because it's easy and all dollars remain in today's dollars. But it's certainly not accurate, which is probably what you mostly meant. SS's best guess for future wage and price inflation (aka the SS Trustees Intermediate Assumptions) are show in the table on the inflation assumptions tab. In the past, wage inflation has generally been higher than price inflation, and they assume this trend will continue. If so, then actual SS benefits will be LARGER (in today's dollars) than the estimates provided by SS. But who really knows what the future holds?

BolderBoy
Posts: 4239
Joined: Wed Apr 07, 2010 12:16 pm

J295 wrote:
Tue Nov 13, 2018 6:16 am
I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.
+1. Agreed completely. Thanks you!

Neurosphere, would it be possible to modify the graph to show the "bend points" which the SSA talks about on their calculator? The graph currently shows the point at which one ceases to generate FICA taxable income.

Thanks.
"Never underestimate one's capacity to overestimate one's abilities" - The Dunning-Kruger Effect

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote:
Tue Nov 13, 2018 9:49 am
Let me try linking the image a different way and see if that works:

Basically for 2020-2032, I'm seeing a SSWB < 132,900. It starts at 94,200 and gradually rises to hit 132,900 in 2033. Also, somehow those years are indexed to numbers > SSWB for 2020-2030. I haven't changed the Inflation estimates.
Yes, there is clearly a problem in the new draft version! It seems there is a disconnect in the values when the bottom of the first column transitions to the top of the second column. E.g. from 2019 to 2020 the SSWB gets lower. I broke something when expanding the range of dates/ages over which the calculator is valid...

Let me work on a fix!

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote:
Tue Nov 13, 2018 9:49 am
Let me try linking the image a different way and see if that works:
I've made the fix, here is the new link to this draft version: https://1drv.ms/x/s!Atj-cegEXidXx2pDBKnWaRLTXMe6
Let me know if it has fixed the problem for you. Many thanks for bringing it to our attention.

[Edit: I edited the link, same calculator but I inadvertently forgot to prevent it from being edited/changed online]
Last edited by neurosphere on Tue Nov 13, 2018 10:48 am, edited 1 time in total.

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm

BolderBoy wrote:
Tue Nov 13, 2018 10:08 am
J295 wrote:
Tue Nov 13, 2018 6:16 am
I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.
+1. Agreed completely. Thanks you!

Neurosphere, would it be possible to modify the graph to show the "bend points" which the SSA talks about on their calculator? The graph currently shows the point at which one ceases to generate FICA taxable income.
The graph shows the bend points by plotting the actual bends. You should see them in the graph. But there are some problems with that. For example, the first bend point may be off the chart to the right. Also, there can be multiple "bends" due to uneven earnings, when one reaches 35 years of earnings, and when 35 maximal years have been reached. So the actual second bend point may not be obvious. BUT, but the entire point of knowing what the second bend point is, is to know when benefits start to accrue at a slower rate than previously for give amount of earnings. The graph shows exactly what the slope is for any set of earnings, so one can see exactly what might be "gained" from more work. This makes knowing the bend point irrelevant, no? The graphs shows you that answer, plus much more.

That said, it would be nice to be able to label the bend points for educational/illustrative purposes.

I'm not sure my excel skills are good enough to add data to the chart, such that there is a special label or line which points to each bend point. But, that's something that would be fun to learn, perhaps. I could simply list what the bend points are, and one could find it on the graph. Maybe rather than label the graph, I could create a table that shows, for example, "bend points 1/2 = X/Y, and that is reached at ages Z and Q".
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

BolderBoy
Posts: 4239
Joined: Wed Apr 07, 2010 12:16 pm

neurosphere wrote:
Tue Nov 13, 2018 10:46 am
BolderBoy wrote:
Tue Nov 13, 2018 10:08 am
J295 wrote:
Tue Nov 13, 2018 6:16 am
I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.
+1. Agreed completely. Thanks you!

Neurosphere, would it be possible to modify the graph to show the "bend points" which the SSA talks about on their calculator? The graph currently shows the point at which one ceases to generate FICA taxable income.
The graph shows the bend points by plotting the actual bends. You should see them in the graph.
Ah! I see them now. I was interpreting the bends incorrectly.
That said, it would be nice to be able to label the bend points for educational/illustrative purposes.
Yes! I learned about "bend points" here on the forum.
I'm not sure my excel skills are good enough to add data to the chart, such that there is a special label or line which points to each bend point. But, that's something that would be fun to learn, perhaps. I could simply list what the bend points are, and one could find it on the graph. Maybe rather than label the graph, I could create a table that shows, for example, "bend points 1/2 = X/Y, and that is reached at ages Z and Q".
. Very well done on creating this. And the only problem I saw with the v2 (?) draft has been fixed in the latest draft (I can't keep straight which draft is which).
"Never underestimate one's capacity to overestimate one's abilities" - The Dunning-Kruger Effect

Topic Author
neurosphere
Posts: 3005
Joined: Sun Jan 17, 2010 1:55 pm