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: 2665
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC

Downloadable Social Security Benefit Estimator (repost)

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

[This is a repost of a previous post which removes a web link which was in violation of the board's policies. Sorry moderators! :happy This post contains a download link only]

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 3" and incorporated some changes suggested by posters in the deleted thread.

Here is the link: https://1drv.ms/x/s!Atj-cegEXidXpHDxqSzL01fOnDyH

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 Thu Nov 09, 2017 1:18 pm, edited 1 time in total.
-- Real name: Sotirios Keros. If you have to ask "Is a Target Retirement fund right for me?", the answer is yes.

User avatar
BogleMelon
Posts: 999
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: 2665
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC

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: 2665
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC

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: 2665
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC

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.

User avatar
BogleMelon
Posts: 999
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: 503
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: 2665
Joined: Sun Jan 17, 2010 1:55 pm
Location: NYC

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.

Post Reply