Spreadsheet to show how taxable SS benefits will affect you

Non-investing personal finance issues including insurance, credit, real estate, taxes, employment and legal issues such as trusts and wills
User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Spreadsheet to show how taxable SS benefits will affect you

Post by PapaGeek » Fri Sep 11, 2015 10:01 am

9/23/2015 update:
The content of this thread has lead to a Wiki page: https://www.bogleheads.org/wiki/Social_Security_tax_impact_calculator

My thanks to all who have added to this thread so far and almost all of your comments have either improved or added to the spreadsheet and Wiki page. I will continue to follow this thread and PMs to make continuing improvements to the topic.

Version 6 of the spreadsheet was uploaded on 9/28/2015 a lot of changes, see my post on 9/28, link display some sort of problem, but the file does download.
Version 5 of the spreadsheet was uploaded on 9/23/2015 to add the "Singles" tab
Version 4 of the spreadsheet was uploaded on 9/21/2015 to create separate setup tab.

Again my thanks to all who have posted on this thread, now to the original content of the initial post:


The spreadsheet for this topic is available on google drive for download:
https://drive.google.com/file/d/0B-beVhyq5Az-Zl9jZXY2V2NSeUU/view?usp=sharing

But before we get started, according to the old cliché, a picture is worth a thousand words:

Image
This is an illustration of an individual who earned a COLA adjusted average income of $75,000 who would get about $28,200 in SS benefits and then got an additional $31,800 from other taxable sources to live off of $60,000, 80% of their pretax earnings. The tallest orange tick mark shows that person’s gross income and the red and blue lines above it shows their income tax rate on the last dollar they got from their taxable source.



Image
Setting up the spreadsheet for yourself if relatively simple. The yellow frames are the ones you have to update. The ones on the right should be updated annually and are currently up to date for 2015. They are used by the spreadsheet to determine your marginal tax bracket and how much tax you will pay at standard deductions. For example: you pay $923 plus 15% of your earnings over $9225 (line above) until you reach $37,450. To keep things accurate you should look up these number on line each year.

The SS and IRA boxes are the primary input to the chart. Since the spreadsheet works in $100 increments you should round both entries to the closest $100. The SS number is your annual SS benefit and the IRA number is the amount of your other taxable income. The second number is only used to place tick marks on the chart to show you your situation.

Image
There is a calculator on the right side of the spreadsheet to help you to estimate these values if you are not receiving SS benefits yet. The link at the top take you to the SS estimator where you will find the two yellow monthly numbers in step 5 of their instructions. You then input your average income(s) and the “factor” that will be applied to your full retirement benefit based on the age you plan to retire. You can also enter the percentage of your current gross income that you want to retire on. I used 80% because many of us put 10% of our income into a 401K and pre-tax earnings necessary to pay your FICA taxes is also about 10%.
This results in the tanish SS and IRA numbers, note that the married number are half of the total for the couple.

Back to the usefulness of the top illustration. The tick marks from right to left show your gross income, your net income if SS was not taxed, net income for a single person, and per capita net income for a married person. The difference between single and married is due to the pink SS limits, the married limits are less than twice the single limit.

The highest gross income tick is what to use for planning. In this case the single individual is barely avoiding the 46.25% marginal tax bracket, The Hump, and the married couple is almost at the end of their hump. The start of the married hump is about $57,000 and their gross tick is at $60,000, a difference of $3,000 per capita. If they could find an alternative non-taxable source for the $6,000 and reduce their taxable retirement income by that amount, they could avoid The Hump and be in relatively the same situation as the single individual.

But in both cases they should look for additional sources for tax free income in case they have an unexpected expense. Also, the pink Taxable SS Limits are fixed and do not adjust with inflation, so these breakpoints will probably get worse over time.

If you can do it before you start collecting SS benefits, the conversion of some of your Traditional IRA to a ROTH IRA Is a great way to create non-taxable income during retirement. Note the tax free municipal bond income is used in the calculations using the pink limits where ROTH income is not. Also note that there are penalties that you will pay if you use your ROTH converted capital in less than 5 years.

There is another thing that the married couple could do if they do not have a source for non-taxable income. Since they are over the hump, they could convert some of their standard IRA to ROTH at the 25% and maybe 28% brackets. They may have to wait 5 years to use each conversion amount, but it would save the 46.25% later by paying the 25% today. They might also consider using a home equity loan during the first 5 years to avoid the 46.25% marginal tax bracket for a 4% loan interest, then use the ROTH money to pay off the loan after 5 years.

The key to all of this is to be aware of how the taxation of your Social Security benefits will affect your retirement tax rates. As I said above, a picture is worth a thousand words, so I created this spreadsheet to show me that picture.

I hope this spreadsheet and information is useful to everyone.

PapaGeek

NOTE: Due to the first two replies to this post I just changed the term "tax bracket" to "marginal tax bracket". The 1993 amendment to Social Security said that for every additional $1 you increase your taxable income and additional $0.85 of your Social Security becomes taxable income and 25% ( the actual bracket ) of $1.85 is $0.4625 ( a marginal tax bracket of 46.25% ).
Last edited by PapaGeek on Mon Sep 28, 2015 9:59 am, edited 12 times in total.

User avatar
BolderBoy
Posts: 3395
Joined: Wed Apr 07, 2010 12:16 pm
Location: Colorado

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by BolderBoy » Fri Sep 11, 2015 10:42 am

PapaGeek wrote:In this case the single individual is barely avoiding the 46.25% tax bracket

This is way beyond me and obviously you've put a lot of effort into it - it is very pretty.

There is no such thing as a 46.25% tax bracket that I've ever seen. Where did you get that?

I have a crude spreadsheet which I use for what I think is a similar purpose to what you are doing, but it uses the traditional tax brackets that are known as of 2015. It shows that my tax bracket will go UP when I start taking SS and RMDs so as you advise, maxing out Roth conversions beforehand is salient.

User avatar
ObliviousInvestor
Posts: 3198
Joined: Tue Mar 17, 2009 9:32 am
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by ObliviousInvestor » Fri Sep 11, 2015 10:46 am

BolderBoy wrote:
PapaGeek wrote:In this case the single individual is barely avoiding the 46.25% tax bracket

This is way beyond me and obviously you've put a lot of effort into it - it is very pretty.

There is no such thing as a 46.25% tax bracket that I've ever seen. Where did you get that?

It's not a 46.25% tax bracket, per se, but rather a 46.25% marginal tax rate on additional non-Social-Security income. This is because each additional dollar of income not only causes the normal amount of income tax (25 cents in this case), it also causes 85 cents of Social Security to become taxable, at a 25% rate, resulting in another 21.25 cents of income tax.
Mike Piper, author/blogger

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Fri Sep 11, 2015 1:24 pm

BolderBoy wrote:
PapaGeek wrote:In this case the single individual is barely avoiding the 46.25% tax bracket

This is way beyond me and obviously you've put a lot of effort into it - it is very pretty.

There is no such thing as a 46.25% tax bracket that I've ever seen. Where did you get that?

I have a crude spreadsheet which I use for what I think is a similar purpose to what you are doing, but it uses the traditional tax brackets that are known as of 2015. It shows that my tax bracket will go UP when I start taking SS and RMDs so as you advise, maxing out Roth conversions beforehand is salient.


This one is easy to show you. Go to TurboTax TaxCaster, https://turbotax.intuit.com/tax-tools/calculators/taxcaster/. On the first page, just fill in that you are 68 years old the go to Other Income. Fill in that your IRA income is $34,000 and your Social Security is $28,000. Note that in the Where You Stand column on the right side that you owe $5,438 and your Taxable Income is $38,550 and at the bottom your Marginal Tax Rate is 25%. Now change the IRA income to $35,000. Your new Tax due is $5,900 which is an additional $462, actually $462.50 rounded, add another $1,000 and it will go up $463, and your Taxable Income is now $40,400, up $1,850.

