Excel worksheet for calculating SS bend points

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
Post Reply
Topic Author
Nyc10036
Posts: 571
Joined: Wed Oct 05, 2016 6:29 pm

Excel worksheet for calculating SS bend points

Post by Nyc10036 »

I was Googling and found https://www.physicianonfire.com/ssa2017/

I am not keen on signing up, so can I create that worksheet on my own?
rkhusky
Posts: 10173
Joined: Thu Aug 18, 2011 8:09 pm

Re: Excel worksheet for calculating SS bend points

Post by rkhusky »

Nyc10036 wrote: Thu Feb 08, 2018 12:21 pm I was Googling and found https://www.physicianonfire.com/ssa2017/

I am not keen on signing up, so can I create that worksheet on my own?
Yes, you can. In fact, it is instructive to do so.
ionball
Posts: 230
Joined: Wed Jan 31, 2018 12:17 pm

Re: Excel worksheet for calculating SS bend points

Post by ionball »

Search on Bogleads.org for the Shared Social Security Benefit Estimator. It's a spreadsheet that you can download and I think it shows bend points. See if that's what you need.
Topic Author
Nyc10036
Posts: 571
Joined: Wed Oct 05, 2016 6:29 pm

Re: Excel worksheet for calculating SS bend points

Post by Nyc10036 »

Thank you.

I have downloaded the Shared Social Security Benefit Estimator

And I have input the earnings in the beige Earnings column.

Do I have to do anything else?

If not, how do I interpret the numbers?
ionball
Posts: 230
Joined: Wed Jan 31, 2018 12:17 pm

Re: Excel worksheet for calculating SS bend points

Post by ionball »

Nyc10036 wrote: Thu Feb 08, 2018 1:33 pm Thank you.

I have downloaded the Shared Social Security Benefit Estimator

And I have input the earnings in the beige Earnings column.

Do I have to do anything else?

If not, how do I interpret the numbers?
Be sure to read the Instructions tab of the spreadsheet which has some information. Did you see one or two bend points on the graph? You could enter future data one year at a time and note the magnitude of change, then decide if the % change is worth continuing to work for those future years.

There's an excellent thread on this subject with input from some very smart minds:
viewtopic.php?t=199103
See the post by #cruncher for details in case you want to build your own spreadsheet.
Topic Author
Nyc10036
Posts: 571
Joined: Wed Oct 05, 2016 6:29 pm

Re: Excel worksheet for calculating SS bend points

Post by Nyc10036 »

I was trying to use the spreadsheet in LibreOffice, but I had to open the spreadsheet in Excel 2010 to see the graphs.

I see the bends now --- I think --- , but I am having difficulty understanding what my "bend" numbers are.
Do I read them off the graph?
Tamales
Posts: 1465
Joined: Sat Jul 05, 2014 10:47 am

Re: Excel worksheet for calculating SS bend points

Post by Tamales »

Duplicate. see next post.
Last edited by Tamales on Thu Feb 08, 2018 6:43 pm, edited 1 time in total.
Tamales
Posts: 1465
Joined: Sat Jul 05, 2014 10:47 am

Re: Excel worksheet for calculating SS bend points

Post by Tamales »

Tamales wrote: Thu Feb 08, 2018 6:39 pm When you set up your spreadsheet, think of it as 3 separate components that you sum up (1st, 2nd bend points, and beyond the 2nd BP)
Using 2017 bend points and assuming full retirement age, here's the (annualized) contribution due to the 1st BP (AIME x 12 on horizontal axis, PIA x 12 on vertical)
Image
and due to the 2nd BP:
Image
and beyond the 2nd bend point:
Image

I used $1000 increments for AIMEx12 so it's not fine granularity.

Summing them together, here's the annual dollar amount of AIME vs PIA:
Image

and here's annual AIME as % of PIA
Image
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

Nyc10036 wrote: Thu Feb 08, 2018 12:21 pm I am not keen on signing up, so can I create that worksheet on my own?
See also the SocialSecurity tab in the personal finance toolbox spreadsheet.
Topic Author
Nyc10036
Posts: 571
Joined: Wed Oct 05, 2016 6:29 pm

