Income Tax

[Scoober]

Hi All,

Can someone come up with a formula on how to work out how much tax is paid
on a person's income.

Tax rates are as follows

$0 - $14,000 = 12.5%

$14,000 - $48,000 = 21%

$48,000 - $70,000 = 33%

$70,000+ = 38%

E30 = Income

J30 = Target Cell

I have no idea how to attack this so can even offer a formula that does not
work!!

 

[demi]

=IF(E30<14000,0.125,IF(E30<48000,0.21,IF(E30<70000,0.33,0.38)))*E30

 

[Scoober]

Thanks Demi,

This formula appears to tax the entire income at the rate the income fits
into instead of taxing the income on the tiers as it passes through them.

e.g. on the 80,000.00 income the formula would have to work out the following

The first $14,000 @ 12.5% + the income between $14,000 and $48,000 @ 21% +
the income between $48,000 and $70,000 @ 33% + $10,000 @ 38%.

as i see it the formula you sent to me taxes the whole 80,000 at 38% which
is not accurate.

Do you have a formula that completes the above example?

 

[Vijay]

=IF(E30<=14000,E30*0.125,IF(E30<=48000,(E30-14000)*0.21+1750,IF(E30<=70000,(E30-48000)*0.33+8890,(E30-70000)*0.38+16150)))

Vijay

 

[Scoober]

Hi Vijay,

When i tried your formula i got the message #Value!

I cannot work out why - do you have any thoughts?

 

[Ashish Mathur]

Hi,

You may refer to this article - http://ashishmathur.com/articles.aspx.
Scroll down to B > 1

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Rosenfeld]

Hi All,

Can someone come up with a formula on how to work out how much tax is paid
on a person's income.

Tax rates are as follows

$0 - $14,000 = 12.5%

$14,000 - $48,000 = 21%

$48,000 - $70,000 = 33%

$70,000+ = 38%

E30 = Income

J30 = Target Cell

I have no idea how to attack this so can even offer a formula that does not
work!!

Easiest, in my opinion, to both understand and maintain, is to use an lookup
table.

Set up a table someplace on your worksheet with the tax tiers, amount of tax on
each tier, and the marginal amount. I used the range J2:L5

The table will look like:

Tier Amount Rate
$ 0.00 $ 0.00 12.5%
$14,000.00 $ 1,750.00 21.0%
$48,000.00 $ 8,890.00 33.0%
$70,000.00 $16,150.00 38.0%

or, showing the formula in the second column:

Tier Amount Rate
0 0 0.125
14000 =K2+(J3-J2)*L2 0.21
48000 =K3+(J4-J3)*L3 0.33
70000 =K4+(J5-J4)*L4 0.38

I then defined the Name: TaxTbl to refer to this range (J2:L5)

With your income in A1, the tax is given by the formula:

=VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3)

As you can see, you can easily modify and/or extend the table if/when tax rates
change.

--ron

 

[Scoober]

Hi Ron,

I am a beginner so not so bright.

I have c&P'd

$ 0.00 $ 0.00 12.5%
$14,000.00 $ 1,750.00 21.0%
$48,000.00 $ 8,890.00 33.0%
$70,000.00 $16,150.00 38.0%

to sheet 3 and Defined it TaxTbl

I have then pasted

=VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3)

In J30.

I get the answer #N/A

What step have i missed?

Scoober

 

[Scoober]

Hi David,

Thank you for your help.

As i am working out tax rates i am using numbers. When you say

=ISTEXT(E30) and =ISNUMBER(E30)

Do i have to add this to Vijays formula - if so how would it look?

 

[Bernd P]

Hello Scoober,

Enter into cell J30:
=MAX(E30*{0.125,0.21,0.33,0.38}+{0,-1190,-6950,-10450})

If you are interested in the derivation of this formula:
http://sulprobil.com/html/minimax_interpolation.html

Have fun,
Bernd

 

[Ron Rosenfeld]

Hi Ron,

I am a beginner so not so bright.

I have c&P'd

$ 0.00 $ 0.00 12.5%
$14,000.00 $ 1,750.00 21.0%
$48,000.00 $ 8,890.00 33.0%
$70,000.00 $16,150.00 38.0%

to sheet 3 and Defined it TaxTbl

I have then pasted

=VLOOKUP(A1,TaxTbl,2)+(A1-VLOOKUP(A1,TaxTbl,1))*VLOOKUP(A1,TaxTbl,3)

In J30.

I get the answer #N/A

What step have i missed?

Scoober

What is in cell A1?

--ron

 

[Steve Dunn]

One of those days Bernd?

=MAX(E30*{0.125,0.21,0.33,0.38}-{0,1190,6950,10450})