Downloadable Social Security Benefit Estimator [Updated for 2024]

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills.
boring.canadian
Posts: 1
Joined: Tue Mar 30, 2021 3:14 pm

Re: Neurosphere's 2021 Downloadable Social Security Benefit Estimator (updated with 2020 CPI-W and 2019 Wage data)

Post by boring.canadian »

neurosphere wrote: Tue Nov 17, 2020 3:44 pm [Post merged into here, see below. --admin LadyGeek]

Hi all, here's the new update for 2021. It's updated to reflect all the necessary data released in 2020, and should be valid until fall of 2021.

Here's the link: https://docs.google.com/spreadsheets/d/ ... sp=sharing

You should be able to save your own copy and enter in any set of earnings (and inflation) assumptions you would like.

Note that the SS Trustee assumptions for the future were released in April, and thus do NOT reflect any covid-related influence on the economy.

Also note that I have not yet kicked the tires, so consider this a work in progress!

I'll edit this post soon to include some links to previous threads which describe/explain this spreadsheet. I think I may have "skipped" a year in my naming system. From now on, I'm going to name the calculate for the most recent year for which the sswb (benefit and contributions base) is known. E.g. we know the SSWB for 2021 based on the 2019 data which was released in October 2020. :D
Newbie question here. I am unable to edit the input (peach) cells in the Google spreadsheet. Does it need to be unlocked, or something?
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Neurosphere's 2021 Downloadable Social Security Benefit Estimator (updated with 2020 CPI-W and 2019 Wage data)

Post by neurosphere »

boring.canadian wrote: Tue Mar 30, 2021 3:37 pm
neurosphere wrote: Tue Nov 17, 2020 3:44 pm [Post merged into here, see below. --admin LadyGeek]

Hi all, here's the new update for 2021. It's updated to reflect all the necessary data released in 2020, and should be valid until fall of 2021.

Here's the link: https://docs.google.com/spreadsheets/d/ ... sp=sharing

You should be able to save your own copy and enter in any set of earnings (and inflation) assumptions you would like.

Note that the SS Trustee assumptions for the future were released in April, and thus do NOT reflect any covid-related influence on the economy.

Also note that I have not yet kicked the tires, so consider this a work in progress!

I'll edit this post soon to include some links to previous threads which describe/explain this spreadsheet. I think I may have "skipped" a year in my naming system. From now on, I'm going to name the calculate for the most recent year for which the sswb (benefit and contributions base) is known. E.g. we know the SSWB for 2021 based on the 2019 data which was released in October 2020. :D
Newbie question here. I am unable to edit the input (peach) cells in the Google spreadsheet. Does it need to be unlocked, or something?
I believe you can go to File-->Download to download a personal copy you can play with. [Edit, consider instead "make a copy" which will create a Sheet you can modify with Sheets and not have to download a local copy].
Last edited by neurosphere on Sat Apr 03, 2021 3:11 pm, edited 1 time in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
gwe67
Posts: 1364
Joined: Mon Dec 23, 2013 8:52 pm

Re: Downloadable Social Security Benefit Estimator

Post by gwe67 »

I followed the input requirements, but get no results in the graph or table. I have tried both the Open Office and Excel versions. Any ideas what I'm doing wrong?
VTI 48%, VXUS 12%, BND 40%
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

gwe67 wrote: Sat Apr 03, 2021 2:54 pm I followed the input requirements, but get no results in the graph or table. I have tried both the Open Office and Excel versions. Any ideas what I'm doing wrong?
The current version at this link is written for Google Sheets: https://docs.google.com/spreadsheets/d/ ... sp=sharing

If you go to File --> "make a copy" and save your own version, you should be able to enter your data and see the results as you go. I don't recall if the version at this link also works with excel (I doubt it) or open office (never checked). Previous versions were excel only, but there was lots of request to create it for sheets instead, so right now that's the only current version. Let me know if it works.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
gwe67
Posts: 1364
Joined: Mon Dec 23, 2013 8:52 pm

Re: Downloadable Social Security Benefit Estimator

Post by gwe67 »

neurosphere wrote: Sat Apr 03, 2021 3:10 pm
gwe67 wrote: Sat Apr 03, 2021 2:54 pm I followed the input requirements, but get no results in the graph or table. I have tried both the Open Office and Excel versions. Any ideas what I'm doing wrong?
The current version at this link is written for Google Sheets: https://docs.google.com/spreadsheets/d/ ... sp=sharing

If you go to File --> "make a copy" and save your own version, you should be able to enter your data and see the results as you go. I don't recall if the version at this link also works with excel (I doubt it) or open office (never checked). Previous versions were excel only, but there was lots of request to create it for sheets instead, so right now that's the only current version. Let me know if it works.
Google Sheets would be my last choice, just because I don't trust Google. But it does indeed work in Google Sheets and provided me with the same values as SS's calculator. Too bad Open Office doesn't work...I only use Open Office. Maybe I can figure out a fix.
VTI 48%, VXUS 12%, BND 40%
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

