Downloadable Social Security Benefit Estimator (2019 Update)

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

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sun Oct 13, 2019 12:41 pm

longinvest wrote:
Sun Oct 13, 2019 10:48 am
neurosphere wrote:
Sun Oct 13, 2019 10:38 am
I'm working on a small addition/feature, and am currently trying to teach myself how to use radio buttons. :)
Do you really need to do that?
Lol. Fine. Now I'm going to maintain two versions. :wink: I guess there are ways I can make my updates without active elements (e.g. enter "1" for option 1, "2" for option 2, etc), but that's no fun, lol.

Here is a version free of active elements. I'm calling it "Neurosphere's 2020 Shared Social Security Benefit Estimator v2". 2020 refers to the fact it's valid from Oct 2019 through Oct 2020. "v2" is version 2, which I will update as I go based on suggestions/problems etc.

Here is version with radio buttons where one can toggle between various assumptions for price and wage inflation, which I think is a VERY COOL feature (imho).

Note that there is currently no way from me to test my calculator against SS's numbers and I make no claims about accuracy (i.e. anypia has not yet been updated). The online calculators are not yet updated either, but these do not take non-zero inflation assumptions into account anyway, so provide limited testing. I have not yet done any troubleshooting or stress testing. Consider this a beta version (or zeta version, lol).

The links above should allow one to download the calculators from one drive. I've created these in whatever the current version of Excel is. They are saved as .xlsx or .xlsm files. I'll admit I won't have much interest/time in making these backwards compatible with previous versions of excel, or ensuring they work in non-excel spreadsheets. Microsoft 365 costs $70/year. :wink: I don't have the patience/time to make this hobby/fun project into a chore/obligation. Hope that doesn't sound too mean. :D

Hopefully a few of you can kick the tires, and at some point I'll create a new thread to announce it. I'm also hoping to create a video walkthrough, to show how to use it and point out what I think are some of the cool features.

[Edit to add: Crap. It seems I didn't update from the latest version I had. My bad. So while the links above will go to valid calculators with numbers which I think are correct, the layout and some previously added features are altered/lost. I'm going to work on updates, because I think the previous valid version was a little more user friendly and looked nicer. Dang.]

Big Dog
Posts: 1611
Joined: Mon Sep 07, 2015 4:12 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by Big Dog » Sun Oct 13, 2019 1:08 pm

if there a trick to getting in?


When I click on the link, I get a request from Microsoft One Drive for a pw. (I get the same prompt even when I click on the ver that is not password protected...)

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sun Oct 13, 2019 1:16 pm

Big Dog wrote:
Sun Oct 13, 2019 1:08 pm
if there a trick to getting in?


When I click on the link, I get a request from Microsoft One Drive for a pw. (I get the same prompt even when I click on the ver that is not password protected...)
When I log out of onedrive and click on the link, it opens the spreadsheet in a browser (I'm using chrome) without a password request, and then I can click on the download button.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

mouth
Posts: 165
Joined: Sun Apr 19, 2015 6:40 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by mouth » Sun Oct 13, 2019 1:30 pm

Harry Livermore wrote:
Sat May 25, 2019 10:04 pm
neurosphere wrote:
Sat May 25, 2019 12:01 pm
Harry Livermore wrote:
Sat May 25, 2019 11:18 am
Ha. Yes, I see that now. I entered nothing after 2018.
I will play around with future years and see what happens. Thanks for the quick response, and of course, thanks again for creating a terrific tool.
What happens if you take your last year of earnings, and put that in the calculator from today forward. Does my result match the SS estimate? It should be +/- a small percentage, because my calculator essentially assumes a Jan 2 birthdate and retirement in January also.
Yes! It matches exactly.
Since I assume that my career (self employed in a field that favors youth) will be on a long slow decline from here on out, it's unlikely that I'll earn the way I have, consistently until retirement. So it's really handy that your spreadsheet allows for a DIFFERENT set of assumptions.
As other posters have pointed out, it's astounding how little of a boost you get for many thousands of additional dollars earned if your AIME is already high, as mine is...
Cheers,
Harry
Harry, another interesting facet I'm seeing (for my earnings profile at least) is the diminishing returns I see from additional working years (beyond this year) as my Top 35 years trade lean years for fat future years (I'm now maxing my taxable SS income).

Early in my working life, first as a military cadet and then as a 2Lt/O-1, my taxed SS income was pretty low compared to the years after that. So replacing those with new high earning years has a big impact on my AMIE. But I'm also 1 year away from the 15% bend. So for four more years from today I see a modest PIA boost (despite being in the 15% section) because my AMIE jumps a fair bit. But after that is a long period of really diminishing returns because the earnings-years I'm replacing aren't that low anymore.

Good problems to have for sure. But it really hammers home the idea that when I turn 48 next year, and I cross the 15% bend point, and again at 51 when my AMIE sees progressively smaller gains, I'm not working for SS but savings.