The marginal tax rate is still 25%, but the Social Security amendments of 1983 and 1993 changed our benefits from tax free to partially taxable. The additional withdraw of $1,000 made an extra $850 taxable Social Security. OK, lets call it your “MarginalTax Bracket”. It is actually 185% of 25% which is 46.25%.

That is why I created my spread sheet, to show the “Marginal” tax brackets at various income levels.

My thanks to both of the previous two responses. I just updated the original post to use the term "Marginal Tax Bracket"

User avatar
OAG
Posts: 1037
Joined: Sat Mar 03, 2007 11:54 am
Location: Currently Central Ohio, USA

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by OAG » Fri Sep 11, 2015 4:09 pm

Great work Papageek. Anyone approaching the point they will be taking SS should study this well (actually would be better to do that long before that time) and get a understanding of the mathematics at play. There is another thing most do not consider which is regarding MEDICARE premiums - where AGI impacts increased premiums ($85,001 for single filers).
OAG=Old Army Guy. Retired CW4 USA (US Army) in 1979.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 12, 2015 6:43 am

I just made an update to the spreadsheet to help with ROTH conversion decisions.

If you are in or near the hump it is a good idea to find a non-taxable source of income during retirement to move you away from the hump. A ROTH conversion of some of you traditional IRA to a ROTH is one alternative. I just added a feature to the spreadsheet to help you determine the level of non-taxable income you are comfortable with.

Image
The previous version of the spreadsheet only had the SS and IRA (taxable income) entries. I added the ROTH (non-taxable income) entry and the total income display to help you “play” with the chart.

Image
The chart now includes an additional tick on the right to show your gross income including ROTH.
For comparison here is the same chart before adding the ROTH feature.

Image
Notice how the first four ticks got closer together because less tax was paid. By drilling down into the data line, the married couple paid $14,293 in Federal tax without the ROTH income and $9,372 with the ROTH income. To look at it another way, their GROSS income was $60,000 in both charts. In the non-ROTH chart their after federal tax income was $45,707. In the ROTH chart their after federal tax income was $50,628, a difference of an extra $4,921 of spendable cash by converting part of their gross income from a taxable (IRA) source to a non-taxable (ROTH) source.

I am wide open on suggestions to improve the chart.

Carl53
Posts: 1430
Joined: Sun Mar 07, 2010 8:26 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Carl53 » Sat Sep 12, 2015 6:49 am

Papa

Recheck your calculation of Taxable SS in column E and compare it to Pub 915 . I did not check it for the other cases (columns). I believe that you will get the correct amount if you change the 6000 factors to 4500.

User avatar
Wildebeest
Posts: 975
Joined: Fri Dec 27, 2013 2:36 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Wildebeest » Sat Sep 12, 2015 7:06 am

PapaGeek,

This is a work of wonder. It deserves a place in the Wiki.

I especially like the graphic representation.
The Golden Rule: One should treat others as one would like others to treat oneself.

HoosierJim
Posts: 385
Joined: Wed Mar 24, 2010 7:11 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by HoosierJim » Sat Sep 12, 2015 9:28 am

Great work - I used to think roth conversions taxed at 25% were insane but this makes me rethink it.

Carl53
Posts: 1430
Joined: Sun Mar 07, 2010 8:26 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Carl53 » Sat Sep 12, 2015 9:41 am

Papa

I like the colored graphics, but please explain a little more about the different lines. Correct me if I'm wrong that the red an blue represent a single individual's share whether filing as single or married jointly but having 2X the single income. Please provide a little more on the green, is it supposed to represent no taxation of the Ss income for a single or half of a married couple? Also, explain each of the ticks please. You might include a note that calculations are for those 65 and older as you've included the extra standard deduction.

Please correct your use of the tax tables in H6, H7, H12, H13 (your new version has these in column I). You are using an incorrect summation of taxes for lower brackets as you begin the 25 and 28% brackets. For instance you begin the 25% bracket for MJ stating that 11235 taxes are owed for 74900 in taxable income. This should be 10313 or 10% * 18450 + 15% * (74900-18450). Consequently, when you jump from the 15% to 25% bracket there is an unusual step change in the cumulative tax shown as you scroll down columns I and U. $100 of additional income is shown to bump up the taxes owed by roughly $500 for a single and $200 of extra income results in an extra $1000 tax for a married couple.

User avatar
Prokofiev
Posts: 928
Joined: Mon Feb 19, 2007 9:45 pm
Location: New Orleans

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Prokofiev » Sat Sep 12, 2015 10:01 am

Why is there a hump that lasts for about $8,000 in the single case? The extra tax can only exist for 15% of the total SS amount.
15% of $28,200 is $4230. What am I missing?
Everything should be made as simple as possible, but not simpler - Einstein

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 12, 2015 10:03 am

Carl53 wrote:Papa

Recheck your calculation of Taxable SS in column E and compare it to Pub 915 . I did not check it for the other cases (columns). I believe that you will get the correct amount if you change the 6000 factors to 4500.


Thank you Carl, you are correct. I have change my spreadsheet and will update Google drive shortly. When I do it might change the link in the first post for you to upload a new version.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 12, 2015 10:12 am

HoosierJim wrote:Great work - I used to think roth conversions taxed at 25% were insane but this makes me rethink it.


I’m 68 and my GF is 59 and still working and plans to retire at 62. After investigating the situation we are starting a program this year to convert as much as we can from traditional to ROTH while she is still working while staying in the 25% bracket. We are even using a home equity loan at 3.9% which is only about 2.6% after tax to pay the conversion taxes instead of having the taxes taken out of the converted assets. This on top of her $6,500 standard ROTH contributions will maximize the amount we can put into ROTH and give us plenty of flexibility during her retirement. We can then continue to use he $6,555 pension after tax income to pay off the loan and have it paid off in less than 5 years when the conversions start to become usable. Remember there is a penalty if you use converted funds in less than 5 years.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 12, 2015 10:53 am

Carl53 wrote:Papa

I like the colored graphics, but please explain a little more about the different lines. Correct me if I'm wrong that the red an blue represent a single individual's share whether filing as single or married jointly but having 2X the single income. Please provide a little more on the green, is it supposed to represent no taxation of the Ss income for a single or half of a married couple? Also, explain each of the ticks please. You might include a note that calculations are for those 65 and older as you've included the extra standard deduction.

Please correct your use of the tax tables in H6, H7, H12, H13 (your new version has these in column I). You are using an incorrect summation of taxes for lower brackets as you begin the 25 and 28% brackets. For instance you begin the 25% bracket for MJ stating that 11235 taxes are owed for 74900 in taxable income. This should be 10313 or 10% * 18450 + 15% * (74900-18450). Consequently, when you jump from the 15% to 25% bracket there is an unusual step change in the cumulative tax shown as you scroll down columns I and U. $100 of additional income is shown to bump up the taxes owed by roughly $500 for a single and $200 of extra income results in an extra $1000 tax for a married couple.


The different lines might be considered a political statement, but they were no meant that way. After all the 1983 amendment was done with a Republican president and the 1993 amendment was done with a Democrat president. The green line is what we would be paying if neither of those amendments were ever written and none of your Social Security benefit was taxed.

I decided not to have two separate graphs, one for single and one for married filing jointly which is why the blue line represents how a single person is taxed compared to the red line represents basically how each person in a married filing jointly couple is taxed. Again, not to be political, it shows how a married couple pays more taxes earlier as compared to two singles living together. This is because the $32,000 and $44,000 taxation points for a married couple are not double the $25,000 and $34,000 taxation points for each single person.

Yes, with the extra column ”C” added for non-taxable income “ROTH” the taxation table is now columns I through K instead of columns H through J, and the deduction and exemption numbers are in column H instead of G.

The chart basically does not cover the 28% bracket. By the time you hit that bracket in retirement the maximum 85% of your Social Security benefit has been made taxable and you are way over the hump and back to standard taxation levels. My spreadsheet and chart only go as high as the point where you are over the hump and back to the 25% bracket. At that point you still have options on ways to push yourself to a point were you pay little or no taxes at the 46.25% level.