gwe67 wrote: Sat Apr 03, 2021 4:05 pm Google Sheets would be my last choice, just because I don't trust Google. But it does indeed work in Google Sheets and provided me with the same values as SS's calculator. Too bad Open Office doesn't work...I only use Open Office. Maybe I can figure out a fix.
Understood. Previous users lamented the excel version because it required using a paid Microsoft product or creating a microsoft account. :D There are some formulas which only work in some subset of programs. It took me a dozen hours to find which excel formula Sheets didn't like. If you can find how to make it work in Open Office, others could download it, make the modification, and use in OO too.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
LadyGeek
Site Admin
Posts: 95695
Joined: Sat Dec 20, 2008 4:34 pm
Location: Philadelphia
Contact:

Re: Downloadable Social Security Benefit Estimator

Post by LadyGeek »

New member cantak has a question which I've moved to a new thread. See: [SSA calculator doesn't match my spreadhseet. What is wrong?]
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
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator

Post by Raspberry-503 »

Which of the CPI-W modes do people use?
The zero-inflation assumption will underestimate benefits for sure, and the further away you are from retirement the more pronounced that is.
Is mode "2" a better approximation then? it is off from this table: https://www.ssa.gov/oact/TR/TRassum.html because I assume it's using older data (unless I have an older version of the spreadsheet) but a better approximation than 0
longinvest
Posts: 5682
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator

Post by longinvest »

It's usually simpler to build a financial plan using inflation-adjusted dollars which corresponds to using a 0 inflation assumption in the estimator spreadsheet. While wage inflation might slightly outpace price inflation, it's not guaranteed and there's generally no need for (impossible to deliver) perfect precision given the many other uncertainties in a future retiree's financial plan.
Variable Percentage Withdrawal (bogleheads.org/wiki/VPW) | One-Fund Portfolio (bogleheads.org/forum/viewtopic.php?t=287967)
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator

Post by Raspberry-503 »

Ah, I understand the difference now. Mode 1 is in today's dollars whereas mode 2 is in the future year dollar. Makes sense then.
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

Raspberry-503 wrote: Mon Oct 11, 2021 9:15 am Ah, I understand the difference now. Mode 1 is in today's dollars whereas mode 2 is in the future year dollar. Makes sense then.
Actually, the default ("1") assumes that future price inflation and wage inflation is zero. Thus today's dollars and tomorrow's dollars are the same in that if there is no inflation a dollar I have today has the same purchasing power in the future.

Option "2" uses the SS Trustees "Intermediate" assumptions for future CPI and wage inflation released April 2020. This is what I call their "best guess" predictions. When selecting option 2, all the outputs are STILL in terms of today's dollars. That is, representing future purchasing power. The reason that the future estimated benefit when using option 2 is higher than option 1, is that wage inflation is usually higher than price inflation. If that continues to hold true, your actual SS benefit will be higher than when using a zero inflation assumption, based on how SS benefits are calculated.

Note that there is a more recent SS Trustee report (april 2021 I believe). I'll update the spreadsheet with the most recent predictions in a few weeks, at the same time I update it with the pending annual CPI and AWI values that are finalized later this month.
Last edited by neurosphere on Mon Oct 11, 2021 11:46 am, edited 1 time in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator

Post by Raspberry-503 »

Thanks, really appreciate the work you do on this
cadreamer2015
Posts: 1536
Joined: Wed Apr 08, 2015 1:52 pm
Location: North County San Diego

Re: Downloadable Social Security Benefit Estimator

Post by cadreamer2015 »

Like others have already said, I really appreciate the work you've done here. I'm wondering, now that the September 2021 CPI data have been released and the 2022 Social Security COLA determined, when we might get an updated version of your wonderful spreadsheet?

TIA
cadreamer2015
De gustibus non disputandum est
Gnomon
Posts: 47
Joined: Fri Sep 17, 2021 8:11 pm

Re: Downloadable Social Security Benefit Estimator

Post by Gnomon »

Hi neurosphere, thanks for sharing this and the work you've done on this in response to comments. I take it that you are not trying to match the variation of this calculation that SSA does in arriving at your age 62, 67, 70 benefit numbers that they insert into your annual downloadable statement?

I did a similar spreadsheet some years ago with a slightly different organization, just to try and understand all the steps in the calculations. I've dusted that off and I'm comparing it to yours. Mine was intended to match the SSA numbers in the downloadable annual statement, and when I update it with all the new numbers since I last used it, I get the age 62, 67, and 70 numbers in my spreadsheet to match the numbers in my downloadable statement.