What I learned is that I should stop working at 51 and then delay my claim until 70 for the best mix of work-years vs. SS benefit. Working longer than 51 only makes sense to boost savings or to stay busy.

Thank you @neurosphere !!!

Big Dog
Posts: 1611
Joined: Mon Sep 07, 2015 4:12 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by Big Dog » Sun Oct 13, 2019 1:31 pm

thanks for responding so quickly, but I still must be doing something wrong. The only thing I had open was outlook, so I closed that and still get a request for a pw (to access 'sensitive information...') in Safari.

Thanks anyway.

mouth
Posts: 165
Joined: Sun Apr 19, 2015 6:40 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by mouth » Sun Oct 13, 2019 1:43 pm

neurosphere wrote:
Sun Oct 13, 2019 12:41 pm
Instead of radio buttons, and two versions, why not just present the user with a 0/1 toggle (which is all radio buttons do in the background) so people can choose what features they want. If things are mutually exclusive, and you don't want to use data validation for compatibility reason, you could create alert cells that show ERROR if there is an incompatible combination chosen.

Just an idea of how to provide a UI without using active elements

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sun Oct 13, 2019 2:47 pm

neurosphere wrote:
Sun Oct 13, 2019 1:16 pm
Big Dog wrote:
Sun Oct 13, 2019 1:08 pm
if there a trick to getting in?


When I click on the link, I get a request from Microsoft One Drive for a pw. (I get the same prompt even when I click on the ver that is not password protected...)
When I log out of onedrive and click on the link, it opens the spreadsheet in a browser (I'm using chrome) without a password request, and then I can click on the download button.
Use chrome instead of safari, that should work. I don't know why safari doesn't work.

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sun Oct 13, 2019 2:57 pm

Thanks for the feedback so far everyone.

Ok, here is a new version (Neurosphere's 2020.....v4).

The main addition from the 2019 version is that one can now more easily select which set of inflation estimates to use, via a drop down list under the main graph on the main output page (second tab).

The default numbers ("1") is what SS uses for their online calculators and "mailed" estimates. I can never keep track when/who/how get those in the mail vs need to actively download them. :D

Selecting "2" for inflation estimates will change the output to match the Social Security Trustees "Intermediate" assumptions, which is their version of their "best guess" for what might happen in the short and long term.

"3" will use your assumptions as entered in the "inflation assumptions" tabs in the peach boxes. Note that in this case, you can only apply the same assumptions for all future years.

Big Dog
Posts: 1611
Joined: Mon Sep 07, 2015 4:12 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by Big Dog » Sun Oct 13, 2019 3:16 pm

awesome work, thanks so much neurosphere.

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Fri Nov 01, 2019 7:00 pm

neurosphere wrote:
Sun Oct 13, 2019 2:57 pm
Thanks for the feedback so far everyone.

Ok, here is a new version (Neurosphere's 2020.....v4).

The main addition from the 2019 version is that one can now more easily select which set of inflation estimates to use, via a drop down list under the main graph on the main output page (second tab).
Neurosphere, I've tested your spreadsheet with the hypothetical retiree of the VPW forward test thread.

This retiree was born in 1954 and had the following salary history, starting in 1979 ($3,506) until 2019 ($20,347), inclusively:

Code: Select all

3506
7931
9094
9921
10931
11574
12126
12860
13412
13779
14698
15708
16351
16994
17637
18556
18739
19199
20026
20944
22230
23333
24619
25996
26639
26915
27558
28844
29855
31232
32702
32702
32702
33713
34815
35826
36285
36285
38949
39316
20347
I left the inflation assumption to "1" as I'm interested in inflation-adjusted future amounts.

I anticipated the 2020 update spreadsheet projection, at age 70, to have increased a little over the $2,000 projection of the 2019 update. It didn't. It still projects $2,000 (but in 2020 dollars).

Is this normal?
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sat Nov 02, 2019 9:19 am

longinvest wrote:
Fri Nov 01, 2019 7:00 pm
I anticipated the 2020 update spreadsheet projection, at age 70, to have increased a little over the $2,000 projection of the 2019 update. It didn't. It still projects $2,000 (but in 2020 dollars).

Is this normal?
Yes, it's normal/correct.

A person born in 1965 is over the age of 60. Which means that updates/changes in the wage index do not affect the calculations because the indexing is fixed with the values available at age 60/62 (that is, around age 62, the wage index for age 60 is available and set as the reference).

So the only thing affecting the benefit after age 60 is 1) additional earnings which change the AIME, if any and 2) inflation. In the absence of additional earnings, an inflation adjusted benefit remains the same, because, well, an inflation adjustment to today's dollars essentially removes/normalizes the effect of inflation.

So for a person born in 1954 an with the earnings history/future as in your example, the benefit will never change (in terms of "today's" dollars). :D

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Nov 02, 2019 9:29 am

neurosphere wrote:
Sat Nov 02, 2019 9:19 am
So for a person born in 1954 an with the earnings history/future as in your example, the benefit will never change (in terms of "today's" dollars). :D
The projected benefit was $2,000 at age 70 expressed in 2019 dollars using last year's spreadsheet. We both agree that this won't change as the hypothetical retiree won't go back to work.

As the pension amount expressed in 2020 dollars, using the updated spreadsheet, is still $2,000, this implies that there was no inflation in 2019 in view of Social Security calculations.

This surprises me.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sat Nov 02, 2019 10:01 am

longinvest wrote:
Sat Nov 02, 2019 9:29 am
neurosphere wrote:
Sat Nov 02, 2019 9:19 am
So for a person born in 1954 an with the earnings history/future as in your example, the benefit will never change (in terms of "today's" dollars). :D
The projected benefit was $2,000 at age 70 expressed in 2019 dollars using last year's spreadsheet. We both agree that this won't change as the hypothetical retiree won't go back to work.

As the pension amount expressed in 2020 dollars, using the updated spreadsheet, is still $2,000, this implies that there was no inflation in 2019 in view of Social Security calculations.

This surprises me.
Oh, I understand. Yes, error in my spreadsheet did not reflect the new CPI numbers. It's should now be fixed if you download from the same link. The age 70 benefit is now $2032.

The link to the VPW assumptions said 2019 dollars, and my calculator is in 2019 dollars too, so expected $2000 to stay $2000. But I think those are different "2019" dollars.

Thanks for pointing that out.

But just to remind everyone, here was my disclaimer:
Note that there is currently no way from me to test my calculator against SS's numbers and I make no claims about accuracy (i.e. anypia has not yet been updated). The online calculators are not yet updated either, but these do not take non-zero inflation assumptions into account anyway, so provide limited testing. I have not yet done any troubleshooting or stress testing. Consider this a beta version (or zeta version, lol).
:D :D

When the new version of anypia comes out I typically run a few scenarios to compare, and then when other point out possible errors with other scenarios/assumptions, I run it in both versions and it helps me much more quickly see where a problem might be.

In any case, thanks! I had updated my spreadsheet with the new CPI values, but hadn't pointed the appropriate formula to this new value (I really should automate this, all I should have to do is enter the new wage and cpi number in one cell and the rest should just work without having to update other fields...it's on the to-do list, lol).
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Nov 02, 2019 10:26 am

