Downloadable Social Security Benefit Estimator (repost)

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

Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Thu Nov 09, 2017 12:32 pm

[Edit April 24 2018: New link to version 3, which now will bold the top 35 earning years]
[Edit May 4 2018: fixed COLA calculations for those age 62+]

Hi all, I've created an excel spreadsheet which allows one to enter any set of earnings and determine a benefit at any age, and displays the data in a graph. This allows for easy identification of such things as the effects of early or delayed benefits, bend points, effects of additional earnings after benefit date, etc. Also, this calculator does something which I have not seen in any other calculator, which is to give the output in today's dollars for any given set of future assumptions for the Average Wage Index and CPI-W changes.

I believe this calculator is the easiest/quickest way to model most scenarios, as one can cut/paste data or easily modify any parameter quickly, and see just about any output all at once. E.g. no need to recalculate for various retirement ages or claiming dates. It's all in the graph. I think this is as user friendly as any calculator currently out there for this level of versitality (but please let me know if you disagree!).

The default for this calculator is for zero wage growth and zero CPI-W changes (similar to the online calculators at ssa.gov and the data in the mailed estimates and in the personal mySSA accounts).

This calculator is "version 4": https://1drv.ms/x/s!Atj-cegEXidXrGZgWf1RyCoGPrn0

Clicking on the link will open a read-only spreadsheet, but there is a download button to the upper right.
Sheets are locked such that you can only make changes in the cells which require data. In other words, you can't mess it up.

Instructions are on the first tab. In the next post, I will post a screenshot as well as a copy of the instructions (they're very easy!).

Please let me know if there are any questions, corrections, or feature requests. Currently the spreadsheet only calculates one's old-age benefit for benefit dates which match your birth month. I do not intend to change the calculator to calculate the benefit for non-integer claiming ages. Learn to use AnyPia for that! :wink:
Last edited by neurosphere on Fri May 04, 2018 8:38 am, edited 5 times in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

BogleMelon
Posts: 1257
Joined: Mon Feb 01, 2016 11:49 am

Re: Downloadable Social Security Benefit Estimator (repost)

Post by BogleMelon » Thu Nov 09, 2017 12:38 pm

Thank you for sharing! I was actually looking for something similar since I haven't worked enough years to get estimated statement from SS with future earnings. But I have a question, should I enter the earnings after or before employer's paid amounts?
"One of the funny things about stock market, every time one is buying another is selling, and both think they are astute" - William Feather

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Thu Nov 09, 2017 12:43 pm

Here is a screenshot of the spreadsheet (blurry due to compression/resizing), with sample data. Enter your birthdate in the upper left, and your earnings in the appropriate age/year (peach cells). If you wish, you can change the default assumptions for future CPI-W and National Average Wage Index changes.

This screenshot shows a worker born in 1965 with maximal earnings from age 26 to 67. Top line is the age 70 benefit, bottom line is the age 62 benefit, with other claiming ages inbetween.

The first "kink" at age 44 or so represents the second bend point (the first bend point occurs prior to age 30). The next kink represents when 35 years of earnings are reached. The small increase in benefits after age 60 illustrate the fact that earnings after age 60 are not indexed, so that if your earnings at age 60+ keep pace with wage growth, they will replace previous years' earnings.

Play with changes to the default wage and CPI assumptions. For example, what happens to your benefit if you put in numbers which are more in-line with the Social Security's "best guess" (i.e. the last two columns in the green table)?

Image

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Thu Nov 09, 2017 12:45 pm

Here are the instructions, for those who can't/won't download the spreadsheet. The instructions will allow you to better know what I'm trying to do or have done:

INPUTS

-- Boxes shaded in Peach can be edited.

--Enter a birthdate and actual or estimated earnings.

--You have the option of change the default assumptions for future price inflation (CPI-W) and wage inflation (National Average Wage Index).

-- The default assumptions of "zero" for each or these ecomic values is what the Social Security Administration uses in estimates they provide (e.g. their online calculators, and the estimate they mail and/or provide in your online account)

OUTPUTS

-- 1) AIME, PIA and Monthly benfit based on claiming age (e.g. the table).
Note that if there are no earnings after age 61, the AIME and PIA will be the same