As best I can tell, what accounts for the difference in the (lower) numbers in your spreadsheet is that in your spreadsheet, in the "main" tab and column K (indexed earnings) you've filled down with zeros from 2020 onward, whereas in the variation of the calculation SSA does, the 2020 max earnings of 137,700 are considered (your last entry is the 2019 max of 132,900) and that is filled down the column for the future years (i.e. they presume you continue working and earn the same as the last year in your current records). That has a ripple effect into AIME and PIA numbers, and would explain why your numbers are a bit lower than in my spreadsheet and in the downloadable statement. I'm just wanting to understand the differences (not criticizing), so does that sound right? And is the reason you've done it that way because your spreadsheet is presuming retirement (no more W2 earnings) at the series of ages from 62-70 you've shown (hence the zero-fill down), which is different than what the downloadable SSA numbers are indicating?
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

cadreamer2015 wrote: Sat Oct 16, 2021 12:27 pm Like others have already said, I really appreciate the work you've done here. I'm wondering, now that the September 2021 CPI data have been released and the 2022 Social Security COLA determined, when we might get an updated version of your wonderful spreadsheet?

TIA
cadreamer2015
Hi there. No timetable for an update. It's whenever I can steal some dedicated time without interruption. Hoping for next weekend but no promises. :D
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

Gnomon wrote: Mon Oct 18, 2021 12:13 am And is the reason you've done it that way because your spreadsheet is presuming retirement (no more W2 earnings) at the series of ages from 62-70 you've shown (hence the zero-fill down), which is different than what the downloadable SSA numbers are indicating?
My spreadsheet has historically matched the SS estimates on the form you're describing if one enters into my sheet the same set of assumptions used by SS. They assume a certain amount and duration of future earnings. If you enter those same earnings into the spreadsheet, and also select "1" for inflation assumptions (i.e. zero future wage or price inflation), then the numbers should be the same.
As best I can tell, what accounts for the difference in the (lower) numbers in your spreadsheet is that in your spreadsheet, in the "main" tab and column K (indexed earnings) you've filled down with zeros from 2020 onward,
There are zeros from 2020 onward only if you don't enter your own future earnings. You can enter any set of future earnings you want. For example, you might assume that you'll have have a certain set/pattern of earning up until you retire. But suppose you don't know when you will retire. So enter earnings up through age 90 (for example). You can now ask, "but what if I actually retire at age 63"? In that case, look to the x axis, find age 63, and look up to find the line matching the age you want to take your benefit. That point will be your future benefit for life (in terms of today's dollars). But now you can ask, ok, but what if I instead actually work two more years and retire at age 65 and start my benefit at age 70? The graph gives you all the future possibilities, once you provide a set of earnings (historical +/- future estimates).
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator

Post by Raspberry-503 »

I love that the spreadsheet lets you enter future years rather than making assumptions. The numbers in the SSA letter assume you keep working till you start collecting. This spreadsheet lets you figure out "what if i work till 59 but wait till 70 to start collecting". Or "what if start dialing work back at 55 for a lower salary..."
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

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

The latest version is now in google sheets. It's a draft version for 2022, updated in Oct 2021 and is here.

PLEASE NOTE: The spreadsheet is always in flux, and is never in any "final form", in that I cannot test all possible permutations and conditions for accuracy. You should consider this spreadsheet a template of sorts that I will update as people find possible issues. Also, do not request that I make this spreadsheet work in any other program (excel, open office, etc). I cannot learn/maintain multiple different versions at once, and certain formulas are not compatible between programs. :D

In order to use it, please download your own copy select "File-->Make a Copy" which will allow you to enter your own earnings history and future estimates. You do not need to request any privileges or permission.

The calculator was introduced in this thread, which has some more info and instructions: viewtopic.php?t=231913

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

I'll post some additional information about this calculator for those who are new to it in a reply within this thread.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator

Post by Raspberry-503 »

Much appreciated!
cadreamer2015
Posts: 1536
Joined: Wed Apr 08, 2015 1:52 pm
Location: North County San Diego

Re: Downloadable Social Security Benefit Estimator

Post by cadreamer2015 »

Thank you!!!
De gustibus non disputandum est
cadreamer2015
Posts: 1536
Joined: Wed Apr 08, 2015 1:52 pm
Location: North County San Diego

Re: Downloadable Social Security Benefit Estimator

Post by cadreamer2015 »

Thank you!!!
De gustibus non disputandum est
h8(N)++
Posts: 20
Joined: Sun May 15, 2016 12:11 am

Re: Downloadable Social Security Benefit Estimator

Post by h8(N)++ »

Neurosphere,

How is the PIA being calculated?

What sparked me to look under the hood is that your latest (2022) spreadsheet reports that I have a higher AIME than your 2021 spreadsheet reported one year ago (makes sense, I'm still working), but the reported PIA for 2022 is less than for 2021 (which doesn't make sense, at least not to me).

If I was a spreadsheet guru I'd know what the function =VLOOKUP(O$6,Main!$Q$39:$Z$94,10)/12 does, but I'm not, so have to ask.
I had naively expected to see a formula for PIA that added [90% of the AIME up to the first bend point] + [32% of the AIME that falls between the first and second bend points] + [15% of the AIME above the second bend point].
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