neurosphere wrote:
Sat Nov 02, 2019 10:01 am
Oh, I understand. Yes, error in my spreadsheet did not reflect the new CPI numbers. It's should now be fixed if you download from the same link. The age 70 benefit is now $2032.
Thanks!
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sat Nov 02, 2019 12:08 pm

neurosphere wrote:
Sat Nov 02, 2019 9:19 am
longinvest wrote:
Fri Nov 01, 2019 7:00 pm
I anticipated the 2020 update spreadsheet projection, at age 70, to have increased a little over the $2,000 projection of the 2019 update. It didn't. It still projects $2,000 (but in 2020 dollars).

Is this normal?
Yes, it's normal/correct. [Edit, see a subsequent post which admits longinvest was correct that something was wrong]

A person born in 1965 is over the age of 60. Which means that updates/changes in the wage index do not affect the calculations because the indexing is fixed with the values available at age 60/62 (that is, around age 62, the wage index for age 60 is available and set as the reference).

So the only thing affecting the benefit after age 60 is 1) additional earnings which change the AIME, if any and 2) inflation. In the absence of additional earnings, an inflation adjusted benefit remains the same, because, well, an inflation adjustment to today's dollars essentially removes/normalizes the effect of inflation.

So for a person born in 1954 an with the earnings history/future as in your example, the benefit will never change (in terms of "today's" dollars). :D
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Nov 02, 2019 4:08 pm

neurosphere wrote:
Sat Nov 02, 2019 10:01 am
But just to remind everyone, here was my disclaimer:
Note that there is currently no way from me to test my calculator against SS's numbers and I make no claims about accuracy (i.e. anypia has not yet been updated). The online calculators are not yet updated either, but these do not take non-zero inflation assumptions into account anyway, so provide limited testing. I have not yet done any troubleshooting or stress testing. Consider this a beta version (or zeta version, lol).
:D :D

When the new version of anypia comes out I typically run a few scenarios to compare, and then when other point out possible errors with other scenarios/assumptions, I run it in both versions and it helps me much more quickly see where a problem might be.
It's interesting to look at this spreadsheet along with the 'SocialSecurity' tab in the personal finance toolbox spreadsheet and the web-based Social Security Calculator.

Appears the latter tool doesn't yet include the latest COLA, but the toolbox spreadsheet was updated recently. Usually all three tools give about the same answers.

Using longinvest's numbers from viewtopic.php?p=4819827#p4819827, for age 70 the toolbox spreadsheet gets
AIME = 2933
Base PIA = 1435
COLA PIA = 1533
Monthly benefit = 2023

The difference between 2023 and 2032 is somewhat larger than the one or two dollars that different rounding schemes often cause, but I don't know which - if either ;) - is correct.

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Nov 02, 2019 4:12 pm

