Author Topic: Marginal tax calculation spreadsheet  (Read 6011 times)

electriceagle

  • Bristles
  • ***
  • Posts: 474
Marginal tax calculation spreadsheet
« on: September 07, 2014, 04:40:11 PM »
I've been slowly trying to build a spreadsheet that calculates overall marginal tax rates for different types of income. This would be very useful in ER as one needs to be able to calculate how much roth conversion to do, whether and when to harvest capital gains (and losses), etc.

This project is difficult because there are so many intersecting tax policies. You have social security/FICA tax (which can be a form of saving if you discount for risk that SS won't be there when you are old), federal income tax, state income tax, different rates for dividends/capital gains, obamacare subsidy (which is treated via federal taxes), etc.

Does anyone know if such a spreadsheet exists? Alternatively, is anyone interested in crowd-creating such a document?

RapmasterD

  • Pencil Stache
  • ****
  • Posts: 589
  • Location: SF Peninsula
Re: Marginal tax calculation spreadsheet
« Reply #1 on: September 07, 2014, 04:46:19 PM »
This is f'ing weird! I was literally thinking the same thing yesterday. I found what I THOUGHT was a spreadsheet. Instead it was a challenge to build such a spreadsheet. And it looked like said spreadsheet would require a plethora of IF-THEN statements. Interesting challenge! But I wanted immediate gratiification.

Alternative: Try this site. Looks like it can provide pretty good estimates for both Fed and State! http://taxcalculator.org

jordand1986

  • 5 O'Clock Shadow
  • *
  • Posts: 14
Re: Marginal tax calculation spreadsheet
« Reply #2 on: September 07, 2014, 05:38:30 PM »
Moneychimp has a good federal calculator, might be a tad too simple for your needs though.

 http://www.moneychimp.com/features/tax_calculator.htm

Gin1984

  • Magnum Stache
  • ******
  • Posts: 4730
Re: Marginal tax calculation spreadsheet
« Reply #3 on: September 07, 2014, 05:51:50 PM »
This is f'ing weird! I was literally thinking the same thing yesterday. I found what I THOUGHT was a spreadsheet. Instead it was a challenge to build such a spreadsheet. And it looked like said spreadsheet would require a plethora of IF-THEN statements. Interesting challenge! But I wanted immediate gratiification.

Alternative: Try this site. Looks like it can provide pretty good estimates for both Fed and State! http://taxcalculator.org
That calculator is for 2013.

MDM

  • Walrus Stache
  • *******
  • Posts: 9492
Re: Marginal tax calculation spreadsheet
« Reply #4 on: September 07, 2014, 06:58:54 PM »
If you want a spreadsheet solution, https://sites.google.com/site/excel1040/ may be the most comprehensive for federal taxes.  From a quick glance, it appears that state taxes are not considered. 

I've considered adding a calculation to the spreadsheet discussed here that would display something like "your marginal federal tax rate is XX%", but haven't gotten around to it.  All the information needed is in the VB functions that calculate the tax itself.  Feel free to take whatever you want from that - if you do write a nested IF or VB function I'd be happy to put it into the case study spreadsheet.

MDM

  • Walrus Stache
  • *******
  • Posts: 9492
Re: Marginal tax calculation spreadsheet
« Reply #5 on: September 08, 2014, 02:28:14 PM »
electriceagle, you provided me enough incentive to go do it.  See VB function now in the spreadsheet linked in the post above.

Function takes two inputs, 1) number of exemptions and 2) taxable income, and returns a horizontal array of two values: tax and marginal tax rate. 

Known limitations: considers only Married Filing Jointly and Single tax filing statuses, and does not display marginal rates for qualified dividends and long term capital gains.  But it's a start....

RapmasterD

  • Pencil Stache
  • ****
  • Posts: 589
  • Location: SF Peninsula
Re: Marginal tax calculation spreadsheet
« Reply #6 on: September 08, 2014, 06:08:07 PM »
This is f'ing weird! I was literally thinking the same thing yesterday. I found what I THOUGHT was a spreadsheet. Instead it was a challenge to build such a spreadsheet. And it looked like said spreadsheet would require a plethora of IF-THEN statements. Interesting challenge! But I wanted immediate gratiification.

Alternative: Try this site. Looks like it can provide pretty good estimates for both Fed and State! http://taxcalculator.org
That calculator is for 2013.

Good enough for me...for an estimate. I'm not aware of any big changes for 2014. 2013 was when the shit came down. Ummm....plus, it does read "2014" on the top of the webpage.

MDM

  • Walrus Stache
  • *******
  • Posts: 9492
Re: Marginal tax calculation spreadsheet
« Reply #7 on: September 08, 2014, 07:26:53 PM »
This is f'ing weird! I was literally thinking the same thing yesterday. I found what I THOUGHT was a spreadsheet. Instead it was a challenge to build such a spreadsheet. And it looked like said spreadsheet would require a plethora of IF-THEN statements. Interesting challenge! But I wanted immediate gratiification.

Alternative: Try this site. Looks like it can provide pretty good estimates for both Fed and State! http://taxcalculator.org
That calculator is for 2013.

Good enough for me...for an estimate. I'm not aware of any big changes for 2014. 2013 was when the shit came down. Ummm....plus, it does read "2014" on the top of the webpage.

Yep, it does say 2014 on the top of the webpage.  And it appears someone did some very nice work with it.  The numbers, however, are from 2012 and it doesn't seem to add the correct number of exemptions.  E.g., with a status of MFJ, checking the boxing for "Yourself", "Spouse", "Do you have a qualifying child dependent", and entering "2" for "Total number of dependents" should give 4 exemptions worth $15,800 in 2014.  The webpage, however, has $7600 for Total Exemptions.

It's too bad - again, it appears someone put a lot of effort into it.  But if the internals haven't been updated past 2012, and it fails a simple exemption calculation, then...?  Unless I missed some option(s), which is completely possible.

Joel

  • Pencil Stache
  • ****
  • Posts: 774
  • Location: California
Re: Marginal tax calculation spreadsheet
« Reply #8 on: September 08, 2014, 09:28:22 PM »
I just built an excel spreadsheet that has all my relevant information in it that calculate my marginal tax rate in order to make sure I stay out of the 25% tax bracket. But at every income level there can be a lot of variables.

electriceagle

  • Bristles
  • ***
  • Posts: 474
Re: Marginal tax calculation spreadsheet
« Reply #9 on: September 14, 2014, 02:57:53 PM »
The 1040 calculator is pretty good, however it needs to be updated to account for Obamacare. Premium assistance makes a big difference on overall tax liability.