As far as the jump point, I verified them using TurboTax TaxCaster. I will recheck my jump points again now that the chart has been modified. I do see some small differences now between TaxCaster and my spreadsheet. They seem minor, but the sheet is supposed to be 100% accurate so I will check into it, find the issue, and repost a new spreadsheet. The sheet is still very usable for planning purposes. The picture is still very close to accurate, but as a Geek, it has to be perfect, and it will be shortly.

dcb
Posts: 100
Joined: Wed Sep 26, 2007 12:44 pm
Location: Southwest Michigan

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by dcb » Sat Sep 12, 2015 10:59 am

Thanks for the spreedsheet. It appears to be just what I have been looking for.

However, I am confused about the whole single vs. married thing. In cells A3 and B3 do I enter the total amounts for our S.S. and our IRA withdrawals? Or do I enter half of the totals for each and then the combined married amount appears in cells A10 and b10?
dcb

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 12, 2015 11:04 am

Prokofiev wrote:Why is there a hump that lasts for about $8,000 in the single case? The extra tax can only exist for 15% of the total SS amount.
15% of $28,200 is $4230. What am I missing?


The 1983 amendment said that the 50% rate lasted until 50% of your entire benefit was taxable. The 1993 amendment said that the 85% rate which started at different basis points lasted until 85% of your benefit was taxable.

You must have read something wrong. 15% of your benefit remains non-taxable income, but 85% can become taxable as your basis continues to grow. The basis equals half of your SS benefit plus basically all of your other taxable income. The 50% and 85% basis points for a single person are $25,000 and $34,000, for a married couple they are $32,000 and $44,000. Not double the single person’s points which is why the red line jumps faster than the blue line.

By the way, when you factor in the blue hump, once everyone is over the humps the taxes come back to equal.

PS: have to go for a honey-let's shop run. Will be back shortly to checkout the spreadsheet again and re-post a new one!

PPS: Just because she has been my GF for almost 13 years and not my wife, doesn't mean I don't know how to say "Yes Dear"! "Happy GF, Happy life is also a correct statement!", it just doesn't rhyme.
Last edited by PapaGeek on Sun Sep 13, 2015 5:46 am, edited 1 time in total.

User avatar
Prokofiev
Posts: 928
Joined: Mon Feb 19, 2007 9:45 pm
Location: New Orleans

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Prokofiev » Sat Sep 12, 2015 11:40 am

"You must have read something wrong. 15% of your benefit remains non-taxable income, but 85% can become taxable as your basis continues to grow."

No. It seems I didn't understand how SS is taxed . . .

So the maximum taxable is 85%
Everything should be made as simple as possible, but not simpler - Einstein

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 12, 2015 9:54 pm

Carl53 wrote:Papa

Please correct your use of the tax tables in H6, H7, H12, H13 (your new version has these in column I). You are using an incorrect summation of taxes for lower brackets as you begin the 25 and 28% brackets. For instance you begin the 25% bracket for MJ stating that 11235 taxes are owed for 74900 in taxable income. This should be 10313 or 10% * 18450 + 15% * (74900-18450). Consequently, when you jump from the 15% to 25% bracket there is an unusual step change in the cumulative tax shown as you scroll down columns I and U. $100 of additional income is shown to bump up the taxes owed by roughly $500 for a single and $200 of extra income results in an extra $1000 tax for a married couple.

My error in the tax tables for married

_________10%___$18,450
_$1,845__15%___$74,900
$11,235__25%__$151,200
$37,800__28%__$230,450

The first column should have been
_________10%___$18,450
_$1,845__15%___$74,900
$10,313__25%__$151,200
$29,388__28%__$230,450

dcb wrote:Thanks for the spreedsheet. It appears to be just what I have been looking for.

However, I am confused about the whole single vs. married thing. In cells A3 and B3 do I enter the total amounts for our S.S. and our IRA withdrawals? Or do I enter half of the totals for each and then the combined married amount appears in cells A10 and b10?
dcb


In my next version of the spreadsheet I re-labeled those cells as “SS Benefit”, “Taxable Income”, and “non Taxable”.

SS Benefits are just that, Taxable Income includes IRA withdrawals plus taxable pensions and annuities, anything that is taxable, non Taxable would income ROTH withdrawals, money from a reverse mortgage, money withdrawn from savings, etc.

Yes you do half them and then round the half to the nearest $100. The spreadsheet will double the numbers for a married couple.


To all, I am making a few changes to fix the math error and make sure no one can repeat that mistake. I will test the new sheet and update google drive tomorrow.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sun Sep 13, 2015 5:41 am

The link in my first post now gets version 3 of the spreadsheet.

Carl53 caught two errors on the sheet, I might start calling him PapaGeek Jr! Thanks!

The first error was in my formulas for taxable social security for a single person. I was adding the 50% amount for a married couple, $32,000 to $44,000 which is $12,000 half of with is $6,000. I merely copied the formula to the single person’s calculation. The proper add on for a single person is $25,000 to $34,000 equals $9,000 at 50% is $4,5000.

Taxing $1,500 less moved the single blue line to the right for the hump.

Image
Carl also mentioned that the taxes in the chart after a married couple reaches the 25% bracket were not correct. The was an input error when I entered the tax bracket information. I changed the spreadsheet to calculate the “tax paid up to now” column for you.
Image
Original version looked like:
Image
You now have to update the Rate chart with only the Rate and where it ends. The spreadsheet now computes the “tax paid up to now” column.

Note the error that cause the issue that Carl pointed out. I had manually entered $11,235 as the amount paid up to the 15% to 25% transition. The correct amount should have been $10,313. The only thing it affected on the chart was the location of the after tax tick mark for a married couple.

I also changed the wording of the inputs from SS, IRA, and ROTH to more generic terms. The IRA, now Taxable Income, should have always included things like taxable pensions, annuities, interest, etc., not just your withdrawals from your traditional IRA. Same was true with the ROTH entry, now non Taxable, is should include ROTH, personal savings, reverse mortgage, etc.

I checked things out and you can enter your exact social security income, no need to round it to the nearest $100. The single person’s Benefit has to be a whole number so the married couple’s benefit will always be an even amount.

The chart itself covers $50,000 of additional income over the SS amount in $100 increments so it is only 500 lines long. For that reason you still have to enter your estimated taxable and not taxable incomes in even $100 increments which will be $200 increments for a married couple when the single values are doubled.

My last job before retirement was as a Software Engineer for a local Hedge Fund. But I have no training to give financial advice. I’m a computer geek who likes to see things instead of reading about them so I built this spreadsheet for myself. I helped a couple of friends with their planning and when they saw my chart they said I should sell it! I do not have the training to give financial advice so selling is out of the question. That is why I decided to give it away for nothing on this forum

User avatar
#Cruncher
Posts: 2418
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by #Cruncher » Mon Sep 14, 2015 9:59 pm

Nice graph, PapaGeek! The stair-steps show much better than words how additional ordinary income can affect Federal taxes of a Social Security recipient.

I downloaded the spreadsheet, changed the Single SS Benefit in cell A4 from $28,200 to $20,000, and confirmed your results against the example in the Wiki article, Taxation of Social Security benefits. In this case the big 46.25% "hump" occurs when non-SS ordinary income increases from $35,243 to $38,706 (in columns F and G below). This is the increment between Taxable Income first reaching the 25% bracket and the taxable portion of SS reaching its 85% maximum.

Code: Select all

Row       Col A                     Col B   Col C   Col D   Col E   Col F   Col G   Col H
---  ----------------------        ------  ------  ------  ------  ------  ------  ------
 29  Non-SS Ordinary Income        11,850  15,000  19,050  24,000  35,243  38,706  40,000
 31  Income for SS Calc            21,850  25,000  29,050  34,000  45,243  48,706  50,000
 32  SS Taxable Portion                 0       0   2,025   4,500  14,057  17,000  17,000
 33  Adjusted Gross Income         11,850  15,000  21,075  28,500  49,300  55,706  57,000
 34  Taxable Income                     0   3,150   9,225  16,650  37,450  43,856  45,150

