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

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History