Re: Excel worksheet for calculating SS bend points

Post by Nyc10036 »

Tamales wrote: Thu Feb 08, 2018 6:39 pm When you set up your spreadsheet, think of it as 3 separate components that you sum up (1st, 2nd bend points, and beyond the 2nd BP)
Using 2017 bend points and assuming full retirement age, here's the (annualized) contribution due to the 1st BP (AIME x 12 on horizontal axis, PIA x 12 on

{snip}
Am I suppose to see graph lines that look like that?
All I see are several lines that look the first graph.
pshonore
Posts: 6874
Joined: Sun Jun 28, 2009 2:21 pm

Re: Excel worksheet for calculating SS bend points

Post by pshonore »

Here are the historical bend points. Note they are fixed for an individual in the year you reach 62. And they can decrease from year to year like 2011

https://www.ssa.gov/oact/cola/bendpoints.html
Tamales
Posts: 1465
Joined: Sat Jul 05, 2014 10:47 am

Re: Excel worksheet for calculating SS bend points

Post by Tamales »

Nyc10036 wrote: Thu Feb 08, 2018 7:52 pm
Tamales wrote: Thu Feb 08, 2018 6:39 pm When you set up your spreadsheet, think of it as 3 separate components that you sum up (1st, 2nd bend points, and beyond the 2nd BP)
Using 2017 bend points and assuming full retirement age, here's the (annualized) contribution due to the 1st BP (AIME x 12 on horizontal axis, PIA x 12 on

{snip}
Am I suppose to see graph lines that look like that?
All I see are several lines that look the first graph.
I'm not sure what you're wanting to see/show. If you gave more details, I'm sure someone here could help you get there.
I was just showing graphically what the components of the spreadsheet could be, including the intermediate results.
I'm not sure if that's what you were looking for.
Topic Author
Nyc10036
Posts: 571
Joined: Wed Oct 05, 2016 6:29 pm

Re: Excel worksheet for calculating SS bend points

Post by Nyc10036 »

Tamales wrote: Thu Feb 08, 2018 8:08 pm I'm not sure what you're wanting to see/show. If you gave more details, I'm sure someone here could help you get there.
I was just showing graphically what the components of the spreadsheet could be, including the intermediate results.
I'm not sure if that's what you were looking for.
I am not sure what I am suppose to see to help me determine the bend points.
Is the graph suppose to tell me that?
On my graph I have 5 or 6 lines I don't remember exactly as I am on Linux.

Even after reading the instructions I am not sure what I am suppose to do after I have entered my earnings to help me find my bend points.
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

Nyc10036 wrote: Thu Feb 08, 2018 8:56 pm I am not sure what I am suppose to see to help me determine the bend points.
Not familiar with that tool, but for the personal finance toolbox spreadsheet:
- Cells with the green background were changed from the template numbers for this "for example"
- bend point values are shown in cells B110 and B111
- see graph for where this PIA calculation falls relative to the break points

Image

Image
Last edited by FiveK on Sun Jul 22, 2018 11:53 am, edited 1 time in total.
Topic Author
Nyc10036
Posts: 571
Joined: Wed Oct 05, 2016 6:29 pm

Re: Excel worksheet for calculating SS bend points

Post by Nyc10036 »

FiveK wrote: Thu Feb 08, 2018 10:20 pm
Nyc10036 wrote: Thu Feb 08, 2018 8:56 pm I am not sure what I am suppose to see to help me determine the bend points.
Not familiar with that tool, but for the personal finance toolbox spreadsheet:
- Cells with the green background were changed from the template numbers for this "for example"
- bend point values are shown in cells B110 and B111
- see graph for where this PIA calculation falls relative to the break points

Image

Image
Thanks. I will give that worksheet a try when I get on my Windows PC. :happy
User avatar
PhysicianOnFIRE
Posts: 465
Joined: Fri Jan 08, 2016 3:46 pm
Location: Up North

Re: Excel worksheet for calculating SS bend points

Post by PhysicianOnFIRE »

Nyc10036 wrote: Thu Feb 08, 2018 12:21 pm I was Googling and found https://www.physicianonfire.com/ssa2017/

