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

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

[Edit: The latest version is now in google sheets. It's a draft version for 2021, released in Nov 2020 and is here.

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

Last edited by neurosphere on Tue Nov 17, 2020 5:17 pm, edited 10 times in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

Below is an example of the main output, which is a graph which displays your annual SS benefit as a function of retirement age and claiming date for a given set of earnings you've entered (actual or theoretical).

The top (grey) line is the benefit if you wait to age 70, while the bottom (light blue) line is age 62.

The example below assumed a person born in 1972, who started work at age 25, and assumes they will earn at least the maximum earnings until retirement. Suppose one wanted to know the benefit if he/she stopped working at age 53, and then claimed at age 69? The x-axis represents the assumed "stop-work" date (e.g. retirement). So go to 53 on the x-axis and look up to the line which represents a claiming age of 69 (top line is age 70, so it's the brown line just below it). You'll see that the benefit is just under \$40,000.

You can also see 3 hitches or elbows in the data. The first about about age 27 represents the first "bend point", the second at age 43 is the second bend point. The 3rd elbow at age 59 is where the earner has 35 years of earnings (and therefore all zero earning years have been eliminated from the calculations).

[Edit, it seems the hosted image is no longer available]
Last edited by neurosphere on Sun Oct 13, 2019 12:43 pm, edited 1 time in total.
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

I've created an update, which I'm calling version 2 ("v2") DRAFT (because I've not kicked all the tires yet to make sure I didn't break something.
[edit on Nov 13, the link below contains some fixes after an error discovered as discussed below]
https://1drv.ms/x/s!Atj-cegEXidXx2pDBKnWaRLTXMe6

Changes include:

-- Taxes paid**: I've added a tab which keep track of your total/cumulative amount of employee SS taxes paid. But this only includes the "OASI" portion of the tax, not the amounts which SS attributes or allocates for "DI" and "HI". The listed dollar amounts for taxes paid is in terms of Sept 2018 dollars.

-- I increased the total range of ages one can have earnings. That is, the calculator now allows one as young as 18 years old to enter earnings data through 'very old'.

-- The y-axis on the benefit graph now scales to maximize the available chart area used. I had reasons for not scaling it earlier.

-- There is now a legend for each claiming age on the chart (e.g. age 62 to 70)

-- For anyone who wishes to enter non-zero assumptions for future wage and price inflation, I've moved that to a standalone tab.

As always, let me know if there are any questions! I'll make this the "official" update if no one finds any problems with it.

Neurosphere

** Note that SS is an INSURANCE program, and not an investment or retirement program. That is, it's not "fair" to directly compare the amount of tax you paid vs your expected/assumed benefit. For example, SS provides a benefit for your non-working spouse. This benefit was promised to you from your earliest earnings (assuming you eventually qualified for an old-age benefit). If you never had a spouse who earned any benefit on your record, you still HAD that potential benefit available, during a time when you theoretically may have or could have ended up with a spouse who could have had a benefit based only on your record. You may not put much value on that kind of insurance, but it has SOME value. Consider the data on taxes paid to be a form of education or amusement.
Last edited by neurosphere on Tue Nov 13, 2018 10:47 am, edited 2 times in total.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
UnclePennybags
Posts: 69
Joined: Fri May 09, 2014 10:50 am

I compared the results from this spreadsheet to my output from the prior version and it appears correct (which is to say that it matches the official website). The contribution section is interesting. Does the OASI portion only include employee contributions or does is also include the portion contributed by either the employer or through self-employment tax?
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote: Thu Nov 08, 2018 11:39 am I compared the results from this spreadsheet to my output from the prior version and it appears correct (which is to say that it matches the official website). The contribution section is interesting. Does the OASI portion only include employee contributions or does is also include the portion contributed by either the employer or through self-employment tax?
Employee contributions only.

Employer contributions (whether self or 'other') are not included. In theory, you could double the tax to get an estimate which includes the employer contribution, but recognizing that self-employed get a tax deduction for that part, and there are cases where the employer part can be more than simply double the employee tax if one has more than one employer and total earnings between the two of them exceed the max taxable earnings.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Silk McCue
Posts: 4877
Joined: Thu Feb 25, 2016 7:11 pm

Sorry if it is an obvious answer or already provided that I have missed, but I downloaded and saved to my PC and the file is password protected when I try to make changes to the Inflation Adjustments tab.

Cheers
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

Silk McCue wrote: Thu Nov 08, 2018 2:59 pm Sorry if it is an obvious answer or already provided that I have missed, but I downloaded and saved to my PC and the file is password protected when I try to make changes to the Inflation Adjustments tab.
You didn't miss anything. You can change the peach colored boxes, which will change the assumptions for all future years. Currently you cannot change each assumption individually. I didn't figure people wanted to get that specific, and once you If you'd like to change each year individually, I can probably do that in a future update. Although, your question reminds me that now that the inflation assumptions are in a new tab, I can more easily provided instructions/info about the table right there on that page.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Silk McCue
Posts: 4877
Joined: Thu Feb 25, 2016 7:11 pm

neurosphere wrote: Thu Nov 08, 2018 3:05 pm
Silk McCue wrote: Thu Nov 08, 2018 2:59 pm Sorry if it is an obvious answer or already provided that I have missed, but I downloaded and saved to my PC and the file is password protected when I try to make changes to the Inflation Adjustments tab.
You didn't miss anything. You can change the peach colored boxes, which will change the assumptions for all future years. Currently you cannot change each assumption individually. I didn't figure people wanted to get that specific, and once you If you'd like to change each year individually, I can probably do that in a future update. Although, your question reminds me that now that the inflation assumptions are in a new tab, I can more easily provided instructions/info about the table right there on that page.
Thanks. I was was just moving to fast and focused on what I couldn't do and missed what I could do. That's a lot like life sometimes isn't it?

Cheers
UnclePennybags
Posts: 69
Joined: Fri May 09, 2014 10:50 am

Just started playing with some future year assumptions this afternoon and noticed that you have the SSWB set quite low for the next 13 years. Can you explain why that is? The indexing also looks a bit off to me as well.

Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote: Mon Nov 12, 2018 1:34 pm Just started playing with some future year assumptions this afternoon and noticed that you have the SSWB set quite low for the next 13 years. Can you explain why that is? The indexing also looks a bit off to me as well.
I can't see your image. Can you give me an example of the problems you are seeing? Perhaps your image contained the example.

The SSWB is \$132,900 for 2019. If you keep the default assumptions of no increase in wage inflation, it will stay \$132,900 for all future years. This is the same assumption (i.e. no wage inflation) that SS uses in their online calculators and the their personalized benefit estimates. If you'd like to set your own wage inflation estimate, you can do so on the "inflation assumptions" tab.

As for the indexing being "off", can you give an example? I.e. give me a specific calendar year and an amount of earnings, and tell me what my calculator has as the output for indexed wages, vs what you think it should be instead?
J295
Posts: 2795
Joined: Sun Jan 01, 2012 11:40 pm

I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.
Bacchus01
Posts: 3182
Joined: Mon Dec 24, 2012 9:35 pm

neurosphere wrote: Tue Nov 13, 2018 1:42 am
UnclePennybags wrote: Mon Nov 12, 2018 1:34 pm Just started playing with some future year assumptions this afternoon and noticed that you have the SSWB set quite low for the next 13 years. Can you explain why that is? The indexing also looks a bit off to me as well.
I can't see your image. Can you give me an example of the problems you are seeing? Perhaps your image contained the example.

The SSWB is \$132,900 for 2019. If you keep the default assumptions of no increase in wage inflation, it will stay \$132,900 for all future years. This is the same assumption (i.e. no wage inflation) that SS uses in their online calculators and the their personalized benefit estimates. If you'd like to set your own wage inflation estimate, you can do so on the "inflation assumptions" tab.

As for the indexing being "off", can you give an example? I.e. give me a specific calendar year and an amount of earnings, and tell me what my calculator has as the output for indexed wages, vs what you think it should be instead?
The \$132900 caught me off guard as well. I read and realize how it’s calc’d. I’m curious what people are actually assuming for their calculations? While that is the SSA method, it sure doesn’t seem practical.
UnclePennybags
Posts: 69
Joined: Fri May 09, 2014 10:50 am

Let me try linking the image a different way and see if that works:

Basically for 2020-2032, I'm seeing a SSWB < 132,900. It starts at 94,200 and gradually rises to hit 132,900 in 2033. Also, somehow those years are indexed to numbers > SSWB for 2020-2030. I haven't changed the Inflation estimates.
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

Bacchus01 wrote: Tue Nov 13, 2018 7:49 am The \$132900 caught me off guard as well. I read and realize how it’s calc’d. I’m curious what people are actually assuming for their calculations? While that is the SSA method, it sure doesn’t seem practical.
When you say "not practical" I assume you are referring to the SS default assumptions for inflation metrics when providing estimates? Which is to say they assume zero for wage and price inflation? It's very practical actually, because it's easy and all dollars remain in today's dollars. But it's certainly not accurate, which is probably what you mostly meant. SS's best guess for future wage and price inflation (aka the SS Trustees Intermediate Assumptions) are show in the table on the inflation assumptions tab. In the past, wage inflation has generally been higher than price inflation, and they assume this trend will continue. If so, then actual SS benefits will be LARGER (in today's dollars) than the estimates provided by SS. But who really knows what the future holds?
BolderBoy
Posts: 5186
Joined: Wed Apr 07, 2010 12:16 pm

J295 wrote: Tue Nov 13, 2018 6:16 am I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.
+1. Agreed completely. Thanks you!

Neurosphere, would it be possible to modify the graph to show the "bend points" which the SSA talks about on their calculator? The graph currently shows the point at which one ceases to generate FICA taxable income.

Thanks.
"Never underestimate one's capacity to overestimate one's abilities" - The Dunning-Kruger Effect
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote: Tue Nov 13, 2018 9:49 am Let me try linking the image a different way and see if that works:

Basically for 2020-2032, I'm seeing a SSWB < 132,900. It starts at 94,200 and gradually rises to hit 132,900 in 2033. Also, somehow those years are indexed to numbers > SSWB for 2020-2030. I haven't changed the Inflation estimates.
Yes, there is clearly a problem in the new draft version! It seems there is a disconnect in the values when the bottom of the first column transitions to the top of the second column. E.g. from 2019 to 2020 the SSWB gets lower. I broke something when expanding the range of dates/ages over which the calculator is valid...

Let me work on a fix!
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

UnclePennybags wrote: Tue Nov 13, 2018 9:49 am Let me try linking the image a different way and see if that works:
I've made the fix, here is the new link to this draft version: https://1drv.ms/x/s!Atj-cegEXidXx2pDBKnWaRLTXMe6
Let me know if it has fixed the problem for you. Many thanks for bringing it to our attention.

[Edit: I edited the link, same calculator but I inadvertently forgot to prevent it from being edited/changed online]
Last edited by neurosphere on Tue Nov 13, 2018 10:48 am, edited 1 time in total.
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

BolderBoy wrote: Tue Nov 13, 2018 10:08 am
J295 wrote: Tue Nov 13, 2018 6:16 am I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.
+1. Agreed completely. Thanks you!

Neurosphere, would it be possible to modify the graph to show the "bend points" which the SSA talks about on their calculator? The graph currently shows the point at which one ceases to generate FICA taxable income.
The graph shows the bend points by plotting the actual bends. You should see them in the graph. But there are some problems with that. For example, the first bend point may be off the chart to the right. Also, there can be multiple "bends" due to uneven earnings, when one reaches 35 years of earnings, and when 35 maximal years have been reached. So the actual second bend point may not be obvious. BUT, but the entire point of knowing what the second bend point is, is to know when benefits start to accrue at a slower rate than previously for give amount of earnings. The graph shows exactly what the slope is for any set of earnings, so one can see exactly what might be "gained" from more work. This makes knowing the bend point irrelevant, no? The graphs shows you that answer, plus much more.

That said, it would be nice to be able to label the bend points for educational/illustrative purposes.

I'm not sure my excel skills are good enough to add data to the chart, such that there is a special label or line which points to each bend point. But, that's something that would be fun to learn, perhaps. I could simply list what the bend points are, and one could find it on the graph. Maybe rather than label the graph, I could create a table that shows, for example, "bend points 1/2 = X/Y, and that is reached at ages Z and Q".
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
BolderBoy
Posts: 5186
Joined: Wed Apr 07, 2010 12:16 pm

neurosphere wrote: Tue Nov 13, 2018 10:46 am
BolderBoy wrote: Tue Nov 13, 2018 10:08 am
J295 wrote: Tue Nov 13, 2018 6:16 am I’m 59 so a few years away from making decisions, but just want to give a big shout out to OP for providing this! Really generous of you.
+1. Agreed completely. Thanks you!

Neurosphere, would it be possible to modify the graph to show the "bend points" which the SSA talks about on their calculator? The graph currently shows the point at which one ceases to generate FICA taxable income.
The graph shows the bend points by plotting the actual bends. You should see them in the graph.
Ah! I see them now. I was interpreting the bends incorrectly.
That said, it would be nice to be able to label the bend points for educational/illustrative purposes.
Yes! I learned about "bend points" here on the forum.
I'm not sure my excel skills are good enough to add data to the chart, such that there is a special label or line which points to each bend point. But, that's something that would be fun to learn, perhaps. I could simply list what the bend points are, and one could find it on the graph. Maybe rather than label the graph, I could create a table that shows, for example, "bend points 1/2 = X/Y, and that is reached at ages Z and Q".
. Very well done on creating this. And the only problem I saw with the v2 (?) draft has been fixed in the latest draft (I can't keep straight which draft is which).
"Never underestimate one's capacity to overestimate one's abilities" - The Dunning-Kruger Effect
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

BolderBoy wrote: Tue Nov 13, 2018 10:58 am . Very well done on creating this. And the only problem I saw with the v2 (?) draft has been fixed in the latest draft (I can't keep straight which draft is which).
Yeah, me neither! The "latest" is suffixed with "v2 draft v2". It's the second revision of the draft revision of the original version 1. Totally clear, right? Lol.

I do think that bend point labels would be nice. I'll add that to a to-do file.

Someone had previously requested I add the benefit in monthly terms instead of in addition to annual terms on the graph. I tried to add a second y axis, but excel does not allow for a simple scaled secondary y axis.

I'll have to go back to the other thread(s) and remind myself what feature requests have been made in the past. I think I've already implemented those I was capable of though.
corpgator
Posts: 34
Joined: Wed Jul 22, 2015 12:00 pm

The speadsheet is great, but could be please have the password to unprotect the sheets or remove the password altogether? On my version of excel 2010, I cannot even make columns wider to be able to see the amounts I put in he boxes of 5 figures or greater.
Last edited by corpgator on Sat Feb 02, 2019 2:36 pm, edited 2 times in total.
Big Dog
Posts: 2227
Joined: Mon Sep 07, 2015 4:12 pm

what, no love for Jan 1 birthdays?
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

corpgator wrote: Sat Feb 02, 2019 2:11 pm The speadsheet is great, but could be please have the password to unprotect the sheets or remove the password altogether? On my version of excel 2010, I cannot even make columns wider to be able to see the amounts I put in he boxes of 5 figures or greater.
Interesting. In my spreadsheet, the earnings column in "D" fits all values up to \$9,999,999 (i.e. 7 digits). And I just found out that column "I" will auto-adjust and expand to fit any value. I can only assume it's because I'm using a different version of excel (I'm using whatever the latest version is).

I hate to give the password to unprotect the sheets, because in the past that has led to a lot of PMs about problems due to inadvertent changes to formulas/etc. I'll send you a PM.
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

Big Dog wrote: Sat Feb 02, 2019 2:21 pm what, no love for Jan 1 birthdays?
Ha, yes, well, one can't have it all. If your birthday is Jan 1, and you need actual numbers, anypia is your friend.

I realize you're just being playful, but note that the main purpose of the spreadsheet is for general modeling and illustration of things like bendpoints with a visualization of the general effects of additional years of earnings. I don't think many people who are doing retirement planning need to know what their exact benefit is for the first 1-12 month, but instead want to know about what their annual spending will be throughout retirements. Which is why I have most of the output in terms of annual amounts.

Also, other than anypia, it's the only calculator I know which allows one to enter non-zero assumptions for wage and price inflation. These assumptions, in most cases, have a much larger impact on your future benefit than that month/day one was born.

But here is a plug for a calculator which WILL take day/month considerations into effect!
https://socialsecurity.tools/

It will show you the exact benefit you will initially receive for any birthdate and earnings assumptions ,and then show when delayed credits (if any) will kick in and change the benefit. But this calculator also assumes zero for inflation measures, which means the "exact benefit" will be wrong 100% of the time (unless we have "exactly" zero wage/price inflation for each year between now and when the benefit starts, to which I'm essentially assigning a zero probability, lol).
Peter Foley
Posts: 5206
Joined: Fri Nov 23, 2007 10:34 am
Location: Lake Wobegon

Thanks for doing this. It is a lot of work to get this type of application right.
Aegon
Posts: 1
Joined: Mon Apr 29, 2019 11:18 am

Thanks for creating the spreadsheet!!! But I am experiencing some of the same problems that one user has already mentioned when using an older version of excel. Could you please PM me the password to unlock the sheets please!
KlangFool
Posts: 19088
Joined: Sat Oct 11, 2008 12:35 pm

Thank you for an excellent social security calculator.

I created a very simple calculator just to check my number.

viewtopic.php?f=10&t=280442

KlangFool
Harry Livermore
Posts: 621
Joined: Thu Apr 04, 2019 5:32 am

Thanks so much for creating this. It's another data point for our planning.
Any idea why my annual SSA statement shows a different (higher) number than is produced by the spreadsheet? I entered in the SS earnings figures as shown in the SSA report.
Example:
SSA Statement benefit @ age 67: \$3,050
Neurosphere calc benefit @ age 67: \$2,631
Would that be an "inflation assumption" variable?
Cheers,
Harry
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

Harry Livermore wrote: Sat May 25, 2019 7:23 am Any idea why my annual SSA statement shows a different (higher) number than is produced by the spreadsheet? I entered in the SS earnings figures as shown in the SSA report.
Example:
SSA Statement benefit @ age 67: \$3,050
Neurosphere calc benefit @ age 67: \$2,631
Would that be an "inflation assumption" variable?
Social Security's estimates and my calculator's default assumptions are that there will be zero price inflation and zero wage inflation. So inflation assumptions cannot account for the difference.

The most likely difference is that SS has assumed a different set of future earnings than you have. SS assumes you will continue to earn the same amount as you have recently earned (last year's earnings, unless that's zero in which case they take the earnings from two years ago), and projects that income forward to to retirement age. With my calculator, your future earnings are obviously whatever you have entered.
Harry Livermore
Posts: 621
Joined: Thu Apr 04, 2019 5:32 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.
Cheers,
Harry
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 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.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Allan
Posts: 876
Joined: Wed Feb 21, 2007 9:15 pm
Location: Houston

Thank you! I am 68 and delaying to age 70, so this is very helpful.

Allan
Harry Livermore
Posts: 621
Joined: Thu Apr 04, 2019 5:32 am

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
itsalive123
Posts: 1
Joined: Thu May 30, 2019 12:42 pm

Posts: 68686
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

itsalive123, Welcome!

neurosphere - I agree with itsalive123. While you'd like to prevent inadvertent modification of the formulas, it's much better to simply protect the sheets without a password. If someone wants to investigate the sheets in detail, they'll be able to do so. It will also help debug any problems that may arise in those sheets.

Can you please remove the password from those sheets (but remain protected)?
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

LadyGeek wrote: Thu May 30, 2019 3:28 pm neurosphere - I agree with itsalive123. While you'd like to prevent inadvertent modification of the formulas, it's much better to simply protect the sheets without a password. If someone wants to investigate the sheets in detail, they'll be able to do so. It will also help debug any problems that may arise in those sheets.
Can you please remove the password from those sheets (but remain protected)?

https://1drv.ms/x/s!Atj-cegEXidXzTjrqDs9nT62XEWh
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 10:36 am

neurosphere wrote: Thu May 30, 2019 3:52 pm Here's a link to a version without a password!
https://1drv.ms/x/s!Atj-cegEXidXzTjrqDs9nT62XEWh
It would be good if you also said "this is v3.0" & the date you released it. Also maybe the schedule when the Govt revises those datum (once a year, quarter, etc). If this is the 2019 version why still use the 2017 wage index?:
"This spreadsheet uses the most recently released 2017 Average Wage Index values (released in 2018), and the Sept 2018 CPI. Several test cases agree with anypia 2019.1. Let me know if you find errors!"

that way in the future someone would have an idea of how old the version they find on their hard drive, is.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Posts: 68686
Joined: Sat Dec 20, 2008 5:34 pm
Contact:

Thank you, but "Taxes Paid" and "Inflation Assumptions" are still password protected. (I've downloaded the file and am using LibreOffice Calc.)

I also found the 3 hidden sheets and recommend unhiding them.
To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

LadyGeek wrote: Thu May 30, 2019 4:53 pm Thank you, but "Taxes Paid" and "Inflation Assumptions" are still password protected. (I've downloaded the file and am using LibreOffice Calc.)

I also found the 3 hidden sheets and recommend unhiding them.
I'm going to leave things as is for now. I think anyone who knows enough or intends to dig deeper and especially desires to make any changes can see that that formulas point to hidden sheets and how to unhide them if they wish. All the formulas are viewable and the logic/math can be followed. Although, kudos to anyone who wants to spend time trying to figure out the flow of data/inputs/etc! This is certainly not the best learning tool for that!

All of the password requests to date had been in order to adjust the formatting of the main input/output page which created a "######" in older versions of excel for some inputs. This version allows for that correction to be made.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

Bongleur wrote: Thu May 30, 2019 4:21 pm
neurosphere wrote: Thu May 30, 2019 3:52 pm Here's a link to a version without a password!
https://1drv.ms/x/s!Atj-cegEXidXzTjrqDs9nT62XEWh
It would be good if you also said "this is v3.0" & the date you released it. Also maybe the schedule when the Govt revises those datum (once a year, quarter, etc). If this is the 2019 version why still use the 2017 wage index?:
"This spreadsheet uses the most recently released 2017 Average Wage Index values (released in 2018), and the Sept 2018 CPI. Several test cases agree with anypia 2019.1. Let me know if you find errors!"

that way in the future someone would have an idea of how old the version they find on their hard drive, is.
I update the first post on this thread with the latest version and also note when the data/version becomes invalid, e.g. Oct 2019.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 10:36 am

I meant, put the info into the spreadsheet itself.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
Bongleur
Posts: 2276
Joined: Fri Dec 03, 2010 10:36 am

Bongleur wrote: Thu May 30, 2019 11:01 pm I meant, put the info into the spreadsheet itself.
Seeking Iso-Elasticity. | Tax Loss Harvesting is an Asset Class. | A well-planned presentation creates a sense of urgency. If the prospect fails to act now, he will risk a loss of some sort.
rennale
Posts: 67
Joined: Sun Apr 23, 2017 6:50 am

I'd be interested to know how to modify this excellent spreadsheet to deal with WEP (Windfall Elimination Provision). Such as adding an input cell for the WEP percentage.
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

Bongleur wrote: Thu May 30, 2019 11:02 pm
Bongleur wrote: Thu May 30, 2019 11:01 pm I meant, put the info into the spreadsheet itself.
Will put on the to-do list!
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

rennale wrote: Fri May 31, 2019 7:46 am I'd be interested to know how to modify this excellent spreadsheet to deal with WEP (Windfall Elimination Provision). Such as adding an input cell for the WEP percentage.
Personally, while I know of the concept of WEP, I know nothing of the formula. I could add this to the "to-do" list, but I suspect I'll never get around to it. But it's the kind of thing I might tackle if I were retired and had extra time.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

There is a web-based version to this calculator here: https://www.doctoredmoney.org/ss-benefit-calculator

That might be useful for those having issues with older versions of excel, or no excel at all, or formulas that don't work in other software.

Until recently, it allowed cut/paste of earnings into the cells. But microsoft must have made a change to how excel spreadsheets are embedded on websites, because it no longer works. Thus, unfortunately, one has to type in all the earnings manually. If anyone knows a workaround or some other way to efficiently enter the data, I'd be happy to hear it!
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
rennale
Posts: 67
Joined: Sun Apr 23, 2017 6:50 am

As far as I can tell it's just a matter of being able to alter the 90% bend point PIA calculation to a different percentage. With WEP, the 90% number reduces by 5% per year for every year fewer than 30 "substantial earnings" years. So, if you only have 29 substantial earnings years (and a separate pension that is based on the years that you didn't pay Social Security) then your bend point multiplier is 85%. For 28 years years the multiplier is 80%, etc., all the way down to 40% for 20 or fewer years of substantial earnings. https://www.ssa.gov/pubs/EN-05-10045.pdf

So all that's needed is the 90% number in the Main worksheet PIA calculation to be adjustable. I think.
longinvest
Posts: 4535
Joined: Sat Aug 11, 2012 8:44 am

neurosphere wrote: Wed Oct 31, 2018 11:48 am [Edit: This is the latest version "v3" as of May 30 2019, with data valid until Oct 2019 -- https://1drv.ms/x/s!Atj-cegEXidXzTjrqDs9nT62XEWh
Neurosphere, do you plan a "v4" soon, with data valid until in Oct 2020?
Bogleheads investment philosophy | One-ETF global balanced index portfolio | VPW
Topic Author
neurosphere
Posts: 3910
Joined: Sun Jan 17, 2010 1:55 pm

longinvest wrote: Sun Oct 13, 2019 10:08 am
neurosphere wrote: Wed Oct 31, 2018 11:48 am [Edit: This is the latest version "v3" as of May 30 2019, with data valid until Oct 2019 -- https://1drv.ms/x/s!Atj-cegEXidXzTjrqDs9nT62XEWh
Neurosphere, do you plan a "v4" soon, with data valid until in Oct 2020?
Working on it at this very moment.

Might be a couple of weeks before it's final, not sure. Also, anypia is not yet updated, so I can't check my work. And another "also", I'm working on a small addition/feature, and am currently trying to teach myself how to use radio buttons.
If you have to ask "Is a Target Date fund right for me?", the answer is "Yes".
longinvest
Posts: 4535
Joined: Sat Aug 11, 2012 8:44 am