h8(N)++ wrote: Sun Oct 24, 2021 6:03 pm What sparked me to look under the hood is that your latest (2022) spreadsheet reports that I have a higher AIME than your 2021 spreadsheet reported one year ago (makes sense, I'm still working), but the reported PIA for 2022 is less than for 2021 (which doesn't make sense, at least not to me).

If I was a spreadsheet guru I'd know what the function =VLOOKUP(O$6,Main!$Q$39:$Z$94,10)/12 does, but I'm not, so have to ask.
I had naively expected to see a formula for PIA that added [90% of the AIME up to the first bend point] + [32% of the AIME that falls between the first and second bend points] + [15% of the AIME above the second bend point].
My very first guess as to why your PIA is less is because price inflation was higher than wage inflation this past year. In such a case, your inflation adjusted PIA (i.e. represented in today's dollars) will be less than previously. That is, inflation went up ~6%, but wages went up ~3%. So your benefit did not keep up with inflation for this particular year. On average in the past, wage inflation has tended to be higher than price inflation.

As to the PIA formula the "90% of ..." calculation is in there somewhere. I admit the spreadsheet is not well organized, and I have on my to-do list to "start over" with formulas and references that don't jump all over the place. Sorry about that! But "vlookup" is a formula which uses the first value (e.g. O$6) to be "looked up" in far left of a table (e.g. the table at q39:z94 in your example) and return the result of of the 10th column. In this case, the 10th column is the inflation adjusted PIA, calculated based on the values to the right. And those numbers calculate the contribution to the PIA of averaged earnings between bend points, such as below the 1st, between 1st and second, and above the second bend point. Hope that makes sense. :)
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
h8(N)++
Posts: 20
Joined: Sun May 15, 2016 12:11 am

Re: Downloadable Social Security Benefit Estimator

Post by h8(N)++ »

Thanks! I had wondered about the impact of 6% CPI vs 3% wage inflation. In retrospect what (I think) I missed was your comment on the instruction tab, that all values are reported in Sept 2020 dollars. My nominal PIA indeed went up over the past year (column V on the Main tab), but my real PIA (column Z divided by 12) (sadly) did not.
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

h8(N)++ wrote: Sun Oct 24, 2021 6:57 pm Thanks! I had wondered about the impact of 6% CPI vs 3% wage inflation. In retrospect what (I think) I missed was your comment on the instruction tab, that all values are reported in Sept 2020 dollars. My nominal PIA indeed went up over the past year (column V on the Main tab), but my real PIA (column Z divided by 12) (sadly) did not.
Whoops, this update reports the output in Sept 2021 dollars. I've fixed that comment in the instructions so that it now says 2021 rather than 2020.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
corn18
Posts: 2867
Joined: Fri May 22, 2015 6:24 am

Re: Downloadable Social Security Benefit Estimator

Post by corn18 »

Ouch. PIA went down $20 / month.
Consistently sets low goals and fails to achieve them.
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

Fyi, I'm getting a lot of email for "share requests" of the spreadsheet.

No need. You can download your own copy. Go to "file" at the top left then select "make a copy" and you should have your own version you can edit and enter in your own data.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

Hi all, I've updated the SS Benefit Spreadsheet. The link to the new version is here. I have NOT done any testing yet. Use at your own risk.

This spreadsheet allows you to enter any actual (or theoretical) past earnings history, as well as any future earnings assumptions, and from there will calculate your future benefit for any combination of benefit start date and retirement age.

It will also calculate "PIA", which can then be used as the input for Open Social Security calculator.

The latest version is a draft version for 2023, updated in Oct 2022.

Updates: In addition to updating with the recently release wage and price inflation numbers, I made a few additional changes:
-- Your benefit is determined by the top 35 years of indexed earnings. So I've now highlighted the top 35 years of indexed earnings in blue. If you haven't entered 35 non-zero years, the entire column will be blue.
-- All earnings are now entered into one long column. Previously this was broken into two columns in an attempt to get all the entry cells onto one page for easier viewing. However, this prevented the conditional formatting feature used to highlight the top 35 years of earnings.

--------------------

PLEASE NOTE: The spreadsheet is always in flux, and is never in any "final form", in that I cannot test all possible permutations and conditions for accuracy. You should consider this spreadsheet a template of sorts that I will update as people find possible issues. Also, do not request that I make this spreadsheet work in any other program (excel, open office, etc). I cannot learn/maintain multiple different versions at once, and certain formulas are not compatible between programs. :D

In order to use it, please download your own copy "File-->Make a Copy" which will allow you to enter your own earnings history and future estimates. You do not need to request any privileges or permission.

The calculator was introduced in this thread, which has some more info and instructions: viewtopic.php?t=231913

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

I'll post some additional information about this calculator for those who are new to it in a reply within this thread soon.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator

Post by neurosphere »

Below is an example of the output so I can summarize the main points/use of this spreadsheet. In this case, the earner is born in 1968, and with theoretical earnings entered through age 70.

The chart at the top shows your averaged indexed monthly earnings, the PIA and the future benefit based on claiming age. The future benefit is in terms of sept 2022 dollars.

A few things to note. In this example, although there are earnings entered until age 70, you can see the PIA in the chart stops increasing at age 64. That's because at age 64, new earnings are not in the top 35 (and are thus ignored).

The graph allows one to calculate the future benefit in terms of retirement age (i.e. the last year you expect you might have earnings) and your claiming date.

In this example, the earner has entered earnings through age 70. E.g. "if I continue to work, I expect to earn $X for future years". But looking at the chart, the earner can ask "but suppose I actually stop working at age 66, and claim at age 68...what is my benefit?" The benefit (y-axis) is at the point "age 66" on the X axis and the light blue line (age 68), for an approximate annual benefit of $43,000. If instead they only work until 60 and claim at 62, the benefit will be about $28,000

Image
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by Raspberry-503 »

This is one of my favorite spreadsheets and tools, it does what it does very well and I appreciate the constant updates. If anything it helped me understand a lot better how Social security benefits are calxulated. For example, I'm one the lucky ones to be beyond the 2nd inflection point, meaning from now on my benefits increase very slowly with additional years worked, so it's good input in whether I can retire earlier.

Taking it to the net step would be look at a couple's benefit, taking his and hers earning and when to claim spousal benefits and survivor benefits. I think I have worked through it for my particular case by hand, but I could see it could be useful.
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by Raspberry-503 »

Another interesting option is the ability to assume that the price and wage inflation will not be zero between now and retirement. The SSA uses 0, but for people many years from retirement it's likely to underestimate the benefits. So it's nice to have another (more realistic?) option.

Of course nobody knows what will happen to Social Security over the years, so this is just a starting point one can add additional assumptions to.
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

Raspberry-503 wrote: Sun Oct 16, 2022 12:26 pm Taking it to the net step would be look at a couple's benefit, taking his and hers earning and when to claim spousal benefits and survivor benefits. I think I have worked through it for my particular case by hand, but I could see it could be useful.
You can use this tool to calculate each spouse's possible future primary insurance amount (PIA) and then enter those values into open social security to determine your optimal claiming strategy.
Raspberry-503 wrote: Sun Oct 16, 2022 12:33 pm Another interesting option is the ability to assume that the price and wage inflation will not be zero between now and retirement. The SSA uses 0, but for people many years from retirement it's likely to underestimate the benefits. So it's nice to have another (more realistic?) option.

Of course nobody knows what will happen to Social Security over the years, so this is just a starting point one can add additional assumptions to.
This calculator allows you to select your inflation parameters. Under the graph there is a dropdown box. The default is set to "2" which uses the SS Trustees "best guess" for future wage and price inflation.

Setting the dropdown to "1" will use zero values for each. Finally, setting the value to "3" will allow the user to set their own future values, as per the tab "inflation assumptions". In this case however you can only enter one value each for wage inflation and price inflation, and will be applied each year to all future years. That is, you cannot model the effects of (for example) 1% for 5 years and 2% after that. But, it does allow you to see how your benefit might change when wage inflation is greater than price inflation, and vice versa.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
CloseNotClose
Posts: 5
Joined: Tue Oct 04, 2022 7:35 am

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by CloseNotClose »

Hi neurosphere. I love the spreadsheet, thanks for all the hard work on this! I especially like how much easier it is to run "what-if" scenarios with this tool than with others I've tried.

I do have a question for you about the PIA / benefits calculation. I've been trying to track down why I get different results (different by ~$200 per month) using your spreadsheet than using other tools. I see identical results for AIME and what I'll call "raw" PIA (column Y in the Main tab). The difference is coming from the calculation to convert raw PIA to inflation adjusted--this looks to be dividing the CPI-W value for 2020 (cell L64) by the CPI value at retiree's age 61. Since I'm not 61 yet, the latter value is defaulting to the most recent CPI-W value available, which is for 2022. The end result is that the reported PIA on the main page is the "raw" PIA multiplied by ~.868 -- this will be true for anyone under age 60 running this calculation.

So my question (sorry for the long intro :happy )--is the reduction based on CPI-W intentional, or is the use of 2020 CPI-W in the above calculation something that got missed on an update? The above calculation makes sense to me for people over age 61 as I know benefits scale with CPI after that age, but it's not obvious to me that that it makes sense before that. With that said, I'm still in the process of trying to fully understand the social security benefits calculation, so I'd love any education you can offer on what drives this calculation if it is in fact intentional.

Thanks!
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

CloseNotClose wrote: Tue Nov 15, 2022 6:33 am Hi neurosphere. I love the spreadsheet, thanks for all the hard work on this! I especially like how much easier it is to run "what-if" scenarios with this tool than with others I've tried.

I do have a question for you about the PIA / benefits calculation. I've been trying to track down why I get different results (different by ~$200 per month) using your spreadsheet than using other tools. I see identical results for AIME and what I'll call "raw" PIA (column Y in the Main tab). The difference is coming from the calculation to convert raw PIA to inflation adjusted--this looks to be dividing the CPI-W value for 2020 (cell L64) by the CPI value at retiree's age 61. Since I'm not 61 yet, the latter value is defaulting to the most recent CPI-W value available, which is for 2022. The end result is that the reported PIA on the main page is the "raw" PIA multiplied by ~.868 -- this will be true for anyone under age 60 running this calculation.

So my question (sorry for the long intro :happy )--is the reduction based on CPI-W intentional, or is the use of 2020 CPI-W in the above calculation something that got missed on an update? The above calculation makes sense to me for people over age 61 as I know benefits scale with CPI after that age, but it's not obvious to me that that it makes sense before that. With that said, I'm still in the process of trying to fully understand the social security benefits calculation, so I'd love any education you can offer on what drives this calculation if it is in fact intentional.

Thanks!
If you select "1" for the inflation assumptions in N41 on the input page, there will be no inflation adjustment and the PIA should match the benefit at age 67 (if that's your full retirement age). If you select "2" or "3", then both your AIME and PIA will be reported on the input/output page in inflation adjusted terms. That is, the "PIA" is in terms of today's dollars and why there is an "(inflation adjusted)" notation on the output page. It's indeed a little strange to inflation adjust the PIA, because when you get to age 62 it "is what it is" based on earnings history, and then an inflation adjustment (aka COLA) is applied TO the PIA at that time to determine your benefit. In my case, it's a type of intermediate calculation used to calculate your future benefit for the case where one want to use various inflation assumptions for wages and price.

Summary: you are correct that the PIA as shown on row 8 on the output page is not the simple future "actual" PIA. However, the benefit is indeed the benefit, in today's dollars.

Hope that makes sense and let me know if that doesn't answer your question or if you have others.

Edit to add: I'm traveling and looked at the spreadsheet in a small laptop rather than the giant double monitor set up I have. So still need to delve into your question and/or may have missed what you are trying to say because I can't easily go back and forth. So there very well may be a problem... :D Now that I read your post more closely, are you saying the "adjusted" PIA should be divided by the *2022* value (e.g. the most recent) rather than 2020? I haven't yet checked to see if that's what happening, just clarifying your question/concern.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
CloseNotClose
Posts: 5
Joined: Tue Oct 04, 2022 7:35 am

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by CloseNotClose »

Thanks for the reply! I should have mentioned in my initial question that I am using option 1 for the inflation adjustment setting.
Now that I read your post more closely, are you saying the "adjusted" PIA should be divided by the *2022* value (e.g. the most recent) rather than 2020? I haven't yet checked to see if that's what happening, just clarifying your question/concern.
Right now it's dividing the 2020 CPI value by the CPI value at age 61. Based on my (quite possibly flawed!) understanding--yes, I suspect that the numerator should be the 2022 value rather than the 2020 one. That would make the reported PIA values on the main page fully independent of the CPI series when using option 1 for anyone currently under 61, whereas now I believe they are reduced based on CPI for 2021/2022.
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

CloseNotClose wrote: Tue Nov 15, 2022 8:37 am Thanks for the reply! I should have mentioned in my initial question that I am using option 1 for the inflation adjustment setting.
Now that I read your post more closely, are you saying the "adjusted" PIA should be divided by the *2022* value (e.g. the most recent) rather than 2020? I haven't yet checked to see if that's what happening, just clarifying your question/concern.
Right now it's dividing the 2020 CPI value by the CPI value at age 61. Based on my (quite possibly flawed!) understanding--yes, I suspect that the numerator should be the 2022 value rather than the 2020 one. That would make the reported PIA values on the main page fully independent of the CPI series when using option 1 for anyone currently under 61, whereas now I believe they are reduced based on CPI for 2021/2022.
I believe that indeed you are correct. The numerator should be the most recent CPI value as of Sept 2022. I don't have the access to the spreadsheet right now but I'll need to confirm/fix this when I get a chance! Thanks!
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

CloseNotClose wrote: Tue Nov 15, 2022 6:33 am So my question (sorry for the long intro :happy )--is the reduction based on CPI-W intentional, or is the use of 2020 CPI-W in the above calculation something that got missed on an update?

Thanks!
Hi CloseNotClose. I'm finally home from travelling and had a chance to look at the sheet. The "inflation adjusted" PIA is now using the 2022 CPI number as the baseline rather than the 2020 CPI-W. If you have time, I'd appreciate you checking to see if you believe this issue is "fixed", as far as you can determine.

Thanks!
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
HobbesMB
Posts: 96
Joined: Mon Sep 18, 2017 1:36 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by HobbesMB »

Hi Neurosphere. Thanks for your spreadsheet - I've been using this sheet for a few years now and it has been very useful. I really like it.

However, I too am getting inconsistent results with your recent sheet. I ran the numbers for myself and my wife using the most recent version you just updated per your last post. The PIA for myself comes out correct, but it is not correct for my wife's numbers (as compared to AnyPIA and SSA.Tools). It's $242 off from those other tools. I'm not sure why it would work for one and not the other.
CloseNotClose
Posts: 5
Joined: Tue Oct 04, 2022 7:35 am

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by CloseNotClose »

neurosphere wrote: Thu Nov 24, 2022 11:13 am Hi CloseNotClose. I'm finally home from travelling and had a chance to look at the sheet. The "inflation adjusted" PIA is now using the 2022 CPI number as the baseline rather than the 2020 CPI-W. If you have time, I'd appreciate you checking to see if you believe this issue is "fixed", as far as you can determine.

Thanks!
Thanks Neurosphere! Post-update, the spreadsheet matches the results from SSA Tools for both myself and my wife--looks good to me.
HobbesMB wrote: Thu Nov 24, 2022 10:01 pm Hi Neurosphere. Thanks for your spreadsheet - I've been using this sheet for a few years now and it has been very useful. I really like it.

However, I too am getting inconsistent results with your recent sheet. I ran the numbers for myself and my wife using the most recent version you just updated per your last post. The PIA for myself comes out correct, but it is not correct for my wife's numbers (as compared to AnyPIA and SSA.Tools). It's $242 off from those other tools. I'm not sure why it would work for one and not the other.
Pure speculation on my part, but did you make sure you set the inflation assumptions dropdown to "1" for your wife? I ask only because when I was comparing my own numbers just now I initially left that at "2" and was confused as to why the numbers suddenly looked so much higher :happy
HobbesMB
Posts: 96
Joined: Mon Sep 18, 2017 1:36 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by HobbesMB »

CloseNotClose wrote: Fri Nov 25, 2022 8:49 am Pure speculation on my part, but did you make sure you set the inflation assumptions dropdown to "1" for your wife? I ask only because when I was comparing my own numbers just now I initially left that at "2" and was confused as to why the numbers suddenly looked so much higher :happy
Yep, the dropdown is set to "1" for both. In fact, for my wife's sheet changing the inflation box from "1" to "2" to "3" doesn't change the output at all, but it does for mine.
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

Hi all. I've updated the spreadsheet to fix an error. This error led to incorrect results in the TABLE in certain cases when there were earnings entered for the years 2050 or later. The graphical output for SS benefits was correct however.

Now, the spreadsheet works for earnings up through 2078.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

Reminder, do not request access or for the spreadsheet to be shared with you. In order to use the spreadsheet you must make a personal copy (if you have a gmail account with Drive) or otherwise "download" your own copy.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
MedDev
Posts: 8
Joined: Thu Apr 09, 2020 5:30 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by MedDev »

Hey neurosphere! I helped years ago when you were somewhat reluctant(?) to fix a small issue for google sheets to work. I forgot the simple fix we worked on, but I'm glad to see that the sheets version made it through to a new version. I'm comparing the results from a official SS provided statement from 2017 just to test if I get approximately the same results - I don't and I'm trying to debug. Off by $555/month which is enough for me to want to figure it out.

I'm using their # from the "Your Taxed Social Security Earnings" column and then following what they assume, took the last earning of $118,500 in 2016 and populated to 67 which it seems to be what they are saying. "At your current rate, if you continue working until....your full retirement age (67 yo), your payment would be about..." $555 less/month that what this new sheet is showing. Any thoughts?

You deserve a lot of credit keeping this up over the years and deserve a huge thanks!!! :beer
User avatar
FiveK
Posts: 15742
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by FiveK »

MedDev wrote: Mon Jun 19, 2023 3:42 pm ...I don't and I'm trying to debug.
You could compare with a couple of other good tools: the 'SocialSecurity' tab of the personal finance toolbox spreadsheet, and the Social Security Calculator.

Best two out of three wins? ;)
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

MedDev wrote: Mon Jun 19, 2023 3:42 pm Hey neurosphere! I helped years ago when you were somewhat reluctant(?) to fix a small issue for google sheets to work. I forgot the simple fix we worked on, but I'm glad to see that the sheets version made it through to a new version. I'm comparing the results from a official SS provided statement from 2017 just to test if I get approximately the same results - I don't and I'm trying to debug. Off by $555/month which is enough for me to want to figure it out.

I'm using their # from the "Your Taxed Social Security Earnings" column and then following what they assume, took the last earning of $118,500 in 2016 and populated to 67 which it seems to be what they are saying. "At your current rate, if you continue working until....your full retirement age (67 yo), your payment would be about..." $555 less/month that what this new sheet is showing. Any thoughts?

You deserve a lot of credit keeping this up over the years and deserve a huge thanks!!! :beer
Hi MedDev! Off the top of my head, I don't think you can use a past estimate statement to test the spreadsheet. Because since 2016 we have actual data which applies to subsequent years and is included in the this spreadsheet, while that statement makes other assumptions, for example concerning future wage inflation. In general and depending on where one is getting the estimate (mailed vs online vs other SS calculator), the SS provided estimates will underestimate future benefits.

And yes, you helped me figure out that sheets treats arrays differently than excel (needed to add "arrayformula"), so with a small change to one cell it ported to Sheets correctly and here we are! :)
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

