Solved I want to find the formula for salary tax calculation.

Microsoft Office excel 2003
September 11, 2014 at 05:26:52
Specs: Windows 7, 2 GHz
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,000

Then,
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 0

Now total tax is 17,650 deducted & net salary 132,350.


See More: I want to find the formula for salary tax calculation.

Report •

#1
September 12, 2014 at 06:50:42
✔ Best Answer
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


Report •
Related Solutions


Ask Question