Does anyone have an Excel formula for calculating total tax (for 2018 federal income) for a given taxable income?
Yes, I’ve googled around for this, but found one that assumes you have the discrete tax tables in another spreadsheet, and also many .xlsx files that appear that they could contain malware.
I mean … I know the brackets (below) but putting this into an Excel formula is alluding me.
Federal tax brackets  Married Filing Jointly 
This amount 
plus 
019050 
0 
10% over 0 
1905077400 
1905 
12% over 19050 
77400165000 
8907 
22% over 77400 
165000315000 
28179 
24% over 165000 
315000400000 
64179 
32% over 315000 
400000600000 
91379 
35% over 400000 
600000 and above 
161379 
37% over 600000 
This is an automaticallygenerated Wiki post for this new topic. Any member can edit this post and use it as a summary of the topic’s highlights.
Couldn’t you do it with a series of if… then… else… expressions for each bracket, such that it returns the right value or zero, then sum them all? Sorry, I don’t know the exact syntax. Maybe they can all be piled into one long formula?
IF taxableincome > 0 AND < 19050, THEN (tax = 0 + taxableincome * .10) ELSE 0
I think the case study spreadsheet will help you. Hopefully, FD doesn’t have a rule against linking to another forum. You can get the link from here and some background on its use:
I think you want the Tax Rates tab.
HTH.
This has potential. Looks like I need to learn a new Excel thing. Once I power through it, I will post here.
IFANDs will be a pretty messy formula.
IFs are probably the least complex way to get this done.
Maybe try if(TI * MR(BM * MR)>0,0,TI * MR)
taxable income
marginal rate
bracket maximum
Add that to each row, then sum. If taxable income is in A1, you can use ‘A$1’  that way you can just create the formula in one row and drag down.
I think sumproduct is probably cleaner, but it’s more difficult to setup.
2 Likes
This is what I’ve been using:
A1 
B 
C 
D 
2 
Taxable: 
$1,000,000 

3 
Tax 
$309,379 

4 



5 
Rate 
Marginal 
Tax Due 
6 
10.00% 
$19,050 
$1,905 – =C6*B6 
7 
12.00% 
$77,400 
$8,907 – =(C7C6)*B7+D6 
8 
22.00% 
$165,000 
$28,179 – =(C8C7)*B8+D7 
9 
24.00% 
$315,000 
$64,179 – =(C9C8)*B9+D8 
10 
32.00% 
$400,000 
$91,379 – =(C10C9)*B10+D9 
11 
35.00% 
$600,000 
$161,379 – =(C11C10)*B11+D10 
12 
37.00% 


Column D above is showing both the result and the formula, in case that’s not clear.
The formula in C3 is a nested IF:
=IF(C2>C11,B12*(C2C11)+D11,IF(C2>C10,B11*(C2C10)+D10,IF(C2>C9,B10*(C2C9)+D9,IF(C2>C8,B9*(C2C8)+D8,IF(C2>C7,B8*(C2C7)+D7,IF(C2>C6,B7*(C2C6)+D6,C2*B6))))))
Keep in mind that this isn’t the total tax owed, because it does not include a few things like qualified dividends or longterm capital gain/losses, which are taxed at a different rate and then added to or subtracted from the value in C3. That’s how I understand it anyway.
5 Likes
Thanks, scripta! That was exactly what I was looking for. It worked fine once I noted what you said about column D not literally being what you see above, but rather only the formula starting at and including the equal.
Your second paragraph is correct but with respect to credits, additional tax on IRAs, AMT, health care tax, etc. However, capital gains/losses would have already been accounted for by the time you get to the Taxable Income line.
For my purposes, I’m just playing what if games on marginal income. Thanks again.
Yes for regular dividends and shortterm capital gains/losses.
Not so for qualified dividends and long term capital gains, which are taxed at lower rates than regular income.
I see what you are saying now. You can’t really reduce Taxable Income > Tax without the Schedule D Tax Worksheet getting in the way. I’ll have to account for that separately.
1 Like
I know you want to roll your own, but Turbotax desktop always has a handy ‘What if’ calculator with the current years rules built in, although the 2017 tax year edition won’t have 2018 stuff.
3 Likes
Also the excel1040.com has a tax estimator using 2018 rules known as to date.
3 Likes