Code: Select all

 41  Ordinary Income @ 25%              0       0       0       0       0   6,406   7,700
 42  Ordinary Income @ 15%              0       0       0   7,425  28,225  28,225  28,225
 43  Ordinary Income @ 10%              0   3,150   9,225   9,225   9,225   9,225   9,225

Code: Select all

 51  Tax on Ordinary Income @ 25%       0       0       0       0       0   1,602   1,925
 52  Tax on Ordinary Income @ 15%       0       0       0   1,114   4,234   4,234   4,234
 53  Tax on Ordinary Income @ 10%       0     315     923     923     923     923     923

Code: Select all

 56  Total Tax                          0     315     923   2,036   5,156   6,758   7,081
 57  Change in tax                            315     608   1,114   3,120   1,602     324
 58  Change in Ordinary Income              3,150   4,050   4,950  11,243   3,463   1,294
 59  Marginal Rate                         10.00%  15.00%  22.50%  27.75%  46.25%  25.00%
I prepared the above figures using a spreadsheet described in my post, Re: AGI, capital gains, and income interaction?.

User avatar
LadyGeek
Site Admin
Posts: 41035
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by LadyGeek » Tue Sep 15, 2015 6:01 pm

I added this thread to the wiki. See: Taxation of Social Security benefits (under "Examples")

FYI - This might make a good companion to the Retiree Portfolio Model spreadsheet.
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.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Tue Sep 15, 2015 8:04 pm

LadyGeek wrote:I added this thread to the wiki. See: Taxation of Social Security benefits (under "Examples")

FYI - This might make a good companion to the Retiree Portfolio Model spreadsheet.



Thank you for your support of this thread LadyGeek. I am honored that you have added a link to this thread on that wiki page.

User avatar
TimeRunner
Posts: 1161
Joined: Sat Dec 29, 2012 9:23 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by TimeRunner » Tue Sep 15, 2015 10:21 pm

PapaGeek wrote:
LadyGeek wrote:I added this thread to the wiki. See: Taxation of Social Security benefits (under "Examples")

FYI - This might make a good companion to the Retiree Portfolio Model spreadsheet.



Thank you for your support of this thread LadyGeek. I am honored that you have added a link to this thread on that wiki page.


Well deserved. Quality work - it shows.
One cannot enlighten the unconscious. | Endurance athletes are the Bogleheads of sports. | "I like people - I just don't want to be around 'em." - Russell Gordy

User avatar
#Cruncher
Posts: 2418
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by #Cruncher » Fri Sep 18, 2015 12:31 pm

in this post I wrote:I downloaded the spreadsheet, changed the Single SS Benefit in cell A4 from $28,200 to $20,000, and confirmed your results against the example in the Wiki article, Taxation of Social Security benefits.
PapaGeek, I failed to also check the Joint results. Doing so now I see that there is a conceptual bug in the spreadsheet. Your "Actual Rate" columns J and V assume that if the taxable SS income has reached its 85% maximum, then the marginal tax rate must be 25%. However, for a Joint return with $40,000 of SS income, the maximum is reached while still in the 15% tax bracket. Therefore the marginal rate falls from 27.75% (15% X 1.85) to 15%, and then later jumps back to 25% when income reaches the 25% bracket. However, because of the bug, your spreadsheet shows the marginal rate falling from 27.75% to only 25% when the taxable SS maximum is reached.

The correct marginal rates are shown in the table below which agrees with the Wiki:

Code: Select all

         Col A           Col B   Col C    Col D    Col E
                        Non-SS    Band   Taxable  Marginal
Row  Event              Income   Extent  Income     Rate
---  -----------------  -------  ------  -------  --------
 28  0 -> 50 Bend        12,000   7,400  (11,100)   0.00%
 29  Begin 10% Bracket   19,400   4,600        0   15.00%
 30  50 -> 85 Bend       24,000   6,243    6,900   18.50%
 31  Begin 15% Bracket   30,243  26,698   18,450   27.75%
 32  85 -> 0 Bend        56,941   7,059   67,841   15.00%
 33  Begin 25% Bracket   64,000           74,900   25.00%
I developed the above table with an Excel sheet that uses a different method to determine the points where marginal tax rates change as non-SS income increases. [***] If you wish to use this spreadsheet as a check on yours, follow these steps:
  • Select All, copy, and paste five times from below at cells A1, B1, C1, D20, & E20 of an empty Excel sheet.
  • Enter filing status ("Single" or "Joint") in cell B9.
  • Enter Social Security income in cell B10.
  • Enter tax exempt interest, if any, in cell B11. This is added to taxable income to determine the threshholds where SS becomes taxable.
  • Enter "Std" in cells B12 and B13 to use the standard deduction and exemption, or enter alternate amounts.
Select All, copy, and paste at cell A1

Code: Select all

Filing Status
"Std" Deduction
"Std" Exemptions
Social Security 50% Threshhold
Social Security 85% Threshhold
0.1
0.15
0.25
Single or Joint Return
Social Security Income
Tax Exempt Interest
Deductions
Exemption
Deduction plus Exemption
=A4
=A5
="Begin "&TEXT(A6,"00%")&" Bracket Taxable Income"
="Begin "&TEXT(A7,"00%")&" Bracket Taxable Income"
="Begin "&TEXT(A8,"00%")&" Bracket Taxable Income"

0 -> 50 Bend
50 -> 85 Bend
85 -> 0 Bend
="Begin "&TEXT(A6,"00%")&" Bracket"
="Begin "&TEXT(A7,"00%")&" Bracket"
="Begin "&TEXT(A8,"00%")&" Bracket"

=INDEX(A$21:A$26,MATCH($B28,$B$21:$B$26,0),1)
=INDEX(A$21:A$26,MATCH($B29,$B$21:$B$26,0),1)
=INDEX(A$21:A$26,MATCH($B30,$B$21:$B$26,0),1)
=INDEX(A$21:A$26,MATCH($B31,$B$21:$B$26,0),1)
=INDEX(A$21:A$26,MATCH($B32,$B$21:$B$26,0),1)
=INDEX(A$21:A$26,MATCH($B33,$B$21:$B$26,0),1)
Select All, copy, and paste at cell B1

Code: Select all

Single
7850
4000
25000
34000
0
9225
37450
Joint
40000
0
Std
Std
=IF(ISNUMBER(B12),B12,HLOOKUP(B$9,$B$1:$C$8,2,FALSE))+IF(ISNUMBER(B13),B13,HLOOKUP(B$9,$B$1:$C$8,3,FALSE))
=HLOOKUP(B$9,$B$1:$C$8,4,FALSE)
=HLOOKUP(B$9,$B$1:$C$8,5,FALSE)
=HLOOKUP(B$9,$B$1:$C$8,6,FALSE)
=HLOOKUP(B$9,$B$1:$C$8,7,FALSE)
=HLOOKUP(B$9,$B$1:$C$8,8,FALSE)
Non-SS Inc
=B15-B$10/2-B$11
=B16-B$10/2-B$11
=B22+(85%*B10-50%*(B22-B21))/85%
=IF(C$23<=C24,C24-85%*B$10,IF(C$22<=C24,(C24-50%*(B$22-B$21)+85%*B$22)/1.85,IF(C$21<=C24,(C24+50%*B$21)/1.5,C24)))
=IF(C$23<=C25,C25-85%*B$10,IF(C$22<=C25,(C25-50%*(B$22-B$21)+85%*B$22)/1.85,IF(C$21<=C25,(C25+50%*B$21)/1.5,C25)))
=IF(C$23<=C26,C26-85%*B$10,IF(C$22<=C26,(C26-50%*(B$22-B$21)+85%*B$22)/1.85,IF(C$21<=C26,(C26+50%*B$21)/1.5,C26)))
Non-SS Inc
=SMALL(B$21:B$26,ROW(A28)-ROW(A$28)+1)
=SMALL(B$21:B$26,ROW(A29)-ROW(A$28)+1)
=SMALL(B$21:B$26,ROW(A30)-ROW(A$28)+1)
=SMALL(B$21:B$26,ROW(A31)-ROW(A$28)+1)
=SMALL(B$21:B$26,ROW(A32)-ROW(A$28)+1)
=SMALL(B$21:B$26,ROW(A33)-ROW(A$28)+1)
Select All, copy, and paste at cell C1