FiveK wrote:
Sat Nov 02, 2019 4:08 pm
The difference between 2023 and 2032 is somewhat larger than the one or two dollars that different rounding schemes often cause, but I don't know which - if either ;) - is correct.
FiveK, using the same salary history but the 2019 version of the toolbox spreadsheet, do you get a $2,000 projection at age 70?
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Nov 02, 2019 4:22 pm

longinvest wrote:
Sat Nov 02, 2019 4:12 pm
FiveK wrote:
Sat Nov 02, 2019 4:08 pm
The difference between 2023 and 2032 is somewhat larger than the one or two dollars that different rounding schemes often cause, but I don't know which - if either ;) - is correct.
FiveK, using the same salary history but the 2019 version of the toolbox spreadsheet, do you get a $2,000 projection at age 70?
No, I needed a slightly higher salary history for that. See viewtopic.php?p=4623526#p4623526.

With the salary history you used I get $1992 when the recent 1.6% COLA is not included.

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Nov 02, 2019 4:35 pm

FiveK wrote:
Sat Nov 02, 2019 4:22 pm
longinvest wrote:
Sat Nov 02, 2019 4:12 pm
FiveK wrote:
Sat Nov 02, 2019 4:08 pm
The difference between 2023 and 2032 is somewhat larger than the one or two dollars that different rounding schemes often cause, but I don't know which - if either ;) - is correct.
FiveK, using the same salary history but the 2019 version of the toolbox spreadsheet, do you get a $2,000 projection at age 70?
No, I needed a slightly higher salary history for that. See viewtopic.php?p=4623526#p4623526.
This is an annoying discrepancy. Is the difference big? What does the toolbox project, in 2019 dollars?

I don't know if it makes a difference, but it's assumed that the retiree was born in mid 1954, started working on July 1st 1979 (at age 25) and enjoyed a first day of retirement on July 1, 2019 (at age 65).
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Nov 02, 2019 4:41 pm

longinvest wrote:
Sat Nov 02, 2019 4:35 pm
This is an annoying discrepancy. Is the difference big? What does the toolbox project, in 2019 dollars?

I don't know if it makes a difference, but it's assumed that the retiree was born in mid 1954, started working on July 1st 1979 (at age 25) and enjoyed a first day of retirement on July 1, 2019 (at age 65).
With the salary history you used I get $1992 when the recent 1.6% COLA is not included. (added that to the earlier post, probably while you were typing yours ;))

Birthdays on the first of any month (in particular, 1-Jan) can cause discrepancies, but generic "born in 1954 and started benefits at age 70 years and 0 months" calculations should be consistent.

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Nov 02, 2019 4:59 pm

FiveK wrote:
Sat Nov 02, 2019 4:41 pm
longinvest wrote:
Sat Nov 02, 2019 4:35 pm
This is an annoying discrepancy. Is the difference big? What does the toolbox project, in 2019 dollars?
With the salary history you used I get $1992 when the recent 1.6% COLA is not included. (added that to the earlier post, probably while you were typing yours ;))
Thanks. That's $8 or 0.4% short of the projection I got with Neurosphere's estimator.
FiveK wrote:
Sat Nov 02, 2019 4:41 pm
generic "born in 1954 and started benefits at age 70 years and 0 months" calculations should be consistent.
I agree. So, which of the two spreadsheets is making the correct projection?

There's still some time to figure things out. I'd like to update the Social Security projection in two months for the December 31, 2019 entry of the VPW forward test.

Thank you both for your work on these spreadsheets.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Nov 02, 2019 5:08 pm

longinvest wrote:
Sat Nov 02, 2019 4:59 pm
So, which of the two spreadsheets is making the correct projection?
Don't know.

All the calculations in the toolbox spreadsheet are exposed, but in the other two tools (I think) some calculations are hidden. From what I could tell, the toolbox calculations follow the SSA formulas, but deciphering Excel formulas, even when visible, is not always easy. ;)

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sat Nov 02, 2019 5:16 pm

longinvest wrote:
Sat Nov 02, 2019 4:59 pm
FiveK wrote:
Sat Nov 02, 2019 4:41 pm
longinvest wrote:
Sat Nov 02, 2019 4:35 pm
This is an annoying discrepancy. Is the difference big? What does the toolbox project, in 2019 dollars?
With the salary history you used I get $1992 when the recent 1.6% COLA is not included. (added that to the earlier post, probably while you were typing yours ;))
Thanks. That's $8 or 0.4% short of the projection I got with Neurosphere's estimator.
FiveK wrote:
Sat Nov 02, 2019 4:41 pm
generic "born in 1954 and started benefits at age 70 years and 0 months" calculations should be consistent.
I agree. So, which of the two spreadsheets is making the correct projection?

There's still some time to figure things out. I'd like to update the Social Security projection in two months for the December 31, 2019 entry of the VPW forward test.