I am not keen on signing up, so can I create that worksheet on my own?
You can download and immediately unsubscribe as soon as you have the sheet (easiest for me). Alternatively, PM me and I'll send it to you. I plan to update it for 2018 soon.

:beer
-PoF
User avatar
neurosphere
Posts: 3775
Joined: Sun Jan 17, 2010 1:55 pm

Re: Excel worksheet for calculating SS bend points

Post by neurosphere »

Nyc10036 wrote: Thu Feb 08, 2018 12:21 pm I was Googling and found https://www.physicianonfire.com/ssa2017/

I am not keen on signing up, so can I create that worksheet on my own?
Edit: SORRY! I didn't read the rest of the thread before posting. The information below is mostly redundant! :mrgreen:

You can also try the downloadable calculator available here: viewtopic.php?t=231913

You can put in your existing earnings, and any assumptions for future earnings, and the graph wont explicitly show you the bend points (unless there is an obvious bend in the graph, so it CAN show bendpoints) but it WILL show you exactly the "slope" by which future earnings affect your benefit.

Note that unless you are in your 60s, figuring out bend points is somewhat meaningless. Bendpoints are highly affected by assumptions of wage inflation. Every calculator I've seen except the SS's 'anypia' assume zero wage growth. For anyone 10+ years away from retirement, this assumption will cause significant errors in what the bend points turn out to be. So I wouldn't use bendpoints calculated from now to make any real planning decisions. However, it's a good educational tool. E.g. perhaps you can figure out you'll be so far above the 2nd bend point that it doesn't much matter for you.

In the calculator I've linked to does allow you to enter assumptions for wage growth and CPI. Change each number (peach boxes in O7 and P7) and see how your future benefit changes as you change assumptions for these. Note that the output is in Sept 2017 dollars. SS calculators for those who are not nearing retirement will typically underestimate your actual future benefit because they do not take inflation between now and a benefit date into account. Edit to add: Actually, if wage growth = CPI growth, then the estimates are accurate. But historically, wage growth exceeds CPI growth. But who knows what the future holds? :D
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
User avatar
neurosphere
Posts: 3775
Joined: Sun Jan 17, 2010 1:55 pm

Re: Excel worksheet for calculating SS bend points

Post by neurosphere »

Nyc10036 wrote: Thu Feb 08, 2018 7:52 pm
Tamales wrote: Thu Feb 08, 2018 6:39 pm When you set up your spreadsheet, think of it as 3 separate components that you sum up (1st, 2nd bend points, and beyond the 2nd BP)
Using 2017 bend points and assuming full retirement age, here's the (annualized) contribution due to the 1st BP (AIME x 12 on horizontal axis, PIA x 12 on

{snip}
Am I suppose to see graph lines that look like that?
All I see are several lines that look the first graph.
I can't see any of the images, but...

Why are you focused on the bend points? The graph will show you exactly how your benefit will change with each additional year of earnings. It gives more info than just bend points. In essence, the graph makes knowing the actual bend points irrelevant. They are "there", yes. If you can't see them, then the slope changes are small for your set of earnings estimates surrounding those points, or you are past both bend points (e.g. one or more bendpoints occurred prior to the earliest age plotted on the graph).
Last edited by neurosphere on Sun Jul 22, 2018 12:55 pm, edited 1 time in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:56 pm, edited 1 time in total.
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

letsgobobby wrote: Sun Jul 22, 2018 12:29 pm Doesn't everyone have the same bend points? I don't know the first but the second is roughly around $2.3 million in lifetime earnings over your highest 35 years, in 2018 dollars.
Everyone born in the same year will have the same bend points. One takes the average wage index (AWI) in the year one turns 60, divided by the AWI in 1979, and multiplies that ratio times the 1979 bend points of $180/mo and $1085/mo.
User avatar
PhysicianOnFIRE
Posts: 465
Joined: Fri Jan 08, 2016 3:46 pm
Location: Up North

Re: Excel worksheet for calculating SS bend points

Post by PhysicianOnFIRE »

Alright, you motivated me to update the spreadsheet referenced above for 2018.

And for my Boglehead friends, you can download directly without sharing your e-mail address. Click to download the spreadsheet.

Image