Code: Select all

Joint
15100
8000
32000
44000
0
18450
74900











AGI
=B21
=B22+MIN(0.85*B10,0.5*(B22-B21))
=B23+85%*B10
=D24+B$14
=D25+B$14
=D26+B$14
Extent
=B29-B28
=B30-B29
=B31-B30
=B32-B31
=B33-B32
Select All, copy, and paste at cell D20

Code: Select all

Taxable
=C21-B$14
=C22-B$14
=C23-B$14
=B17
=B18
=B19
Taxable
=INDEX(D$21:D$26,MATCH($B28,$B$21:$B$26,0),1)
=INDEX(D$21:D$26,MATCH($B29,$B$21:$B$26,0),1)
=INDEX(D$21:D$26,MATCH($B30,$B$21:$B$26,0),1)
=INDEX(D$21:D$26,MATCH($B31,$B$21:$B$26,0),1)
=INDEX(D$21:D$26,MATCH($B32,$B$21:$B$26,0),1)
=INDEX(D$21:D$26,MATCH($B33,$B$21:$B$26,0),1)
Select All, copy, and paste at cell E20

Code: Select all

Marginal
=1.5*IF(D21>=B$19,A$8,IF(D21>=B$18,A$7,IF(D21>=B$17,A$6,0)))
=1.85*IF(D22>=B$19,A$8,IF(D22>=B$18,A$7,IF(D22>=B$17,A$6,0)))
=1*IF(D23>=B$19,A$8,IF(D23>=B$18,A$7,IF(D23>=B$17,A$6,0)))
=A6*IF(B24>=B$23,1,IF(B24>=B$22,1.85,IF(B24>=B$21,1.5,1)))
=A7*IF(B25>=B$23,1,IF(B25>=B$22,1.85,IF(B25>=B$21,1.5,1)))
=A8*IF(B26>=B$23,1,IF(B26>=B$22,1.85,IF(B26>=B$21,1.5,1)))
Marginal
=INDEX(E$21:E$26,MATCH($B28,$B$21:$B$26,0),1)
=INDEX(E$21:E$26,MATCH($B29,$B$21:$B$26,0),1)
=INDEX(E$21:E$26,MATCH($B30,$B$21:$B$26,0),1)
=INDEX(E$21:E$26,MATCH($B31,$B$21:$B$26,0),1)
=INDEX(E$21:E$26,MATCH($B32,$B$21:$B$26,0),1)
=INDEX(E$21:E$26,MATCH($B33,$B$21:$B$26,0),1)
*** Edit 9/22/2015 to add this comment: As mentioned in my post below, this spreadsheet fails to include two possible "bend points" (50% to 0% and 0% to 85%) that may occur when the SS benefit is small. (The example in this post is OK.) When and if I'm able to fix the spreadsheet, I'll edit this post again with the corrected Excel cells to paste.
Last edited by #Cruncher on Tue Sep 22, 2015 8:48 am, edited 1 time in total.

User avatar
LadyGeek
Site Admin
Posts: 41035
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by LadyGeek » Fri Sep 18, 2015 9:56 pm

Can the Social Security experts please check a reference?

Image
The link listed in the above image (benefits calculator) shows http://www.ssa.gov/pubs/10070.html , but is instead redirected to Your Retirement Benefit: How It Is Figured - EN-05-10070.pdf

The important part is the publication number "EN-05-10070". Now, go to the Social Security website: Publications

Scroll down to find "Your Retirement Benefit: How It Is Figured". The right side of the section contains a PDF button, which is the publication link. Or so I thought. Click on PDF and you are presented with choices:

Each of these links is a different version of Publication EN-05-10070. The publication date follows the birth year, going back one year for each link.

If you were not born in 1953 (current publication date of 2015), is the calculator incorrect as shown? I assume there's a reason the Social Security Administration is maintaining these publication links.
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.

User avatar
Watty
Posts: 11559
Joined: Wed Oct 10, 2007 3:55 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Watty » Fri Sep 18, 2015 11:04 pm

That is a great spreadsheet. :beer

It seems to work in LibreOffice if anyone wants to use it but does not have Excel.

Just one suggestion, you might want to put a revision number on it so that if people have questions about it they can tell if they are using the latest revision.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 19, 2015 6:14 am

Watty wrote:That is a great spreadsheet. :beer

It seems to work in LibreOffice if anyone wants to use it but does not have Excel.

Just one suggestion, you might want to put a revision number on it so that if people have questions about it they can tell if they are using the latest revision.


Great suggestion, I'll also post a list of the versions.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 19, 2015 8:55 am

#Cruncher wrote:
in this post I wrote:I downloaded the spreadsheet, changed the Single SS Benefit in cell A4 from $28,200 to $20,000, and confirmed your results against the example in the Wiki article, Taxation of Social Security benefits.
PapaGeek, I failed to also check the Joint results. Doing so now I see that there is a conceptual bug in the spreadsheet. Your "Actual Rate" columns J and V assume that if the taxable SS income has reached its 85% maximum, then the marginal tax rate must be 25%. However, for a Joint return with $40,000 of SS income, the maximum is reached while still in the 15% tax bracket. Therefore the marginal rate falls from 27.75% (15% X 1.85) to 15%, and then later jumps back to 25% when income reaches the 25% bracket. However, because of the bug, your spreadsheet shows the marginal rate falling from 27.75% to only 25% when the taxable SS maximum is reached.



Thanks for catching that cruncher! I appreciate all the work you are doing. What I’m attempting to do here is to help people avoid the 46.25% effective bracket but I want the entire process to be completely accurate. I’m on a camping trip right now, but I will get to work on that correction when we get home on Monday.

It looks like you are another MathGeek like myself who has no problems looking at a column of numbers. This whole spreadsheet is based on the old cliché that a picture is worth a thousand words, and for most of my non-mathematical friends, seeing the graph makes this issue easier to “see”, comprehend. They can SEE The Hump and SEE how close their gross income tick mark is to The Hump.

Just two different ways to show people the same data.

User avatar
celia
Posts: 7090
Joined: Sun Mar 09, 2008 6:32 am
Location: SoCal

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by celia » Sat Sep 19, 2015 12:21 pm

Although this looks nice, I don't understand it. That may be because I have done something similar in spreadsheets for our own situation. In particular, I don't understand the yellow cells in columns G through J (although I is obviously the tax rate) as the columns are not labeled. How would I know to put 923 into cell H5? What is that? If it is the tax, I would think that would be a calculated output instead of an input.

The "other taxable income" is also nebulous. I can have dividends and LT capital gains that are tax-free to a certain point, then suddenly they can become taxable when SS kicks in. And boy, does the tax jump up, as you've mentioned. Not only is the SS taxable but it makes non-taxables become taxable. If your other taxable income is high enough, I find that it's easier to not even think of a 46.5% marginal tax rate, but just plan on 85% of your SS being taxable and add that 85% up with all your other income. THEN, start adding in your "tax-free" amounts to see what happens.
A dollar in Roth is worth more than a dollar in a taxable account. A dollar in taxable is worth more than a dollar in a tax-deferred account.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 19, 2015 3:14 pm

celia wrote:Although this looks nice, I don't understand it. That may be because I have done something similar in spreadsheets for our own situation. In particular, I don't understand the yellow cells in columns G through J (although I is obviously the tax rate) as the columns are not labeled. How would I know to put 923 into cell H5? What is that? If it is the tax, I would think that would be a calculated output instead of an input.