Thank you both for your work on these spreadsheets.
For what it's worth, one online social security calculator (https://www.ssa.gov/planners/retire/AnypiaApplet.html) shows a benefit of $1992 for one born Jan 2 or later during 1954. The page says "*The most recent calculator update was in February, 2019." If $1992 is the same value that toolbox gets, I suspect it's not updated to reflect the Sept 2019 CPI-U CPI-W numbers (upon which COLAs depend*, and the value I used for "today's" dollars).

If we just wait for SS to update their calculators (anypia, online calculators, etc) we can troubleshoot and cross reference values. On the other hand, it's nice to have some data in advance and perhaps we'll have the "right" answer prior to relying on SS to check our work. It's certainly possible that the SS calculators can have errors. In fact, I know for certain that SS calculators can have errors. :wink:

Neurosphere

* SS COLA's do not depend solely on the Sept CPI-U values, but without the Sept CPI-U the COLAs cannot be calculated. ;)
Last edited by neurosphere on Sat Nov 02, 2019 6:52 pm, edited 1 time in total.

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sat Nov 02, 2019 5:20 pm

FiveK wrote:
Sat Nov 02, 2019 5:08 pm
All the calculations in the toolbox spreadsheet are exposed, but in the other two tools (I think) some calculations are hidden. From what I could tell, the toolbox calculations follow the SSA formulas, but deciphering Excel formulas, even when visible, is not always easy. ;)
I sometimes can't decipher my own formulas. Which is why every year I get to re-learn and re-inforce my knowledge of various SS calculations...as in, hmmm..what the heck was I trying to do with THIS formula?!?. :mrgreen:
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Nov 02, 2019 5:27 pm

My dream would be for a simple spreadsheet similar to the one I've built for Canadians here. It's completely open (no passwords) and contains no macros. It's built using the open-source LibreOffice Calc software, but also available online (Google Sheets) and in Microsoft Office Excel format. I just don't have time to learn about Social Security calculations; it's challenging enough to keep up to date on CPP and QPP calculations which differ from each other in subtle manners.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sat Nov 02, 2019 5:51 pm

longinvest wrote:
Sat Nov 02, 2019 5:27 pm
My dream would be for a simple spreadsheet similar to the one I've built for Canadians...
Agreed! A simple calculator would be great. But doesn't the toolbox version or any of the SS online calculators already fit that purpose? Maybe there are some bogleheads with much better spreadsheet skills than I who have an interest in this?

My tool's purpose was 1) to provide a graphical output in order to illustrate things such as bend points, the effect of additional future earnings, etc and 2) to allow for various sets of assumptions of future wage and price inflation. All of the current SS calculators assume NO wage and NO price inflation. In general, and if the past projects into the future, this underestimates SS benefits (because wage inflation has tended to be greater than price inflation). For those nearing 60, this doesn't really matter much. For those in their 30s, it could possibly make a big difference. Compare option "2" or "3" to option "1" in my calculator to see the impact for a person age 30 with significant past/future earnings for example. :shock:

I don't know of any other calculator which has that feature (apart from anypia). In other words, it's a spreadsheet based substitute for many (but certainly not all!) features of anypia (which does not work in on a Mac OS, and is anything but user friendly). And I'm not sure how one could get the graphs and charts/tables to be compatible across various platforms (sheets, open office, libre, Mac, excel, etc).

I've always thought it would be awesome to code it into a browser/java/whatever such as the wonderful work I was so glad to contribute to at socialsecurity.tools. But I do not have the coding expertise. I DO have a version of my spreadsheet embedded into a browser which I had hoped would make this really easy to use (no downloads, no OS or software requirements, etc). But unfortunately, microsoft recently removed the ability for cut/paste into the embedded spreadsheet, and now it's cumbersome to enter earnings year by year. Although, no more cumbersome than this SS online calculator.

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Nov 02, 2019 6:10 pm

neurosphere wrote:
Sat Nov 02, 2019 5:51 pm
Although, no more cumbersome than this SS online calculator.
OK, I now figured out how to get it make a projection at age 70. I had to tell it that the retiree would stop working at age 70 and 0 months, but provide a $0 salary for 2020 and later.

As it's an ssa.org calculator, I'll trust its $1,992 projection. But, this means that I'll have to fix the hypothetical retiree's salary history to get a $2,000 projection in 2019 dollars, while also accumulating a $1,000,000 portfolio using historical market returns.
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Nov 02, 2019 6:22 pm

neurosphere wrote:
Sat Nov 02, 2019 5:16 pm
For what it's worth, one online social security calculator (https://www.ssa.gov/planners/retire/AnypiaApplet.html) shows a benefit of $1992 for one born Jan 2 or later during 1954. The page says "*The most recent calculator update was in February, 2019." If $1992 is the same value that toolbox gets, I suspect it's not updated to reflect the Sept 2019 CPI-U numbers (upon which COLAs depend*, and the value I used for "today's" dollars).
Yes, based on that it appears the AnypiaApplet has not been updated because it matches the older version of the toolbox.

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Nov 02, 2019 6:31 pm