-- 2) The graph, which displays the benefit based on the age you start collecting benefits. The top line is age 70, the bottom line is age 62.

-- For any given set of earnings/estimates you provide the graph will illustrate what your benefit will be if you stop earning at any age (e.g. "retirement", the x-axis). At that retirement age, your benefit will depend on the age you start to receive benefits (what I'm calling the benefit age), as represented by the set of age 62 to age 70 lines on the graph.

-- All outputs are always in Sept 2017 dollars.

NOTES

-- This spreadsheet uses the recently released 2016 Average Wage Index values, and the Sept 2017 CPI. I do not believe AnyPia nor any of the online calculators available at ssa.gov have been updated yet. Let me know if you think otherwise.

-- Keep in mind this spreadsheet is meant to provide ballpark estimates of social security benefits, as well as to allow you simulate the effects of price and wage inflation in order to convert benefits more "accurately" into "today's dollars" than the estimates provided by Social Security, which assume no wage inflation or price inflation (although technically, those estimates are thus still in "today's dollars" under those assumptions). The graphical output illustrates, in one chart, certain features such as bendpoints and the change in benefit based on claiming age and retirement date, as well as the effects of additional earnings after the benefit date.

-- The previous version of this calculator matched the output of version "AnyPia" 2017.2 (the most recent version) "to the penny" for MOST scenarios for mid-month birthdays where claiming dates were in a birthday month.

-- I believe the calculator is "exactly" accurate for those with an Jan 2nd birthday, and January claiming date (but I do not attest to this claim). In this case, I believe that all earnings from the year prior and any delayed retirement credits are credited to the benefit age/year, and credited to the current year (even if some increases due to recent earnings, for example, may be paid retroactively)

-- There may be various "edge" effects which are not taken into account. Jan 1 birthdays for example, perhaps other first of month birthdays, etc.

-- A special thanks to "sscritic" who spent many hours with me in back and forth conversations when I believed there was an error in my spreadsheet because I couldn't match AnyPia's outputs. It turned out that my spreadsheet was correct and the error was with AnyPia. But his willingness to walk me through each calculation in a meticulous and pedantic way (and I mean that in a positve way!) was what helped lead to my "aha" moment where I realized the error was with AnyPia and not with me. :)

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Thu Nov 09, 2017 12:51 pm

BogleMelon wrote:
Thu Nov 09, 2017 12:38 pm
Thank you for sharing! I was actually looking for something similar since I haven't worked enough years to get estimated statement from SS with future earnings. But I have a question, should I enter the earnings after or before employer's paid amounts?
You enter the earnings which SS uses to calculate your benefit. In most cases, that would be what's in Box 3 on your W2 if you are a regular employee. In almost all cases, it's your salary up through the social security maximum. But you can enter any earnings, and the spreadsheet knows only to account for the earnings which are below the "contribution and benefit base",referred to by some as the earnings limit, the social security wage base, the social security maximum, etc.

The fact that your employer pays SS taxes on your behalf does not alter the way your benefits are calculated.

Note that there are certain jobs which are exempt from SS/FICA taxes, and those job's earnings will not count towards SS earnings.

BogleMelon
Posts: 1257
Joined: Mon Feb 01, 2016 11:49 am

Re: Downloadable Social Security Benefit Estimator (repost)

Post by BogleMelon » Thu Nov 09, 2017 12:55 pm

neurosphere wrote:
Thu Nov 09, 2017 12:51 pm
BogleMelon wrote:
Thu Nov 09, 2017 12:38 pm
Thank you for sharing! I was actually looking for something similar since I haven't worked enough years to get estimated statement from SS with future earnings. But I have a question, should I enter the earnings after or before employer's paid amounts?
You enter the earnings which SS uses to calculate your benefit. In most cases, that would be what's in Box 3 on your W2 if you are a regular employee. In almost all cases, it's your salary up through the social security maximum. But you can enter any earnings, and the spreadsheet knows only to account for the earnings which are below the "contribution and benefit base",referred to by some as the earnings limit, the social security wage base, the social security maximum, etc.