The "other taxable income" is also nebulous. I can have dividends and LT capital gains that are tax-free to a certain point, then suddenly they can become taxable when SS kicks in. And boy, does the tax jump up, as you've mentioned. Not only is the SS taxable but it makes non-taxables become taxable. If your other taxable income is high enough, I find that it's easier to not even think of a 46.5% marginal tax rate, but just plan on 85% of your SS being taxable and add that 85% up with all your other income. THEN, start adding in your "tax-free" amounts to see what happens.



You are asking about columns G thru J, that is an old version. I’ve added column C so you can enter your non-taxable income. Also, column J is now calculated for you.

They are used to calculate your federal tax due based on your taxable income. The first two are your standard deduction and personal exemption for single and married. The next two show the first 3 tax rates and the dollar amount when that tax rate ends. I then calculate the next column which is how much tax you by the end of that bracket.

This is confusing because I needed the zeros above the data you enter so I could not properly label the data. I’m going to create another tab used to initialize the data where I can put better labels on everything.

The following chart shows how your SS benefit is totally dependent on two things: your income and your choice on when to retire:
Retire Age.. . . . 62 . . .. . . . 66 . . .. . . . 70
Income . . ... . . . 75% . . .. . . . 100% . . .. . . . 132%
$10,000 . ..... . $6,712 . ..... . $8,949 . .... . $11,813
$20,000 . ..... . $9,112 . .... . $12,149 . .... . $16,037
$30,000 . .... . $11,512 . .... . $15,349 . .... . $20,261
$40,000 . .... . $13,912 . .... . $18,549 . .... . $24,485
$50,000 . .... . $16,312 . .... . $21,749 . .... . $28,709
$60,000 . .... . $18,681 . .... . $24,908 . .... . $32,879
$70,000 . .... . $19,806 . .... . $26,408 . .... . $34,859
$80,000 . .... . $20,931 . .... . $27,908 . .... . $36,839
$90,000 . .... . $22,056 . .... . $29,408 . .... . $38,819
$100,000 . .. . $23,181 . .... . $30,908 . .... . $40,799
$110,000 . .. . $24,306 . .... . $32,408 . .... . $42,779

Note that a person making $40,000 who retires at 70 will get $24,485 while a person making $110,000 who retires at 62 only gets $24,306
Last edited by PapaGeek on Sun Sep 20, 2015 4:44 pm, edited 1 time in total.

User avatar
celia
Posts: 7090
Joined: Sun Mar 09, 2008 6:32 am
Location: SoCal

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by celia » Sat Sep 19, 2015 4:59 pm

PapaGeek wrote:Your comment “just plan on 85% of your SS being taxable” is only true when your pre-retirement income is well over the $90,000 mark, six figure incomes. Sorry, but this spreadsheet is designed for that who can avoid The Hump and can’t afford expensive tax accountants.