neurosphere wrote:
Sat Nov 02, 2019 5:51 pm
All of the current SS calculators assume NO wage and NO price inflation.
...
I don't know of any other calculator which has that feature (apart from anypia).
That's not completely true. Yours does have more options, but the toolbox also allows "Alternative II (i.e., intermediate) wage increase estimates" and shows a chart with a single curve having the two bend points and a diamond indicating where on that curve the case calculation falls.

All the tools have their pros and cons, which I why I'll often suggest all three (that I mentioned earlier) when people are looking for estimates.

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sat Nov 02, 2019 7:06 pm

FiveK wrote:
Sat Nov 02, 2019 6:31 pm
neurosphere wrote:
Sat Nov 02, 2019 5:51 pm
All of the current SS calculators assume NO wage and NO price inflation.
...
I don't know of any other calculator which has that feature (apart from anypia).
That's not completely true. Yours does have more options, but the toolbox also allows "Alternative II (i.e., intermediate) wage increase estimates" and shows a chart with a single curve having the two bend points and a diamond indicating where on that curve the case calculation falls.

All the tools have their pros and cons, which I why I'll often suggest all three (that I mentioned earlier) when people are looking for estimates.
Thanks so much for pointing out the "trustee II" option in the toolbox! I had forgotten or not looked that closely recently. I think my mind, I was referring to the "official" SS calculators when describing the lack of inflation projection options. But you are correct, I was not considering or thinking of non-SS tools like the toolbox spreadsheet. Thanks!

Also there is indeed a graph! :beer I think perhaps I didn't scroll down far enough. Maybe my efforts are redundant! :shock: To be fair, my spreadsheet was initially embedded in a browser for easy cut-paste of whatever earnings assumptions one wanted, on whatever machine one had. Microsoft/one-drive ruined that for me. Maybe I really do need to migrate to a google sheets (or similar open source) version.

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Sun Nov 03, 2019 1:14 pm

FiveK wrote:
Sat Nov 02, 2019 6:31 pm
That's not completely true. Yours does have more options, but the toolbox also allows "Alternative II (i.e., intermediate) wage increase estimates" and shows a chart with a single curve having the two bend points and a diamond indicating where on that curve the case calculation falls.
The purpose of that chart and my chart are different, of course, although the overlap in certain ways.

Mine allows one to put in any hypothetical past and future earnings, and display the effect of changing one's retirement date on the benefit. For example, if one assumes their future income will exceed the maximum taxable earnings, one can ask, "how quickly will my benefit change if I work for another 5 years" or, if I retire at age X, what will my benefit be compared to age Y?

The chart below is a person born in 1980, with maximal taxable earnings starting at age 25, who assumes that if she continues to work she'll remain above the taxable maximum until she decides to no longer work. It uses the Trustees intermediate assumptions.

The chart illustrates some neat things. The first "bend" on the graph at about age 27 is when she'll reach the first bend point. The next bend at age 42ish is the second bend point. The elbow at age 60 is when she has 35 years of earnings. The upswing after that represents what additional earnings years after age 60. Those who are over 70 who keep working get nice increases in benefits, with these assumptions.

Image

Here is the same earnings history, but using the default "zero" assumptions for wage and earnings growth, as per the online SS calculators:

Image

I mostly post those images because I think they're cool, and show how inflation assumptions can really impact the estimated benefit for a younger person, assuming no other changes (e.g. legislative) to the formulas. One can do this kind of modeling with other tools, but IMNSHO I think the graphs make it easier to see a lot of information all at once.

[Edited to add, I also wanted to test using google photos to share the images. It worked! Thanks to Lady Geek (and others) for explaining how to do this so nicely in the wiki.]

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sun Nov 03, 2019 3:13 pm

neurosphere wrote:
Sun Nov 03, 2019 1:14 pm
...I think the graphs make it easier to see a lot of information all at once.
Agreed!

One might quibble about exactly how many words a picture is worth, but charts such as the ones you show here and ones that display the marginal tax rate for optional income or contributions seem worth quite a few.

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Dec 21, 2019 4:31 pm

longinvest wrote:
Sat Nov 02, 2019 6:10 pm
neurosphere wrote:
Sat Nov 02, 2019 5:51 pm
Although, no more cumbersome than this SS online calculator.
OK, I now figured out how to get it make a projection at age 70. I had to tell it that the retiree would stop working at age 70 and 0 months, but provide a $0 salary for 2020 and later.

As it's an ssa.org calculator, I'll trust its $1,992 projection. But, this means that I'll have to fix the hypothetical retiree's salary history to get a $2,000 projection in 2019 dollars, while also accumulating a $1,000,000 portfolio using historical market returns.
neurosphere wrote:
FiveK wrote:
Neurosphere and FiveK, I've fixed the historical salary record of the hypothetical retiree to get a $2,000 pension in 2019 dollars using the SSA's calculator, while also accumulating a $1,000,000 portfolio.

The process was quite painful. Having to enter data by hand, year by year, on the SSA's web page isn't fun. :annoyed

