Microsoft Office excel 2003

The salary has the following schedule If salary is up to 5000 its exempted from tax.

If salary is up to 12500, so 12500-5000=7500. now 7500 is taxable with 2%.

If salary is up to 100000, so 100000-12500=97500, now 97500 is taxable with 10%.

If any thing above 100000 it is taxable with 20%.

e.g

Salary = 150,000Then,

first exempted part, 5000 = 0 tax, remaining salary 145,000

for 2% tax, 7500 = 150 tax, remaining salary 137,500

for 10% tax, 100,000 = 10,000 tax, remaining salary 37,500

and for 20% tax, 37,500 = 7500, remaining salary 0Now total tax is 17,650 deducted & net salary 132,350.

Try the formula suggested below. You supplied one complete example (150K) and the formula works for that. The other example you supplied doesn't make sense to me:

If salary is up to 100000, so 100000-12500=97500, now 97500 is taxable with 10%.100000-12500 = 87500, not 97500. My formula returns 8750 in taxes for a 100K salary.

=IF(A1>100000,(A1-112500)*0.2+10150,IF(A1>12500,(A1-12500)*0.1,IF(A1>5000,(A1-5000)*0.02,0)))

BTW...you might want to consider putting your salary breakpoints and tax percentages in a table and then refer to that table in your formula. That way you only have to edit the table if the breakpoints and/or tax percentages change. There will be no need to edit the formula(s).

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Ask Your Question

Weekly Poll