Calculating tax on SS benefits

[dixonge]

I finally found my 'savings' spreadsheet where I attempt to project annual income from pension and SS, including calculating SS taxes, etc. I adjusted some numbers that have changed, but I am unsure if I am calculating the SS taxes correctly. Google has been unhelpful

So here is what I have, would appreciate if anyone has an excel formula for this or corrections to my info:

Maximum tax bracket is 12% for forseeable future

SS income * .5 then add pension income then subtract 44,000 then take all that and multiply by .85 and the result should be the taxable portion of SS. Does that sound about right?

 

[pb4uski]

See https://www.irs.gov/pub/irs-pdf/p915.pdf#page=16

 

[dixonge]

See https://www.irs.gov/pub/irs-pdf/p915.pdf#page=16

well hopefully that will help once I start doing tax return prep and have all those forms available, but I was hoping for an existing formula...

 

[pb4uski]

Calculator on this page: https://www.fool.com/retirement/2016/06/06/social-security-tax-calculator-are-your-retirement.aspx

 

[niven]

well hopefully that will help once I start doing tax return prep and have all those forms available, but I was hoping for an existing formula...

It's probably more complicated than should be put into a single formula, but the calculations for married filing jointly are:

1. Calculate your provisional income (AGI + tax-free interest + 1/2 Social Security)
2. Calculate 50% of your provisional income over $32,000 and not exceeding $44,000. Then you need to compare this amount (which would max out at $6,000) to 50% of your Social Security and use the smaller amount.
3. Calculate 85% of your provisional income over $44,000
4. Add the amounts from step 2 and 3. Then you need to compare this total to 85% of your Social Security. The smaller amount is the final taxable amount of your Social Security.

 

[dixonge]

Calculator on this page: https://www.fool.com/retirement/2016/06/06/social-security-tax-calculator-are-your-retirement.aspx

So I was close but under-estimated my taxable SS amount. Looks like I have more figgerin' to do...

It's probably more complicated than should be put into a single formula, but the calculations for married filing jointly are:

1. Calculate your provisional income (AGI + tax-free interest + 1/2 Social Security)
2. Calculate 50% of your provisional income over $32,000 and not exceeding $44,000. Then you need to compare this amount (which would max out at $6,000) to 50% of your Social Security and use the smaller amount.
3. Calculate 85% of your provisional income over $44,000
4. Add the amounts from step 2 and 3. Then you need to compare this total to 85% of your Social Security. The smaller amount is the final taxable amount of your Social Security.

Well ok then - I think I can come up with a formula to handle that. Thanks!

 

[dixonge]

This calculator was helpful, in that I was able to figure out how to plug the same numbers into a spreadsheet, sort of:

https://www.calcxml.com/calculators/how-much-of-my-social-security-benefit-may-be-taxed

 

[N02L84ER]

This is the formula that works for us. We also have a 12% marginal tax bracket.


MIN(A5*2*85%,0.5*MAX(A7-32000,0)+0.35*MAX(A7-44000,0))


A5 is 50% of SS benefit
A7 is 50% of SS benefit plus All Other Taxable Income

 

[corn18]

See https://www.irs.gov/pub/irs-pdf/p915.pdf#page=16

Once you plug this into excel as a worksheet, all your SS tax worries go away. That's what I did and now estimating taxes with SS benefits is easy peasy.

 

[niven]

This is the formula that works for us. We also have a 12% marginal tax bracket.


MIN(A5*2*85%,0.5*MAX(A7-32000,0)+0.35*MAX(A7-44000,0))


A5 is 50% of SS benefit
A7 is 50% of SS benefit plus All Other Taxable Income

That will work as long as their combined Social Security is not less than $12,000. I'll admit that is probably not true for most MFJ's, but I'm OCD about numbers.

 

[FI_RElater]

That will work as long as their combined Social Security is not less than $12,000. I'll admit that is probably not true for most MFJ's, but I'm OCD about numbers.

this: https://www.amazon.com/Panoware-Mens-Funny-Graphic-T-Shirt/dp/B07C596RW5

 

[SevenUp]

See Calculations!X32:AE51 in the MMM case study spreadsheet if you want an example with the Excel formulas exposed.

 

[N02L84ER]

That will work as long as their combined Social Security is not less than $12,000. I'll admit that is probably not true for most MFJ's, but I'm OCD about numbers.

Help me understand when the formula will not work.

Notice the first function is MIN, which is Minimum not Maximum.
If you say the Total income plus half of SS is 20K the formula works.

MIN(10000*50%*2*85%,0.5*MAX(20000-32000,0)+0.35*MAX(20000-44000,0)) =
MIN(8500,0+0) = 0 This is correct. Right?


or


If you say the Total income plus half of SS is 40K, the formula works.

MIN(10000*50%*2*85%,0.5*MAX(40000-32000,0)+0.35*MAX(40000-44000,0)) =

MIN(8500,4000+0) = 4000 This is correct. Right?


or


If you say the Total income plus half of SS is 80K, the formula works

MIN(10000*50%*2*85%,0.5*MAX(80000-32000,0)+0.35*MAX(80000-44000,0)) =

MIN(8500,24000+12600) = 8500 In this instance, it is the 85% that limits the taxable part of SS. Right?

 

[niven]

Help me understand when the formula will not work.

Sorry, I should have been more specific. It's a limited sets of cases where less than $12,000 in SS won't work.

For example:

If the provisional income is $45,000 and the SS is $10,000, the formula doesn't work.

MIN(10000*50%*2*85%,0.5*MAX(45000-32000,0)+0.35*MAX(45000-44000,0)) =

MIN(8500,6500+350) = 6850

This is not correct, the actual taxable is $5,850 because the 50% taxable calculation has it's own limit of 50% of the SS ($5,000 here). The other $850 comes from 85% of the $1,000 of provisional income over $44,000.

So, the provisional income range > $42,000 and < $48,000 here can cause your formula to fail.

 

[N02L84ER]

^^ Got it, thanks!

 

[dixonge]

This is the formula that works for us. We also have a 12% marginal tax bracket.


MIN(A5*2*85%,0.5*MAX(A7-32000,0)+0.35*MAX(A7-44000,0))


A5 is 50% of SS benefit
A7 is 50% of SS benefit plus All Other Taxable Income

Thanks!

See Calculations!X32:AE51 in the MMM case study spreadsheet if you want an example with the Excel formulas exposed.

And thanks for that link - lots of good things in that thread it looks like...

 

[Dtail]

Once you plug this into excel as a worksheet, all your SS tax worries go away. That's what I did and now estimating taxes with SS benefits is easy peasy.

+1
Helps especially with DGF's Roth conversion scenarios.