FiveK, I would be nice if you could double-check using the personal finance toolbox that the new earnings record effectively results into a $2,000 delayed pension in 2019 dollars, and if possible, provide me with an estimate using 2020 calculation factors. I'll need the updated pension amount for calculating the next portfolio withdrawal in early January 2020.

Neurosphere, were you able to identify the source of the difference in results between your estimator spreadsheet and the SSA's calculator?
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Dec 21, 2019 5:07 pm

longinvest wrote:
Sat Dec 21, 2019 4:31 pm
Neurosphere and FiveK, I've fixed the historical salary record of the hypothetical retiree to get a $2,000 pension in 2019 dollars using the SSA's calculator, while also accumulating a $1,000,000 portfolio.

The process was quite painful. Having to enter data by hand, year by year, on the SSA's web page isn't fun. :annoyed

FiveK, I would be nice if you could double-check using the personal finance toolbox that the new earnings record effectively results into a $2,000 delayed pension in 2019 dollars, and if possible, provide me with an estimate using 2020 calculation factors. I'll need the updated pension amount for calculating the next portfolio withdrawal in early January 2020.
That earnings record would have led to a $2000/mo prediction for 2024 when calculated earlier this year. With the recently announced 1.6% COLA, the new prediction is $2032/mo. Additional COLAs in the next few years will also increase the 2024 number, but the "zero assumed inflation" number calculated in July 2019 for 2024 would indeed have been $2000/mo.

longinvest
Posts: 4083
Joined: Sat Aug 11, 2012 8:44 am

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by longinvest » Sat Dec 21, 2019 5:15 pm

FiveK wrote:
Sat Dec 21, 2019 5:07 pm
That earnings record would have led to a $2000/mo prediction for 2024 when calculated earlier this year. With the recently announced 1.6% COLA, the new prediction is $2032/mo.
Thanks FiveK. That's what I was looking for. So, I'll use $2,032 for the early January withdrawal calculations of the forward test. We'll be able to confirm the number once the SSA updates its AnyPIA applet, probably in February 2020 (the current version dates back to February 2019).
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Sat Dec 21, 2019 5:22 pm

longinvest wrote:
Sat Dec 21, 2019 5:15 pm
FiveK wrote:
Sat Dec 21, 2019 5:07 pm
That earnings record would have led to a $2000/mo prediction for 2024 when calculated earlier this year. With the recently announced 1.6% COLA, the new prediction is $2032/mo.
Thanks FiveK. That's what I was looking for. So, I'll use $2,032 for the early January withdrawal calculations of the forward test. We'll be able to confirm the number once the SSA updates its AnyPIA applet, probably in February 2020 (the current version dates back to February 2019).
If the SSA applet returns anything other than $2000 * 1.016 = $2032 it will be surprising. :)

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Fri Jan 03, 2020 1:14 pm

FiveK wrote:
Sat Dec 21, 2019 5:22 pm
longinvest wrote:
Sat Dec 21, 2019 5:15 pm
FiveK wrote:
Sat Dec 21, 2019 5:07 pm
That earnings record would have led to a $2000/mo prediction for 2024 when calculated earlier this year. With the recently announced 1.6% COLA, the new prediction is $2032/mo.
Thanks FiveK. That's what I was looking for. So, I'll use $2,032 for the early January withdrawal calculations of the forward test. We'll be able to confirm the number once the SSA updates its AnyPIA applet, probably in February 2020 (the current version dates back to February 2019).
If the SSA applet returns anything other than $2000 * 1.016 = $2032 it will be surprising. :)
The new anypia is out (version 2020.1) but it seems to have an error, in that the CPI and wage index (and thus the wage base) is not actually updated with the newest numbers. Seems strange to release an update which is not really an update? I was hoping to cross reference and troubleshoot my spreadsheet against anypia as the "gold standard", but I can't yet do that. :(
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Fri Jan 03, 2020 5:04 pm

neurosphere wrote:
Fri Jan 03, 2020 1:14 pm
The new anypia is out (version 2020.1) but it seems to have an error, in that the CPI and wage index (and thus the wage base) is not actually updated with the newest numbers. Seems strange to release an update which is not really an update? I was hoping to cross reference and troubleshoot my spreadsheet against anypia as the "gold standard", but I can't yet do that. :(
Using the Windows self-extracting zip file, under Properties>Historical Amounts>Review, the "Amounts for 2020 computations" show as

Code: Select all

Benefit increase for December 2019 (percent)   1.6
                       Average wage for 2018   52,145.80
                          Wage base for 2020   137,700
which seems correct.

Is there something different in the Windows Installer file version?

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Fri Jan 03, 2020 5:22 pm

FiveK wrote:
Fri Jan 03, 2020 5:04 pm
neurosphere wrote:
Fri Jan 03, 2020 1:14 pm
The new anypia is out (version 2020.1) but it seems to have an error, in that the CPI and wage index (and thus the wage base) is not actually updated with the newest numbers. Seems strange to release an update which is not really an update? I was hoping to cross reference and troubleshoot my spreadsheet against anypia as the "gold standard", but I can't yet do that. :(
Using the Windows self-extracting zip file, under Properties>Historical Amounts>Review, the "Amounts for 2020 computations" show as