Feedback is welcome.

edit: updated to calculate based on only the top 35 years of indexed wages for those who have more than 35 years of contributions. Big thanks to Tamales for the detailed assistance.

:beer
-PoF
Last edited by PhysicianOnFIRE on Tue Jul 24, 2018 3:03 pm, edited 1 time in total.
Tamales
Posts: 1465
Joined: Sat Jul 05, 2014 10:47 am

Re: Excel worksheet for calculating SS bend points

Post by Tamales »

Hi PoF, for your spreadsheet to be more generally applicable the spreadsheet should sort the entire annual indexed wages column by amount, take the sum of the top 35, and divide that by (35*12)

Here's an example of using Excel functions LARGE() and ROWS() to create a separate column which gives the top 35 from your full list in E4:E65
http://www.exceltip.com/working-with-fo ... order.html

edit: picking a random place in your spreadsheet to add this sorting process:
In cell f8 of your spreadsheet, add the formula:
=LARGE($E$4:$E$65,ROWS(F$8:F8))

Then use the fill handle on the cell (lower right corner of cell) and drag that to auto-fill down to cell f42.

Then in cell g8 enter the formula:
=ROWS(F$8:F8)
and drag/fill that to cell g42. This just serves as numbering for the list from 1-35.

Then sum cells f8:f42, and divide by 420.
For your example the result is the same since there are fewer than 35 non-zero entries, but in general a person might have more than 35 annual indexed wage entries.
Dottie57
Posts: 9165
Joined: Thu May 19, 2016 5:43 pm
Location: Earth Northern Hemisphere

Re: Excel worksheet for calculating SS bend points

Post by Dottie57 »

Tamales wrote: Mon Jul 23, 2018 9:43 am Hi PoF, for your spreadsheet to be more generally applicable the spreadsheet should sort the entire annual indexed wages column by amount, take the sum of the top 35, and divide that by (35*12)

Here's an example of using Excel functions LARGE() and ROWS() to create a separate column which gives the top 35 from your full list in E4:E65
http://www.exceltip.com/working-with-fo ... order.html

edit: picking a random place in your spreadsheet to add this sorting process:
In cell f8 of your spreadsheet, add the formula:
=LARGE($E$4:$E$65,ROWS(F$8:F8))

Then use the fill handle on the cell (lower right corner of cell) and drag that to auto-fill down to cell f42.

Then in cell g8 enter the formula:
=ROWS(F$8:F8)
and drag/fill that to cell g42. This just serves as numbering for the list from 1-35.

Then sum cells f8:f42, and divide by 420.
For your example the result is the same since there are fewer than 35 non-zero entries, but in general a person might have more than 35 annual indexed wage entries.
The calculated amt is different than SS web site. I think it is because of the zer in 2018. Is that why you are suggesting the sort?
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:54 pm, edited 1 time in total.
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

letsgobobby wrote: Mon Jul 23, 2018 12:16 pm
FiveK wrote: Sun Jul 22, 2018 12:43 pm
letsgobobby wrote: Sun Jul 22, 2018 12:29 pm Doesn't everyone have the same bend points? I don't know the first but the second is roughly around $2.3 million in lifetime earnings over your highest 35 years, in 2018 dollars.
Everyone born in the same year will have the same bend points. One takes the average wage index (AWI) in the year one turns 60, divided by the AWI in 1979, and multiplies that ratio times the 1979 bend points of $180/mo and $1085/mo.
Can you give some examples where the second bend point is substantially different than $2.3 million in adjusted earnings over the highest 35 years?
Using the 'SocialSecurity' tab of the personal finance toolbox spreadsheet, with maximum earnings for years 1983-2017, for someone born in
1952, indexed earnings are $3,840,699 and the second bend point is $4,917
1956, indexed earnings are $4,173,387 and the second bend point is $5,397

AIME for $2.3 million indexed earnings is $5,476.

Whether those are substantial differences is in the eye of the beholder. ;)
User avatar
racy
Posts: 264
Joined: Sun Mar 30, 2008 7:38 am

Re: Excel worksheet for calculating SS bend points

Post by racy »