FiveK wrote: Mon Jun 19, 2023 5:56 pm
MedDev wrote: Mon Jun 19, 2023 3:42 pm ...I don't and I'm trying to debug.
You could compare with a couple of other good tools: the 'SocialSecurity' tab of the personal finance toolbox spreadsheet, and the Social Security Calculator.

Best two out of three wins? ;)
With the default assumptions of no price inflation and no wage inflation (the default setting in the personal finance toolbox) our numbers match. When using the Trustees intermediate estimate for both tools, they don't agree. But my spreadsheet outputs the number in terms of today's dollars, while I assume the personal finance toolkit outputs in terms of future dollars.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Harry Livermore
Posts: 1937
Joined: Thu Apr 04, 2019 5:32 am

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by Harry Livermore »

Just another data point. I copy/pasted my earnings history from a previous version of this spreadsheet (2019?) into the current iteration, and added the last few years. I then plugged in last year's number into each cell until age 67. The result matches exactly the SSA benefits estimator PDF I downloaded from the SS website earlier this spring.
Thanks again for another tool in the toolbox, neurosphere!
Cheers
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

Harry Livermore wrote: Tue Jun 20, 2023 4:18 pm Just another data point. I copy/pasted my earnings history from a previous version of this spreadsheet (2019?) into the current iteration, and added the last few years. I then plugged in last year's number into each cell until age 67. The result matches exactly the SSA benefits estimator PDF I downloaded from the SS website earlier this spring.
Thanks again for another tool in the toolbox, neurosphere!
You're welcome! Thanks for the data point.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
User avatar
Raspberry-503
Posts: 954
Joined: Sat Oct 03, 2020 6:42 am

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by Raspberry-503 »