The fact that your employer pays SS taxes on your behalf does not alter the way your benefits are calculated.

Note that there are certain jobs which are exempt from SS/FICA taxes, and those job's earnings will not count towards SS earnings.
Awesome! Thanks again for all of that! I always struggled with these points thing, and that calculator is super useful I guess
"One of the funny things about stock market, every time one is buying another is selling, and both think they are astute" - William Feather

Ron Scott
Posts: 557
Joined: Tue Apr 05, 2016 5:38 am

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Ron Scott » Thu Nov 09, 2017 5:07 pm

How does this compare with Quicnen SSO?

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Thu Nov 09, 2017 5:25 pm

Ron Scott wrote:
Thu Nov 09, 2017 5:07 pm
How does this compare with Quicnen SSO?
I've never used Quicken SSO (https://www.quickensso.com), but looking at the website here are some differences:

-- Mine is FREE :) Quicken's seems to be $50/year to use
-- Mine does not show benefits for your spouse on the same page. You'll have to re-enter spousal data into the earnings table to see the results. One person at a time, so to speak.
-- Mine does not calculate spousal benefits, nor offer/suggest any claiming strategies.
-- Mine allows for various economic assumptions (I don't believe quicken's does) and keeps the output in inflation-adjusted dollars (today's dollar equivalent)
-- Mine allows you to change your future earnings estimates (or any of the inputs) and see the changes immediately as you enter the data. I don't know what Quicken does in this regard.

Essentially, these are two very different things. Mine is an attempt to replicate AnyPia, for a limited set of inputs (i.e. old-age benefits only, integer claiming ages, etc), but then also allow for inflation adjusting non-default assumptions. The quicken tool seems primarily to optimize claiming strategies with a spouse (the "optimizer" part of the title). It SEEMS like it requires you to already know your benefit at FRA? But it's not clear from the promo materials.

CoachDave
Posts: 1
Joined: Wed Mar 21, 2018 3:37 am

Re: Downloadable Social Security Benefit Estimator (repost)

Post by CoachDave » Wed Mar 21, 2018 3:40 am

neurosphere,

I signed up for an account on Bogleheads today to say thank you for this spreadsheet. It is exactly what I needed. I tested it several times using the SS site and it is spot on. Well done!

:sharebeer

FrankLUSMC
Posts: 79
Joined: Tue Nov 28, 2017 1:43 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by FrankLUSMC » Wed Mar 21, 2018 7:58 am

It doesn't work with Libre, entered all income fields, but the benefits cells are stuck at low values:

AIME stuck at 262 across all cells
PIA at 234 across all cells.
Monthly benefit does increase, but it is at low values, $170 at 62 up to $300 at 70.

My SSA account says $1778 at 62 and $2439 at 70

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Wed Mar 21, 2018 3:40 pm

CoachDave wrote:
Wed Mar 21, 2018 3:40 am
neurosphere,

I signed up for an account on Bogleheads today to say thank you for this spreadsheet. It is exactly what I needed. I tested it several times using the SS site and it is spot on. Well done!

:sharebeer
Thanks CoachDave! And thanks for the feedback that it matches the SS site. Incidentally, I will never refrain from bragging that at one point, for some inputs, my spreadsheet and "AnyPia" from SS did not agree. Of course I assumed it was my error, and resulted in a lot of headbanging. With help from a BH named sscritic, I found that the error was with AnyPia and they fixed the calculations in a subsequent update. :D
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Wed Mar 21, 2018 3:42 pm

FrankLUSMC wrote:
Wed Mar 21, 2018 7:58 am
It doesn't work with Libre, entered all income fields, but the benefits cells are stuck at low values:
Hmm, I've never worked with Libre, so I don't have insights into what might be going on, other than the obvious, which is that one or more of my formulas is not compatible with Libre. That's too bad. sorry. :(

Kelmscott
Posts: 36
Joined: Fri Aug 17, 2012 8:02 am

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Kelmscott » Wed Mar 21, 2018 5:12 pm

Thank you so much, Neurosphere (and SSCritic, too) for putting the time and effort into creating this excellent spreadsheet!

It's a million times easier to work with than AnyPIA and it's put my mind at ease with my SS calculations for the future.

I really appreciate it. :beer

RetireBy60
Posts: 2
Joined: Sun Apr 22, 2018 6:33 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by RetireBy60 » Sun Apr 22, 2018 6:41 pm

Excellent spreadsheet! Saved me so much time. One thing I would like to see is to somehow indicate which 35 years of income were used to calculate SS benefits. In most cases, I can see that would be the last 35 years (after turning 21, I believe). But, in other cases, there may be a few gaps of income due to sabbaticals, travel, illness, etc. Maybe just a simple highlight of the peach income boxes that were used -- maybe just bolding the income amounts. Just something simple to identify which income values were used. Thanks again!

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Tue Apr 24, 2018 4:42 am

[deleted, duplicate]

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Tue Apr 24, 2018 4:49 am

RetireBy60 wrote:
Sun Apr 22, 2018 6:41 pm
Excellent spreadsheet! Saved me so much time. One thing I would like to see is to somehow indicate which 35 years of income were used to calculate SS benefits. In most cases, I can see that would be the last 35 years (after turning 21, I believe). But, in other cases, there may be a few gaps of income due to sabbaticals, travel, illness, etc. Maybe just a simple highlight of the peach income boxes that were used -- maybe just bolding the income amounts. Just something simple to identify which income values were used. Thanks again!
Thanks RetireBy60! Glad you found the spreadsheet helpful. I have no idea how I would go about highlighting the top 35 years, but...challenge accepted! I'll work on it and see if I can learn something new. :)

EDIT: There was an easy built-in feature I could use. The spreadsheet will now bold the top 35 earnings years. All ties for the top 35 will also be bolded. Thus, all zero-earnings years will be bold as long as there are any zeros whatever among the top 35. You can download the new version with the new link in the first post in this thread.

P.S. Welcome to the forum!
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
Rob54keep
Posts: 329
Joined: Mon Dec 01, 2008 10:06 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Rob54keep » Tue Apr 24, 2018 6:07 am

It does bold the top 35 earning years but when using the index inflation factor that SS provides (using the inflation factors from the year when you were eligible for SS at age 62) these bold numbers are not the top 35 years. For example, I have a wage in 1975 that has an index inflation factor of 5.39. That inflated wage is greater than the 2017 earning but yet the spreadsheet highlights the 2017 as a top earning year (of the top 35 years used int he calculation)

Also playing around with the CPI-W and AWI does nothing to the results or graph. Am I doing something wrong here?

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Tue Apr 24, 2018 6:13 am

Rob54keep wrote:
Tue Apr 24, 2018 6:07 am
It does bold the top 35 earning years but when using the index inflation factor that SS provides (using the inflation factors from the year when you were eligible for SS at age 62) these bold numbers are not the top 35 years. For example, I have a wage in 1975 that has an index inflation factor of 5.39. That inflated wage is greater than the 2017 earning but yet the spreadsheet highlights the 2017 as a top earning year (of the top 35 years used int he calculation)

Also playing around with the CPI-W and AWI does nothing to the results or graph. Am I doing something wrong here?
Jeez, duh. What a silly mistake to bold just the non-indexed earnings!! Thanks for pointing that out. I was thrown off by the ease of the pre-built conditional formatting option. I will fix.

As for CPI/AWI, I get changes to the values in the table above the graph, as well as the lines on the graph, whenever I change the entries for either CPI or AWI. Changing a value in the peach boxes should then change all values in the green shades below it. Is that happening for you?

User avatar
Rob54keep
Posts: 329
Joined: Mon Dec 01, 2008 10:06 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Rob54keep » Tue Apr 24, 2018 6:52 am

When I change a value in the Peach Box (CPI-W or AWI) it changes the green values below the peach box but it does nothing to the benefits box (above the graph) nor to the graph itself. Is anything "Protected" in the spreadsheet that may prevent the changes from occurring?

abner kravitz
Posts: 296
Joined: Tue May 05, 2015 7:42 am
Location: Beaufort County, SC

Re: Downloadable Social Security Benefit Estimator (repost)

Post by abner kravitz » Tue Apr 24, 2018 6:56 am

I put in my earnings and was within a couple of dollars of the official social security estimate. This is particularly helpful for me, since ANYPIA does not work with newer MACs. Thanks!

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Tue Apr 24, 2018 7:08 am

Rob54keep wrote:
Tue Apr 24, 2018 6:52 am
When I change a value in the Peach Box (CPI-W or AWI) it changes the green values below the peach box but it does nothing to the benefits box (above the graph) nor to the graph itself. Is anything "Protected" in the spreadsheet that may prevent the changes from occurring?
I can't reproduce your problem. When I enter some dummy values for a few years earnings, changing the CPI or AWI changes the benefit in the chart and the graph. I have to think for a sec if there are some range of years and/or values for which changing CPI or AWI does not actually result in any changes, but I don't think so. Maybe if all the earnings you enter are in the future? Can you share your inputs, perhaps in a private message. Or give "fake" data which otherwise still causes the error?

And verify you are using the newest version: https://1drv.ms/x/s!Atj-cegEXidXrGGIdnYkwUKOglMD

BTW, I added a column for indexed earnings, and the top 35 INDEXED years now appear in bold italics.

User avatar
Rob54keep
Posts: 329
Joined: Mon Dec 01, 2008 10:06 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Rob54keep » Tue Apr 24, 2018 7:53 am

Ah,, my bad. The manual changes in the CPI-W and AWI affect future earnings only. Seeing all my earnings are in the past (64 and retired), it does not affect my PIA. Also the BOLD indexed earnings now works AOK. Thanks.

Also where does the SS-CPI-W come from and how does that relate to COLA? Because the COLA for 2017 = 0.30%, 2018 =2%. Can I make assumptions for 2018 and beyond?

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Tue Apr 24, 2018 8:00 am

abner kravitz wrote:
Tue Apr 24, 2018 6:56 am
I put in my earnings and was within a couple of dollars of the official social security estimate. This is particularly helpful for me, since ANYPIA does not work with newer MACs. Thanks!
It should be exact (except that I do not show decimals in my spreadsheet, e.g. for the PIA), not off by a couple of dollars. I found a small error which I fixed. Feel free to re-download and re-calculate. :D

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Tue Apr 24, 2018 8:16 am

Rob54keep wrote:
Tue Apr 24, 2018 7:53 am
Ah,, my bad. The manual changes in the CPI-W and AWI affect future earnings only. Seeing all my earnings are in the past (64 and retired), it does not affect my PIA. Also the BOLD indexed earnings now works AOK.
Exactly. Your benefit will increase based on CPI. But I'm presenting all the data in Sept 2017 dollars. So when the cola is applied to your future benefit, it will not have changed in terms of 2017 dollars. And as you note, AWI changes do not affect your earnings after age 60.

If you decide to go back to work, Rob, you can now easily see exactly how much your benefit might increase. :wink:
Also where does the SS-CPI-W come from and how does that relate to COLA? Because the COLA for 2017 = 0.30%, 2018 =2%. Can I make assumptions for 2018 and beyond?
The short answer is that COLA's are based on the changes in the third quarter CPI-W relative to the previous year. The real answer can be found here:
https://www.ssa.gov/oact/cola/latestCOLA.html

And regarding assumptions for future COLA, you are free to make all the assumptions you want! :D

You can see what the Social Security Trustees most recently used as their "best guess" predictions right there in the spreadsheet. It's the column marked "2017 Inter est COLA" in green.

User avatar
Rob54keep
Posts: 329
Joined: Mon Dec 01, 2008 10:06 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Rob54keep » Tue Apr 24, 2018 8:39 am

Yes, nice. Thanks

Bmac
Posts: 279
Joined: Sun Mar 03, 2013 8:58 am
Location: Seattle

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Bmac » Tue Apr 24, 2018 8:47 am

Ok, I must be an idiot. What am I doing wrong? I used your link and then downloaded the benefit estimator, but I am unable to enter any data in the pink sections. I am on an iMac and using Safari. Please advise.
Thanks,
Bmac

dowse
Posts: 310
Joined: Tue Sep 10, 2013 3:10 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by dowse » Tue Apr 24, 2018 8:53 am

Thanks for providing this. It's quite useful in my case, because I recently took advantage of the restricted application strategy and am collecting a spousal benefit with the intention of switching to my own benefit at age 70. Because I am already collecting, SSA will no longer produce a benefits estimate for me, but they still make my earnings record available. With that and your spreadsheet, I am able to see my estimated benefits once again, which could be quite useful if circumstances change for me, and I wish to assess alternative strategies.

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Tue Apr 24, 2018 9:05 am

Bmac wrote:
Tue Apr 24, 2018 8:47 am
Ok, I must be an idiot. What am I doing wrong? I used your link and then downloaded the benefit estimator, but I am unable to enter any data in the pink sections. I am on an iMac and using Safari. Please advise.
Thanks,
Bmac
I don't use mac so I'm not sure about that. I doubt the browser type would matter. Using excel to open the file on windows, I get a warning/button at the top which I must click to enable any data entry. Does that appear in your case?

Are you using excel to open? Maybe another mac user can help. Sorry. :(

Bmac
Posts: 279
Joined: Sun Mar 03, 2013 8:58 am
Location: Seattle

Re: Downloadable Social Security Benefit Estimator (repost)

Post by Bmac » Tue Apr 24, 2018 9:08 am

neurosphere wrote:
Tue Apr 24, 2018 9:05 am
Bmac wrote:
Tue Apr 24, 2018 8:47 am
Ok, I must be an idiot. What am I doing wrong? I used your link and then downloaded the benefit estimator, but I am unable to enter any data in the pink sections. I am on an iMac and using Safari. Please advise.
Thanks,
Bmac
I don't use mac so I'm not sure about that. I doubt the browser type would matter. Using excel to open the file on windows, I get a warning/button at the top which I must click to enable any data entry. Does that appear in your case?

Are you using excel to open? Maybe another mac user can help. Sorry. :(
The problem is that it is being opened in Preview. I don't have Excel on my iMac (don't really use spreadsheets). Guess I need to download an Excel app.

abner kravitz
Posts: 296
Joined: Tue May 05, 2015 7:42 am
Location: Beaufort County, SC

Re: Downloadable Social Security Benefit Estimator (repost)

Post by abner kravitz » Tue Apr 24, 2018 1:22 pm

neurosphere wrote:
Tue Apr 24, 2018 8:00 am
abner kravitz wrote:
Tue Apr 24, 2018 6:56 am
I put in my earnings and was within a couple of dollars of the official social security estimate. This is particularly helpful for me, since ANYPIA does not work with newer MACs. Thanks!
It should be exact (except that I do not show decimals in my spreadsheet, e.g. for the PIA), not off by a couple of dollars. I found a small error which I fixed. Feel free to re-download and re-calculate. :D

Thanks, I probably made an input error somewhere along the way. Next rainy day maybe I'll try to find it, but I can live with a couple of bucks in the meantime. :happy

RetireBy60
Posts: 2
Joined: Sun Apr 22, 2018 6:33 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by RetireBy60 » Thu May 03, 2018 8:57 pm

neurosphere wrote:
Tue Apr 24, 2018 6:13 am
Rob54keep wrote:
Tue Apr 24, 2018 6:07 am
It does bold the top 35 earning years but when using the index inflation factor that SS provides (using the inflation factors from the year when you were eligible for SS at age 62) these bold numbers are not the top 35 years. For example, I have a wage in 1975 that has an index inflation factor of 5.39. That inflated wage is greater than the 2017 earning but yet the spreadsheet highlights the 2017 as a top earning year (of the top 35 years used int he calculation)

Also playing around with the CPI-W and AWI does nothing to the results or graph. Am I doing something wrong here?
Jeez, duh. What a silly mistake to bold just the non-indexed earnings!! Thanks for pointing that out. I was thrown off by the ease of the pre-built conditional formatting option. I will fix.

As for CPI/AWI, I get changes to the values in the table above the graph, as well as the lines on the graph, whenever I change the entries for either CPI or AWI. Changing a value in the peach boxes should then change all values in the green shades below it. Is that happening for you?
Thank you much for the update! Love the highlighted wages. Exactly what I was looking for.

Question: I read on ssa.gov (section 404.221. Computing Your Average Monthly Wage, (a) General) that only the wages after turning 21 are counted when calculating the social security benefit. Am I reading that wrong? The statement about "after 1950, or after reaching 21, if later" is confusing.

Thanks again!

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

Re: Downloadable Social Security Benefit Estimator (repost)

Post by FiveK » Fri May 04, 2018 12:48 am

Noticed the following in the Instructions tab:
The previous version of this calculator matched the output of version "AnyPia" 2017.2 "to the penny" for MOST scenarios for mid-month birthdays where claiming dates were in a birthday month. I have not yet checked to see whether anypia 2018.1 and this spreadsheet agree.

The 'SocialSecurity' tab in the personal finance toolbox spreadsheet was updated recently and notes "The estimates on the 'SocialSecurity' tab match the version 2018.1 anypia.exe numbers within $1/mo for all examples tested, e.g., https://www.ssa.gov/oact/ProgData/retirebenefit1.html."

I tried entering "Example B" from that ssa.gov page, and for age 66 got
AIME = 9144 (matches the ssa.gov number and the toolbox spreadsheet)
PIA (inflation adjusted) = 2742 (differs from the 2788 on ssa.gov and the 2789 on the toolbox spreadsheet).

Couldn't figure out - perhaps this is intentional? - how the 2742 was calculated to see where the three calculations diverge.

Nice work with the highlighted top 35!

SGM
Posts: 2590
Joined: Wed Mar 23, 2011 4:46 am

Re: Downloadable Social Security Benefit Estimator (repost)

Post by SGM » Fri May 04, 2018 8:20 am

I used your spreadsheet and compared it to the SS website on-line calculator. Your program came up with a result $21 a month higher for taking SS at age 70 than the SS on-line calculator. So I am going with your estimate. :wink: I had trouble downloading the SS website detailed calculator and can no longer use the Retirement estimator calculator because I filed and suspended.

I had a couple of goose-eggs and other low earnings during prime earning years because of taking off work to obtain several advanced degrees. Still my results are pretty close to the maximum SS payments. I am very happy that I took time off of work to retool for interesting and lucrative career changes.

Thanks to you and sscritic for your efforts.

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Fri May 04, 2018 8:28 am

FiveK wrote:
Fri May 04, 2018 12:48 am
I tried entering "Example B" from that ssa.gov page, and for age 66 got
AIME = 9144 (matches the ssa.gov number and the toolbox spreadsheet)
PIA (inflation adjusted) = 2742 (differs from the 2788 on ssa.gov and the 2789 on the toolbox spreadsheet).
My error. My spreadsheet was not yet updated to reflect COLAs which begin to be paid in January 2018 (for the Dec 2017 benefit). I'm working on an update and will re-upload.
FiveK wrote: The 'SocialSecurity' tab in the personal finance toolbox spreadsheet was updated recently and notes "The estimates on the 'SocialSecurity' tab match the version 2018.1 anypia.exe numbers within $1/mo for all examples tested, e.g., https://www.ssa.gov/oact/ProgData/retirebenefit1.html."
My update will agree with the personal finance toolbox for 2018 benefits, and neither will be correct. Anypia and online scenarios as shown by SS are the correct benefits. The reason is that, officially, the COLAs are applied to the PIA each year. And each year the PIA is rounded down prior to the next COLA calculation. For example (PIA1*COLA1) = (Unrounded PIA2). Then this number is rounded down to the dime and then the next COLA is applied. Instead, personal finance toolkit seems to just apply the cumulative COLA to the initial PIA. I do the something similar for those over aged 62+.

Just to reiterate, my calculator was designed to do three things which are not otherwise available online for free, to my knowledge:

1 - Graphical output to easily see the approximate annual benefit for a range of retirement dates and claiming dates, based on any existing and future earnings
2. Provide an inflation adjusted benefit in today's dollars, for any range of future wage and CPI changes
3. Illustrate how dramatically the inflation adjusted benefit can change based on CPI and AWI assumptions.

I'll have an update up soon!

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Fri May 04, 2018 8:38 am

If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Fri May 04, 2018 8:52 am

SGM wrote:
Fri May 04, 2018 8:20 am
I used your spreadsheet and compared it to the SS website on-line calculator. Your program came up with a result $21 a month higher for taking SS at age 70 than the SS on-line calculator. So I am going with your estimate. :wink: I had trouble downloading the SS website detailed calculator....
See if the updated version (just uploaded) matches the online calculator, if you wish. If it still doesn't match, I'd be curious to know your inputs. You could send me your birthdate and earnings history in a PM (but I understand why you would not want to do that) and I can put them into anypia for you and we can compare the answers from three different calculators. :D One reason I created the spreadsheet is that anypia (the detailed calculator) takes some time to understand and also it doesn't work on newer Mac OS, so although many people are instructed to use it, they don't. That, and it's also a pain in anypia to test a range of earnings assumptions, given the way earnings are entered into the interface.

User avatar
neurosphere
Posts: 2820
Joined: Sun Jan 17, 2010 1:55 pm

Re: Downloadable Social Security Benefit Estimator (repost)

Post by neurosphere » Fri May 04, 2018 9:17 am

RetireBy60 wrote:
Thu May 03, 2018 8:57 pm
Question: I read on ssa.gov (section 404.221. Computing Your Average Monthly Wage, (a) General) that only the wages after turning 21 are counted when calculating the social security benefit. Am I reading that wrong? The statement about "after 1950, or after reaching 21, if later" is confusing.
I didn't look into this too much, but I suspect the reference to age 21 no longer applies. I also agree that the wording is confusing and it does seem to suggest earnings prior to 21 are not counted. Also, later there is this "We count the years beginning with 1951 or (if later) the year you reached age 22..."

I did find elsewhere a mention that earnings for "household chores" for a family member under 21 did not qualify for SS (either benefits, or credits, or both, no sure).

I also found many unofficial references which say that earnings at any age count towards the AIME, as well as all the calculators which don't otherwise make any disclaimer that earnings under age 21 don't count. This question has also been asked in various forums and online columns, with the answer being that all earnings count, but never with a mention as to why this age 21 issue is a question to begin with.

I suspect there is some update/addition which removed the age 21 requirement? Just guessing that must be the case. Either that, or we're completely misunderstanding section 404.221!

[EDIT: I figured it out, but don't have time to fully explain but hopefully this helps... The age 21 reference is not which earnings years are calculated, but rather how they figure out "elapsed years" of work that one would have reasonably been expected to work by age 62. Then they subtract 5 years from these reasonable number of years to come up with 35 years of earnings the average. So the age 21 reference is more or less an explanation of why they use the number of years they use to average. Because one would not reasonably be expected to have earnings years before 21. But if one DOES have earnings years prior, they count. The reason this section is awkward is because it provides a method for using LESS than 35 years of earnings for those of a certain age. Now that 1950 is way in the past for most earnings, this section can simply be summarized as "we use the top 35 years". :D At least, I think that's right! I had the read section 404.221 many times through...]

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

Re: Downloadable Social Security Benefit Estimator (repost)

Post by FiveK » Fri May 04, 2018 12:42 pm

neurosphere wrote:
Fri May 04, 2018 8:28 am
My update will agree with the personal finance toolbox for 2018 benefits, and neither will be correct. Anypia and online scenarios as shown by SS are the correct benefits. The reason is that, officially, the COLAs are applied to the PIA each year. And each year the PIA is rounded down prior to the next COLA calculation. For example (PIA1*COLA1) = (Unrounded PIA2). Then this number is rounded down to the dime and then the next COLA is applied. Instead, personal finance toolkit seems to just apply the cumulative COLA to the initial PIA. I do the something similar for those over aged 62+.
Thanks for the explanation. Makes sense that a program such as anypia can do that calculation relatively easily, while it would be relatively complex for a spreadsheet writer (short of using Visual Basic, etc.).

Post Reply