PhysicianOnFIRE wrote: Sun Jul 22, 2018 4:54 pm Alright, you motivated me to update the spreadsheet referenced above for 2018.

And for my Boglehead friends, you can download directly without sharing your e-mail address. Click to download the spreadsheet.

Image


Feedback is welcome.

:beer
-PoF
So, this is only good for those turning 60 in 2016, right? (Since the index used is 1).
Tamales
Posts: 1465
Joined: Sat Jul 05, 2014 10:47 am

Re: Excel worksheet for calculating SS bend points

Post by Tamales »

I only did a quick review of the spreadsheet but it seems it is missing a few steps if doing a full benefit calculation at ages 62-70 if you're currently under eligibility age. There's an extension of the indexed earnings based on the most recent NAWI value (the 2016 value for a 2018 calculation) that you have to account for, out to the year before you turn x, where x is the age your're doing the calculation for, and in turn that affects the sum of the largest 35 divided by 420 (i.e. the AIME), and in turn, you need that number to correctly reflect the AIME adjustments above in order for the monthly benefit estimate numbers and adjustments for various ages to be correct.
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:53 pm, edited 1 time in total.
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

letsgobobby wrote: Mon Jul 23, 2018 4:57 pm
FiveK wrote: Mon Jul 23, 2018 12:34 pm Using the 'SocialSecurity' tab of the personal finance toolbox spreadsheet....
Why do people born in different years have to earn different amounts to reach the second bend point? This doesn’t make sense to me.
Assuming that spreadsheet is correct (it seems to be), the math is as stated.

Making sense of congressional lawmaking, however, is beyond my ken. ;)
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:52 pm, edited 1 time in total.
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

letsgobobby wrote: Mon Jul 23, 2018 8:27 pm I don’t know... those numbers don’t pass the smell test for me. Over $4m in lifetime earnings to get to the second bend point?
Correct, it takes much less than $4 million to reach the second bend point - but that's not what I (or the personal finance toolbox spreadsheet) was saying.

As downloaded, that spreadsheet has maximum earnings for years 1983-2017, and a birth year of 1952. Without changing the earnings, I changed the birth year to 1956. That changes the indexed earnings (which are irrelevant to the bend point calculation), but also changes the bend points (which is your question).

In other words, the bend points can change based on one's birth year. See that spreadsheet if you want to look at the formulas used. In words, it takes the average wage index (AWI) in the year one turns 60, divided by the AWI in 1979, and multiplies that ratio times the 1979 bend points of $180/mo and $1085/mo.

Thinking more about it, changing the bend points makes sense because the average wage increases. The bend points in 1979 dollars must be increased to compensate. If today's wages were indexed down to 1979 dollars, then the bend points could stay constant. But it is older wages that are indexed up to more recent dollars, so the bend points must also change.
Tamales
Posts: 1465
Joined: Sat Jul 05, 2014 10:47 am

Re: Excel worksheet for calculating SS bend points

Post by Tamales »

FiveK wrote: Mon Jul 23, 2018 11:29 pm [...]In other words, the bend points can change based on one's birth year. See that spreadsheet if you want to look at the formulas used. In words, it takes the average wage index (AWI) in the year one turns 60, divided by the AWI in 1979, and multiplies that ratio times the 1979 bend points of $180/mo and $1085/mo.

Thinking more about it, changing the bend points makes sense because the average wage increases. The bend points in 1979 dollars must be increased to compensate. If today's wages were indexed down to 1979 dollars, then the bend points could stay constant. But it is older wages that are indexed up to more recent dollars, so the bend points must also change.
That's because of the birth years (or age) you chose for your examples. 1956 happens to be the top of a threshold in the calculation as done in that spreadsheet. If you set the birth year to a year after 1956, the AIM and the bend points it references stay the same.

The calculation chain done in your SSA annual statement is a bit different that what's done in this spreadsheet if you're younger than claiming age, and SSA presumes you continue working until you claim, and in turn it extends the NAMI and therefore the indexed earnings, and therefore the numbers used as the top 35, resulting in a different PIA.

PS, if you increment the birth year in that spreadsheet back from 1956, you'll see that the bend points chosen by the spreadsheet track the steps in the SSA's long-term bend point history table:
https://www.ssa.gov/OACT/COLA/bendpoints.html

