Tax IF formulas?

[rescueraven]

Hey,
I need help figuring out an IF formula that would allow me to calculate the tax owed. The tax rates are 20%, 25% and 30% and the full bracket total for 20% is 4,000$ and for 25%, 11,500$.
In D14, I have as a taxable income, 20,000$ and In E14, I would need a IF formula that calculates that... but I would need to copy only one formula down the E column to be used on varying taxable incomes...
Thanks in advance,
Raven

 

[PA HS Teacher]

Could you give a range of taxable incomes and the desired result for each.

 

[rescueraven]

From $0-$100,384.
I want the tax owed for each individual. I have a flow chart with how the IF formula should work.
Y Income ≤ 20,000 N
tax= 20% * Income Income≤50,000
Y N
tax=40,000 +25% * (Income - 2000) /
tax= 11500+30%*(Income-5,000)

Does that make if clearer?

 

[konew1]

Hi, I don't really understand where the tax rate changes occur, but I think you mean income from $1 to $20,000 is taxed at 20%, then income from $20,001 to $66,000 is taxed at 25% and then all income over $66,000 is taked at 30%.
You should be able to adjust this for differnt break points. If the income amount is in cell B1 paste this into C1 and copy down.
It assumes you never have a negative number in column B.

=(MIN(B1,20000)*20%)+(MAX(0,MIN(B1-20000,66000-20000))*0.25)+(MAX(0,(B1-20000-46000))*0.3)

 

[rescueraven]

20% of all income up to 20 000$
25% of all income over 20 000$ and up to 50 000$.
30% of all income over 50 000$.

But is this an IF formula?
Thanks guys for all you help up to now!!

 

[rescueraven]

but konew, where does the value 46000 come from?

 

[konew1]

I understood that 46,000 (from 20,000 to 66,000) was paid at 25%.
You could simplify my formula so the part "-20000-46000" reads just "-66000"

 

[konew1]

Just read your posting #5.
This uses your tax brackets.
You only have 3 tax brackets so you don't really need IF statements, just add up the tax payable in each bracket

=(MIN(B1,20000)*20%)+(MAX(0,MIN(B1-20000,50000-20000))*0.25)+(MAX(0,(B1-50000))*0.3)

 

[rescueraven]

I need the IF formula because its an exercise for school. But thank you soo much for all your help. It works now!

 

[rescueraven]

One more question though, I need to filter the data afterwards and in order for it not to change, I need to put $ in front of my constant cell address, right? But every time I do, it messes up the formula and reverts to all the values in numbers...
for example: =(MIN(D14,B10)*C9)+(MAX(0,MIN(D14-B10,B11-B10))*C10)+(MAX(0,(D14-B11))*C11)

vs:=(MIN(D16,20000)*20%)+(MAX(0,MIN(D16-20000,50000-20000))*0.25)+(MAX(0,(D16-50000))*0.3)