Not true. According to the SS website, page 14 of this document (http://www.ssa.gov/pubs/EN-05-10035.pdf) says incomes over $34,000 for singles and $44,000 for marrieds may have UP TO 85% of the SS be taxed.

I put a pension income of $45,000 into my 2014 tax software for a married couple. Then I added $1,000 of SS and it told me 85% of it would be taxed. However, I wanted to see if that still held if you received more SS. When I add $35,000 of SS, it said $21,725 (62%) of it would be taxed. And when I added $35,000 for each person, that $70,000 had $36,600 (52%) taxed.

Well, I learned something new today. Playing around with the software gives me the 85% starting to decrease between 16k and 17k of SS for this couple with a 45k pension.
A dollar in Roth is worth more than a dollar in a taxable account. A dollar in taxable is worth more than a dollar in a tax-deferred account.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sat Sep 19, 2015 7:37 pm

celia,

The problem, not that it is one for you, is that you are getting a large pension. But this is a good example for those who have not retired yet. Do your planning early enough. You might be able to take a portion of your pension as a lump sum so that you will have more control over taxable and non-taxable during retirement.

Better to take a lump sum at the 25% or 28% level than get your pension at the 46.25% level.

User avatar
celia
Posts: 7090
Joined: Sun Mar 09, 2008 6:32 am
Location: SoCal

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by celia » Sun Sep 20, 2015 1:38 am

Hey PapaGeek, That is not our pension situation at all. (It could also be wages or Roth conversions or . . .) It is the amount over which SS becomes 85% taxable for marrieds.

Until reading this thread, I had assumed that once you hit the 85% of SS, that you stayed at that rate. I did not realize that it goes down at some point. Maybe that is what you are referring to as the "hump". Can you identify what makes it go down, in general?
A dollar in Roth is worth more than a dollar in a taxable account. A dollar in taxable is worth more than a dollar in a tax-deferred account.

Carl53
Posts: 1430
Joined: Sun Mar 07, 2010 8:26 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Carl53 » Sun Sep 20, 2015 6:36 am

celia wrote:Hey PapaGeek, That is not our pension situation at all. (It could also be wages or Roth conversions or . . .) It is the amount over which SS becomes 85% taxable for marrieds.

Until reading this thread, I had assumed that once you hit the 85% of SS, that you stayed at that rate. I did not realize that it goes down at some point. Maybe that is what you are referring to as the "hump". Can you identify what makes it go down, in general?


The equation for calculating the amount of taxable SS is somewhat complicated. You can study PPG's spreadsheet but there are three competing methods of calculation of the amounts of taxable SS. Once your SS exceeds 1619 for the couple at 45000 other taxable income, it is no longer a straight 85% but a increasing portion is included at only 50%. If you get more taxable nonSS income you'll see the rate on the SS inclusion climb back to 85%. For instance with your 70k SS case, if the nonSS income climbs back to say 72000, you are back to 85% taxable SS.

Perhaps the 4 graphs in this old thread will make it more evident what is going on.
viewtopic.php?f=1&t=70229

The Wizard
Posts: 11000
Joined: Tue Mar 23, 2010 1:45 pm
Location: Reading, MA

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by The Wizard » Sun Sep 20, 2015 8:51 am

celia wrote:Hey PapaGeek, That is not our pension situation at all. (It could also be wages or Roth conversions or . . .) It is the amount over which SS becomes 85% taxable for marrieds.

Until reading this thread, I had assumed that once you hit the 85% of SS, that you stayed at that rate. I did not realize that it goes down at some point. Maybe that is what you are referring to as the "hump". Can you identify what makes it go down, in general?

Once ALL of your SS income is 85% taxable, you are "over the hump" and back to a marginal federal tax rate that equals your tax bracket, possibly 25% but more likely 28% or higher.

The Hump is caused by the transition zone where additional non SS income causes more of your SS income to be taxed at 85% instead of 50%...
Attempted new signature...

cherijoh
Posts: 4046
Joined: Tue Feb 20, 2007 4:49 pm
Location: Charlotte NC

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by cherijoh » Sun Sep 20, 2015 9:16 am

[quote="PapaGeek"]The spreadsheet is available on google drive for download:
https://drive.google.com/file/d/0B-beVhyq5Az-Zl9jZXY2V2NSeUU/view?usp=sharing
But before we get started, according to the old cliché, a picture is worth a thousand words:

Image

Maybe I'm misreading this, but how does a single person have a lower marginal tax rate than a married couple for a given taxable income amount? For example at $38K, the red line is at 20% while the blue line is at 0%.

User avatar
#Cruncher
Posts: 2418
Joined: Fri May 14, 2010 2:33 am
Location: New York City
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by #Cruncher » Sun Sep 20, 2015 11:08 am

PapaGeek, while examining Celia's example of a small $1,000 SS benefit on a joint return, I found another logical error in your spreadsheet. You aren't properly handling the maximum SS that is taxable in the "Taxable SS" columns (E & Q). For example when I enter $500 as the SS Single benefit (making the Joint benefit $1,000) your spreadsheet shows:

Code: Select all

                                Single       Joint
                                ------       -----
SS Benefit                         500       1,000
50% Threshhold                  25,000      32,000
Taxability Basis                29,150      37,900
Taxable SS                       2,075       2,950
Taxability Basis (next row)     29,250      38,000
Taxable SS                         425         850
Correct Taxable SS (both rows)     250         500
There are two aspects to this bug. First you don't reflect the maximum kicking in at the right time, and second, when you do show the maximum, it is based on 85% instead of 50%. (I also made this second error in my spreadsheet in this post.)

Changes in the marginal tax rate can be even more complicated than I thought. In additions to "bend points" when the SS taxable portion goes from 0% to 50%, 50% to 85%, and 85% to 0%; in some cases there may be two more bend points when it goes from 50% to 0% and from 0% to 85%.

celia in this post wrote:Well, I learned something new today. Playing around with the software gives me the 85% starting to decrease between 16k and 17k of SS for this couple with a 45k pension.
celia in this post wrote:I had assumed that once you hit the 85% of SS, that you stayed at that rate. I did not realize that it goes down at some point. ... Can you identify what makes it go down, in general?
Celia, PapaGeek's spreadsheet shows the change in marginal tax rates as non-SS income increases while SS income remains constant. You are looking at it from the opposite perspective as SS income increases while non-SS income remains constant. The effects are quite different. Under the first case, as non-SS income increases, the taxable portion of the fixed SS income will indeed reach 85% and remain there. But under the second case, as SS income increases, it will reach a point where only 42.5% of the increase will be taxable. This arises because of the underlined portion of the following:
the Wiki in Taxation of Social Security benefits wrote:The relevant income for Social Security taxation includes all items which are normally part of your adjusted gross income, plus tax-exempt interest income, plus 50% of your Social Security benefits. (underline added)
The effect of this can be seen in the table below where SS income increases from $0 to $70,000 while non-SS income remains constant at $45,000:

Code: Select all

Social Security Income           0     1,000    16,118    35,000    70,000
Non-SS Income               45,000    45,000    45,000    45,000    45,000
Income to Calc SS Taxable   45,000    45,500    53,059    62,500    80,000
--------------------------------------------------------------------------
Taxable SS in 50% Band           0       500     6,000     6,000     6,000
Taxable SS in 85% Band           0       350     7,700    15,725    30,600
                             -----     -----    ------    ------    ------
Total SS Taxable Portion         0       850    13,700    21,725    36,600
                             -----     -----    ------    ------    ------ 
Increment in SS Income                 1,000    15,118    18,882    35,000
Increment in SS Taxable                  850    12,850     8,025    14,875
                                       -----    ------    ------    ------ 
SS Taxable Increment Pct               85.0%     85.0%     42.5%     42.5%
Consider the last two columns for example. SS income increases $35,000 to $70,000; but the relevant income used to calculate the taxable portion of SS (on the 3rd row) increases only $17,500 from $62,500 to $80,000. Therefore the taxable portion of SS increases only 85% of this or $14,875 (85% X 17,500). Therefore the $14,875 is only 42.5% of the $35,000 increase in SS income. No matter how much more SS income increases, only 42.5% of the increase will be taxed.

Carl53 in this post wrote:Once your SS exceeds 1619 for the couple at 45000 other taxable income, it is no longer a straight 85% but a increasing portion is included at only 50%.
I think you made a typo, Carl. The point where the taxable portion of SS no longer is 85% occurs at $16,118; not $1,619.

Code: Select all

0.85  * SSI = 0.50 * (44000 - 32000) + 0.85  * (SSI / 2 + 45000 - 44000)
0.85  * SSI = 6000                   + 0.425 * SSI      +   850
0.425 * SSI = 6850
        SSI = 6850 / 0.425
        SSI = 16118

Carl53
Posts: 1430
Joined: Sun Mar 07, 2010 8:26 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by Carl53 » Sun Sep 20, 2015 1:38 pm

#Cruncher wrote:
Carl53 in this post wrote:Once your SS exceeds 1619 for the couple at 45000 other taxable income, it is no longer a straight 85% but a increasing portion is included at only 50%.
I think you made a typo, Carl. The point where the taxable portion of SS no longer is 85% occurs at $16,118; not $1,619.

Code: Select all

0.85  * SSI = 0.50 * (44000 - 32000) + 0.85  * (SSI / 2 + 45000 - 44000)
0.85  * SSI = 6000                   + 0.425 * SSI      +   850
0.425 * SSI = 6850
        SSI = 6850 / 0.425
        SSI = 16118


Haste makes waste, or don't post as you are hurrying to do it prior to heading out the door and do not check your work. thanks

User avatar
LadyGeek
Site Admin
Posts: 41035
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by LadyGeek » Sun Sep 20, 2015 1:59 pm

Before this gets buried, can someone please answer my previous question? Since no one picked up on it, I assume that I'm wrong on how the benefits are figured. However, I'd like confirmation - it's an important set of inputs that need to be correct.

LadyGeek wrote:Can the Social Security experts please check a reference?

Image
The link listed in the above image (benefits calculator) shows http://www.ssa.gov/pubs/10070.html , but is instead redirected to Your Retirement Benefit: How It Is Figured - EN-05-10070.pdf

The important part is the publication number "EN-05-10070". Now, go to the Social Security website: Publications

Scroll down to find "Your Retirement Benefit: How It Is Figured". The right side of the section contains a PDF button, which is the publication link. Or so I thought. Click on PDF and you are presented with choices:

Each of these links is a different version of Publication EN-05-10070. The publication date follows the birth year, going back one year for each link.

If you were not born in 1953 (current publication date of 2015), is the calculator incorrect as shown? I assume there's a reason the Social Security Administration is maintaining these publication links.
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.

User avatar
ObliviousInvestor
Posts: 3198
Joined: Tue Mar 17, 2009 9:32 am
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by ObliviousInvestor » Sun Sep 20, 2015 2:44 pm

LG,

I haven't check PapaGeek's spreadsheet. (Lots going on with family stuff right now, so I haven't been able to devote much time to the forum for the last couple of weeks.) But, perhaps this information will be helpful.

Social Security benefits are taxed the same way, regardless of the year in which you are born.

Your actual Social Security benefit is calculated differently, depending on the year in which you're born (which is why they have different publications for each year).

Specifically, the "bend points" change from year to year. So a person turning 62 this year will have a slightly different benefit than a person who turned 62 last year, even if the two people have identical earnings histories.

Another reason that year of birth matters is that all of your earnings prior to age 60 are indexed (based on the "national average wage index") to what they would have been if you'd earned them in the year you turned age 60. So, if you and I turn age 60 in different years, our earnings will be indexed differently.
Mike Piper, author/blogger

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sun Sep 20, 2015 4:44 pm

ObliviousInvestor wrote:LG,

I haven't check PapaGeek's spreadsheet. (Lots going on with family stuff right now, so I haven't been able to devote much time to the forum for the last couple of weeks.) But, perhaps this information will be helpful.

Social Security benefits are taxed the same way, regardless of the year in which you are born.

Your actual Social Security benefit is calculated differently, depending on the year in which you're born (which is why they have different publications for each year).

Specifically, the "bend points" change from year to year. So a person turning 62 this year will have a slightly different benefit than a person who turned 62 last year, even if the two people have identical earnings histories.

Another reason that year of birth matters is that all of your earnings prior to age 60 are indexed (based on the "national average wage index") to what they would have been if you'd earned them in the year you turned age 60. So, if you and I turn age 60 in different years, our earnings will be indexed differently.


That is why I recommend that everyone get an accurate number from Social Security. If you are already retired and looking for ways to improve your situation, you know your numbers. I can supply a rough ballpark estimator. The planning process is close when your SS numbers are within a few $100 or so, but again, the best is to get YOUR data from Social Security.

The main problem deals more with when you retire. To quote my previous post, “a person making $40,000 who retires at 70 will get $24,485 while a person making $110,000 who retires at 62 only gets $24,306”.

User avatar
LadyGeek
Site Admin
Posts: 41035
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by LadyGeek » Sun Sep 20, 2015 6:43 pm

ObliviousInvestor wrote:...Your actual Social Security benefit is calculated differently, depending on the year in which you're born (which is why they have different publications for each year)...

Thank you. That answers the crux of my question.

PapaGeek's benefit estimator (latest download with an "Estimate" tab) has the 2015 bend point numbers in cells E4 and F4 (Step 5 of the Social Security benefits publication. This should be changed to reflect the user's actual situation.

I propose adding a table in the Estimate tab that tells the users which bend points to use. Remember that the purpose of the spreadsheet is to educate. It's the process as well as the accuracy.

Below is the same content, but ready to copy-n-paste into a spreadsheet.

Code: Select all

Year Pub Link Low High
1953 http://www.socialsecurity.gov/pubs/EN-05-10070-1953.pdf 826 4980
1952 http://www.socialsecurity.gov/pubs/EN-05-10070-1952.pdf 816 4917
1951 http://www.socialsecurity.gov/pubs/EN-05-10070-1951.pdf 791 4768
1950 http://www.socialsecurity.gov/pubs/EN-05-10070-1950.pdf 767 4624
1949 http://www.socialsecurity.gov/pubs/EN-05-10070-1949.pdf 749 4517
1948 http://www.socialsecurity.gov/pubs/EN-05-10070-1948.pdf 761 4586
1947 http://www.socialsecurity.gov/pubs/EN-05-10070-1947.pdf 744 4483
1946 http://www.socialsecurity.gov/pubs/EN-05-10070-1946.pdf 711 4288
1945 http://www.socialsecurity.gov/pubs/EN-05-10070-1945.pdf 680 4100
1944 http://www.socialsecurity.gov/pubs/EN-05-10070-1944.pdf 656 3955
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.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Sun Sep 20, 2015 7:40 pm

LadyGeek wrote:
ObliviousInvestor wrote:...Your actual Social Security benefit is calculated differently, depending on the year in which you're born (which is why they have different publications for each year)...

Thank you. That answers the crux of my question.

PapaGeek's benefit estimator (latest download with an "Estimate" tab) has the 2015 bend point numbers in cells E4 and F4 (Step 5 of the Social Security benefits publication. This should be changed to reflect the user's actual situation.


The key word in what ObliviousInvestor posted was the word "BENEFIT". Yes it is true that there are minor differences in benefits, but they are minor and that is also why you should check with SS to get your real benefit. If you run the spreadsheet with a $23,500 benefits and it should be $23,300 things do not change that much. Again, I have said to get your real benefit, not what the rough estimator gives you.

The number is E4 and F4 were set in stone by the 1983 and 1993 amendments to the Social Security Act. The are the taxation points! The fact that those numbers have not change in 32 years is what is making the situation worse as the years go by. There is no legislation under consideration to make those numbers COLA or inflations adjusted.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Mon Sep 21, 2015 9:14 am

Version 4 of spreadsheet has been uploaded:

There is now a separate setup page
You only have to enter the IRS data in one place
The new page mimics the IRS tables

The page also contains the Exemption and Standard Deduction amounts with check boxes for you and your spouse over age 65. It then calculates the proper Deductions and passes them on to the various spreadsheets.

The page also contains a section for Revision history and what is coming.

I also added a warning on the Estimate tab to remind users that this is only a rough ballpark estimate of your Social Security benefit with advice to contact SSA to get the real numbers.

User avatar
House Blend
Posts: 4237
Joined: Fri May 04, 2007 1:02 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by House Blend » Mon Sep 21, 2015 3:29 pm

^Any warnings or accomodations for qualified dividends and/or long term capital gains? If your AGI includes either of these kinds of income, this alters the tax computation (the tax tables no longer apply), and can change the marginal rates you pay on ordinary income both inside and outside the humps.

For example, the 46.25% rate can become 55.5% under the right conditions.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Tue Sep 22, 2015 6:11 am

House Blend wrote:^Any warnings or accomodations for qualified dividends and/or long term capital gains? If your AGI includes either of these kinds of income, this alters the tax computation (the tax tables no longer apply), and can change the marginal rates you pay on ordinary income both inside and outside the humps.

For example, the 46.25% rate can become 55.5% under the right conditions.


That is a great catch House Blend. The tax law is thousands of pages long and it would be impossible to add every possible rule and rate that it contains into a single spreadsheet that handles it all. The spreadsheet is not really designed to give you totally accurate amounts, the taxable and non-taxable incomes have to be rounded to the nearest $100 mark to make the tick marks appear. It is just a representation of the affect of The Hump for most people.

I am publishing a Wiki page on Bogle that should be ready within a few days. One of the paragraphs on the Wiki pages says:

So, where do you find alternative sources for non-taxable income? Once you are in retirement your income will consist of Social Security, taxable pensions, taxable annuities, and other taxable sources. You might also receive income from non-taxable sources like the sale of personal items, moving to a smaller home, taking out a reverse mortgage, withdrawals from a ROTH IRA, and other sources. There are also hybrid sources like withdrawals from various accounts where the interest is taxed annually but the principle is not taxed when you withdraw it.

I believe the last line covers your examples, but they seem to be Hybrids of the Hybrid income sources.

User avatar
House Blend
Posts: 4237
Joined: Fri May 04, 2007 1:02 pm

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by House Blend » Tue Sep 22, 2015 10:08 am

^I'm not sure that language is plain enough to make it clear that any retiree with a taxable investment account containing stocks (or funds that include stocks), even if never sold, may have a marginal tax rate on ordinary income different from any of those in your spreadsheet.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Wed Sep 23, 2015 9:57 am

House Blend wrote:^I'm not sure that language is plain enough to make it clear that any retiree with a taxable investment account containing stocks (or funds that include stocks), even if never sold, may have a marginal tax rate on ordinary income different from any of those in your spreadsheet.


The spreadsheet does not cover hybrid accounts. It handles taxable income like IRA, Pension, Annuity, etc. and non-taxable income like ROTH, reverse mortgages, sale of personal items, etc.

It only has the two categories of taxable and non-taxable. There are many different forms of hybrid income sources. You will have to calculate for yourself how much of the income from those sources will be taxable and non-taxable.

The spreadsheet is for general use. It does not contain special columns for hybrid income that is taxed at different rates than ordinary income.

You are welcome to download the spreadsheet and make the modifications needed for your own personal situation.

The Wiki page originally stated: “There are also hybrid sources like withdrawals from various accounts where the interest is taxed annually but the principle is not taxed when you withdraw it.”

I made it a separate paragraph which now says:

There are also hybrid sources of income like withdrawals from various accounts where the interest is taxed annually but the principle is not taxed when you withdraw it. Since these income sources can follow a number of different taxation policies which could even be different for each State, you will have to do your own individual work to to convert your hybrid income sources into the taxable and non-taxable amounts handled by this general use spreadsheet. You are welcome to download the spreadsheet and make any modifications needed for your own personal situation.

Thanks for pointing out the need for this clarification House Blend.

User avatar
PapaGeek
Posts: 263
Joined: Sun Nov 02, 2014 9:00 am
Location: North East, Maryland
Contact:

Re: Spreadsheet to show how the taxable SS benefits will affect you

Post by PapaGeek » Wed Sep 23, 2015 10:00 am

I just uploaded version 5 of the spreadsheet which adds pop up error messages on all of the spreadsheet tabs and adds a new tab called Singles where you can do the calculations for non-married couples or “look at” the differences between your retirement at different ages.

The Wiki page is now ready for Prime Time and I am waiting on a final review by the ops before taking it live.

Once it is live I will add a note about it in the first post of this thread and use the thread as a feedback source for suggestions for fixes and improvements.

User avatar
LadyGeek
Site Admin
Posts: 41035
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Spreadsheet to show how the taxable of SS benefits will affect you

Post by LadyGeek » Wed Sep 23, 2015 6:55 pm

^^^ The wiki page is now "live", see: Social Security tax impact calculator

PapaGeek has recently become a wiki editor and developed this wiki page. The "review by the ops" was the more experienced wiki editors (including me) to help with the formatting and make a few tweaks.

This is a collaborative effort. All wiki editors are welcome to update this page. I've taken a stab to put the download info and a link to this thread at the top.
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.

Post Reply