Code: Select all

Benefit increase for December 2019 (percent)   1.6
                       Average wage for 2018   52,145.80
                          Wage base for 2020   137,700
which seems correct.

Is there something different in the Windows Installer file version?
Interesting. I can't copy/paste but I see "0.0" for the "Benefit increase for December 2019 (percent)" and 50,321.89 for the "average wage for 2019". In the "about" menu it clearly says version "2020.1". I'm going to uninstall and reinstall. Perhaps they got my email a couple of days ago and there is an updated download?

Edit: I used the self extracting zip to a new folder and got the same results. Weird.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Fri Jan 03, 2020 5:47 pm

FiveK wrote:
Fri Jan 03, 2020 5:04 pm
Is there something different in the Windows Installer file version?
I've tried both the windows installer and the raw zip files. Both of those confirm "2020.1" as the version, but do not give me the values you are seeing for "2020 computations". Frustrating.

I'm on a PC. Are you on a Mac? I assume not. Anypia does not work on Mac Os versions prior to Lion (2011).
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
FiveK
Posts: 7998
Joined: Sun Mar 16, 2014 2:43 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by FiveK » Fri Jan 03, 2020 7:41 pm

neurosphere wrote:
Fri Jan 03, 2020 5:47 pm
FiveK wrote:
Fri Jan 03, 2020 5:04 pm
Is there something different in the Windows Installer file version?
I've tried both the windows installer and the raw zip files. Both of those confirm "2020.1" as the version, but do not give me the values you are seeing for "2020 computations". Frustrating.

I'm on a PC. Are you on a Mac? I assume not. Anypia does not work on Mac Os versions prior to Lion (2011).
That's bizarre. Plain ol' Windows 10 PC here. Wish I could help but clutching at the straw of a difference between the installer and zip files was my best shot.

"Help>About Calculator..." shows 2020.1 so...?

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Fri Jan 03, 2020 7:54 pm

FiveK wrote:
Fri Jan 03, 2020 7:41 pm
That's bizarre. Plain ol' Windows 10 PC here. Wish I could help but clutching at the straw of a difference between the installer and zip files was my best shot.

"Help>About Calculator..." shows 2020.1 so...?
Thanks. Windows 10 here too. Tried each of the installers. "about" definitely shows 2020.1. However the installer msi doesn't create a desktop icon like usual. It has to be in installation issue and/or conflict with a previous version. i'll keep working on it!
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
One Ping
Posts: 796
Joined: Thu Sep 24, 2015 4:53 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by One Ping » Mon Jan 13, 2020 5:59 pm

neurosphere wrote:
Fri Jan 03, 2020 7:54 pm
FiveK wrote:
Fri Jan 03, 2020 7:41 pm
That's bizarre. Plain ol' Windows 10 PC here. Wish I could help but clutching at the straw of a difference between the installer and zip files was my best shot.

"Help>About Calculator..." shows 2020.1 so...?
Thanks. Windows 10 here too. Tried each of the installers. "about" definitely shows 2020.1. However the installer msi doesn't create a desktop icon like usual. It has to be in installation issue and/or conflict with a previous version. i'll keep working on it!
Any luck, neuro?
"Re-verify our range to target ... one ping only."

User avatar
Topic Author
neurosphere
Posts: 3379
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by neurosphere » Mon Jan 13, 2020 6:42 pm

One Ping wrote:
Mon Jan 13, 2020 5:59 pm
Any luck, neuro?
Actually yes, I finally got it to work. Registry had mix of old and new versions suggesting previous versions were not uninstalled correctly? Not sure what I did not but at least the data within anypia is now the newest data.

My spreadsheet matches anypia for the default assumption of zero wage inflation and zero price inflation. Haven't had a chance to test other inflation assumptions.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
One Ping
Posts: 796
Joined: Thu Sep 24, 2015 4:53 pm

Re: Downloadable Social Security Benefit Estimator (2019 Update)

Post by One Ping » Mon Jan 13, 2020 6:48 pm

neurosphere wrote:
Mon Jan 13, 2020 6:42 pm
One Ping wrote:
Mon Jan 13, 2020 5:59 pm
Any luck, neuro?
Actually yes, I finally got it to work. Registry had mix of old and new versions suggesting previous versions were not uninstalled correctly? Not sure what I did not but at least the data within anypia is now the newest data.

My spreadsheet matches anypia for the default assumption of zero wage inflation and zero price inflation. Haven't had a chance to test other inflation assumptions.
Excellent!! That's kind of a subtle and scary problem to have. :shock:

It's not that large so I usually don't uninstall my old versions, I just install the new version from the zip files in a new folder and set up a new shortcut.
"Re-verify our range to target ... one ping only."

Post Reply