Excel formula for calculating 2018 federal income tax

Finance Tax
1

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
0-19050 0 10% over 0
19050-77400 1905 12% over 19050
77400-165000 8907 22% over 77400
165000-315000 28179 24% over 165000
315000-400000 64179 32% over 315000
400000-600000 91379 35% over 400000
600000 and above 161379 37% over 600000
2

This is an automatically-generated Wiki post for this new topic. Any member can edit this post and use it as a summary of the topic’s highlights.

3

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

4

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.

5

This has potential. Looks like I need to learn a new Excel thing. Once I power through it, I will post here.

6

IF-ANDs 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
7

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 – =(C7-C6)*B7+D6
8 22.00% $165,000 $28,179 – =(C8-C7)*B8+D7
9 24.00% $315,000 $64,179 – =(C9-C8)*B9+D8
10 32.00% $400,000 $91,379 – =(C10-C9)*B10+D9
11 35.00% $600,000 $161,379 – =(C11-C10)*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*(C2-C11)+D11,IF(C2>C10,B11*(C2-C10)+D10,IF(C2>C9,B10*(C2-C9)+D9,IF(C2>C8,B9*(C2-C8)+D8,IF(C2>C7,B8*(C2-C7)+D7,IF(C2>C6,B7*(C2-C6)+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 long-term 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
8

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.

9

Yes for regular dividends and short-term capital gains/losses.
Not so for qualified dividends and long term capital gains, which are taxed at lower rates than regular income.

10

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
11

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
12

Also the excel1040.com has a tax estimator using 2018 rules known as to date.

3 Likes