PPS I wonder where that spreadsheet comes up with its SS maximums (column C) for the future years.
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

Tamales wrote: Tue Jul 24, 2018 12:26 am That's because of the birth years (or age) you chose for your examples. 1956 happens to be the top of a threshold in the calculation as done in that spreadsheet. If you set the birth year to a year after 1956, the AIM and the bend points it references stay the same.
Yes, because of the "wage choice" in cell B10: birth years after 1956 will use AWIs that have not yet been determined, so the assumption is "no change".
The calculation chain done in your SSA annual statement is a bit different that what's done in this spreadsheet if you're younger than claiming age, and SSA presumes you continue working until you claim, and in turn it extends the NAMI and therefore the indexed earnings, and therefore the numbers used as the top 35, resulting in a different PIA.
Yes, different assumptions may lead to different results.
PS, if you increment the birth year in that spreadsheet back from 1956, you'll see that the bend points chosen by the spreadsheet track the steps in the SSA's long-term bend point history table:
https://www.ssa.gov/OACT/COLA/bendpoints.html
Good to know it is consistent with SSA!
PPS I wonder where that spreadsheet comes up with its SS maximums (column C) for the future years.
Probably one of the many references listed on that tab, but which...?
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

Tamales wrote: Tue Jul 24, 2018 12:26 am PPS I wonder where that spreadsheet comes up with its SS maximums (column C) for the future years.
Perhaps C. PROGRAM-SPECIFIC ASSUMPTIONS AND METHODS, intermediate values under "Contribution and benefit base".
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:51 pm, edited 1 time in total.
soccerrules
Posts: 1033
Joined: Mon Nov 14, 2016 4:01 pm

Re: Excel worksheet for calculating SS bend points

Post by soccerrules »

letsgobobby wrote: Tue Jul 24, 2018 4:02 pm Maybe I’m not asking the right question.

What I want to get at is this: Say a 30, 40, or 50 something year old single man wants to work only until such time as he has reached the second bend point in his SS retirement benefit. He wants to know how much in today’s dollars he has to have earned in eligible social security wages in his highest 35 years in order to achieve that. What is the answer? I have understood the answer to be “about $2.3 million in today’s dollars.” Is that not correct?
google it.
for 2018 the 2nd bend point PIA starts at 5,397. 5,397 multiplied by 420 months (35 years) you will get $2,266,740. (indexed earnings)

Looking back 10 years the 2nd bend point PIA was 4,288 , or $1,800,960.

I believe the $1,800,960 is indexed up to match the $2,266,740 for 2018 dollars.
Don't let your outflow exceed your income or your upkeep will be your downfall.
User avatar
FiveK
Posts: 10285
Joined: Sun Mar 16, 2014 2:43 pm

Re: Excel worksheet for calculating SS bend points

Post by FiveK »

letsgobobby wrote: Tue Jul 24, 2018 4:02 pm Maybe I’m not asking the right question.

What I want to get at is this: Say a 30, 40, or 50 something year old single man wants to work only until such time as he has reached the second bend point in his SS retirement benefit. He wants to know how much in today’s dollars he has to have earned in eligible social security wages in his highest 35 years in order to achieve that. What is the answer? I have understood the answer to be “about $2.3 million in today’s dollars.” Is that not correct?
Yes, the second bend point for someone turning 62 in 2018 is $5397/mo. Multiplied by 420 months, that is ~$2.3 million dollars in total indexed wages.

As I see soccerrules already noted....
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:51 pm, edited 1 time in total.
soccerrules
Posts: 1033
Joined: Mon Nov 14, 2016 4:01 pm

Re: Excel worksheet for calculating SS bend points

Post by soccerrules »

letsgobobby wrote: Tue Jul 24, 2018 4:58 pm
soccerrules wrote: Tue Jul 24, 2018 4:30 pm I believe the $1,800,960 is indexed up to match the $2,266,740 for 2018 dollars.
Which was really my point. In today's wage adjusted dollars, one has to have thirty five year eligible earnings of about $2.3 million to reach the second bend point, regardless of how old one is. In other words, if the goal is knowing when one reaches the second SS bend point, no spreadsheet is required, other than a way to convert prior year earnings into 2018 dollars. Then make sure that adds up to around $2.3 million.