So I referenced this thread in another (viewtopic.php?p=7468163#p7468163), and user MoreTaxes had this to say:
I looked at Neurosphere's spreadsheet. It says that SSA assumes NAWI growth will be zero. That's not true. SSA assumes NAWI growth will match price inflation.That's why the inflation index is set to 1.0 for the future years, for expressing future-year salaries in today's dollars.
look in that thread for other comments/suggestions, but I'm curious about the comment above because it's still mostly a mystery to me what method 1 vs 2 means and which one is the most accurate
User avatar
Topic Author
neurosphere
Posts: 5205
Joined: Sun Jan 17, 2010 12:55 pm

Re: Downloadable Social Security Benefit Estimator [Updated for 2023]

Post by neurosphere »

Raspberry-503 wrote: Wed Sep 20, 2023 12:39 am So I referenced this thread in another (viewtopic.php?p=7468163#p7468163), and user MoreTaxes had this to say:
I looked at Neurosphere's spreadsheet. It says that SSA assumes NAWI growth will be zero. That's not true. SSA assumes NAWI growth will match price inflation.That's why the inflation index is set to 1.0 for the future years, for expressing future-year salaries in today's dollars.
look in that thread for other comments/suggestions, but I'm curious about the comment above because it's still mostly a mystery to me what method 1 vs 2 means and which one is the most accurate
Method 1 assumes wage growth equals zero, and price growth equals zero. Method 2 assumes the values in columns E and F on the tab "inflation assumptions. Method 1 is what many of the SS provided estimates use (wage growth = price growth = zero). The numbers used for method 2 are the "best guess" estimates as provided by the SS Trustees. It's impossible to know which method is more accurate because one can't predict the future. That said, method 2 better matches history, in that looking backwards, both price and wage growth have averaged greater than zero, and that wage growth has been higher than price growth. Note that if the future resembles the past, Method 2 provides for a larger benefit.

Note that different SS calculators and provided benefits estimates use different sets of assumptions. For example, the Online Benefit Calculator (https://www.ssa.gov/benefits/retirement ... pplet.html) uses the SS Intermediate Assumptions (https://www.ssa.gov/OACT/TR/TRassum.html) my Method 2, and allows for reporting the output in terms of today's dollars or future dollars. My calculator only reports values in terms of "today's dollars" which is currently set as of the CPI set in Sept 2022. Later this fall I'll update my calculator with the new CPI adjustments as used by SS and which in general are not available until late October.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes" (even in taxable accounts).
Post Reply