Yes?
I really don't know. This is conjecture on my part.
If the 2nd bend point moved essentially $466K for the last 10 years it is likely to move again over the next 10 years.
I think much of this is either based on the year you hit age 60 (or FRA). The number will differ for a 30 year old and a 60 year old-- but the opportunity and playing field is level for both- because of the indexing factor.
If the 2nd bend point moves up it is also increasing with it the yearly indexing factors and each years final indexed earnings move upward (or on a very rare occasion down).
In looking at my own record I hit $2.3M in indexed earning after about 19 years of maxed earnings or 27 years of some less than maxed and 15 maxed years. Although I may stop earning my overall earnings will continue to rise because of the indexing.
I am not sure using $2.3M for a current 22 year old really is a good target because it will probably increase as it did in the prior 10 years leading up to 2018.
A rising index lifts all years indexed,
Don't let your outflow exceed your income or your upkeep will be your downfall.
Tamales
Posts: 1465
Joined: Sat Jul 05, 2014 10:47 am

Re: Excel worksheet for calculating SS bend points

Post by Tamales »

I don't get the fascination with the second bend point (including some other recent threads), especially as a strategic marker in one's retirement strategy. Maybe someone can explain. Is it something to do with "getting back at the government" so you quit paying taxes on something where you'd get diminished returns after the second bend point? That's about all I can think of, and you're certainly entitled to take that approach if you want. But I'd point out that we all pay a lot of other taxes, or equivalent of taxes, over our lifetimes--far beyond this 6.2%--where we don't get any return at all, and/or are effectively subsidizing things we don't derive a direct benefit from. Anyway, I'm hoping someone will elaborate since maybe I'm missing something and should give new attention to this heretofore shunned factor.
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:50 pm, edited 1 time in total.
User avatar
neurosphere
Posts: 3775
Joined: Sun Jan 17, 2010 1:55 pm

Re: Excel worksheet for calculating SS bend points

Post by neurosphere »

letsgobobby wrote: Tue Jul 24, 2018 4:58 pm
soccerrules wrote: Tue Jul 24, 2018 4:30 pm I believe the $1,800,960 is indexed up to match the $2,266,740 for 2018 dollars.
Which was really my point. In today's wage adjusted dollars, one has to have thirty five year eligible earnings of about $2.3 million to reach the second bend point, regardless of how old one is. In other words, if the goal is knowing when one reaches the second SS bend point, no spreadsheet is required, other than a way to convert prior year earnings into 2018 dollars. Then make sure that adds up to around $2.3 million.

Yes?
YES.

And your focus on "eligible" earnings is well placed, based on previous questions on this and other threads.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
letsgobobby
Posts: 12073
Joined: Fri Sep 18, 2009 1:10 am

Re: Excel worksheet for calculating SS bend points

Post by letsgobobby »

Deleted
Last edited by letsgobobby on Sun Apr 28, 2019 11:47 pm, edited 1 time in total.
User avatar
PhysicianOnFIRE
Posts: 465
Joined: Fri Jan 08, 2016 3:46 pm
Location: Up North

Re: Excel worksheet for calculating SS bend points

Post by PhysicianOnFIRE »

Nyc10036 wrote: Thu Feb 08, 2018 12:21 pm I was Googling and found https://www.physicianonfire.com/ssa2017/

I am not keen on signing up, so can I create that worksheet on my own?
PM me and I'll gladly send it to the email of your choice.

You also have the option of entering your email, downloading the file, and unsubscribing immediately thereafter. [edit: or downloading from my earlier post above. I didn't initially realize this was an old thread resurrected.]

:beer
-PoF
dkb140
Posts: 82
Joined: Mon May 21, 2018 3:30 pm

Re: Excel worksheet for calculating SS bend points

Post by dkb140 »

I replaced the values in your ranked list with:

Code: Select all

=LARGE(E$3:E$57,1)
=LARGE(E$3:E$57,2)
...
=LARGE(E$3:E$57,35)
So it automatically pulls out the 35 highest-paid years.
Post Reply