Calculation Tax bands with one formula?

[fcfcfc]

Hi,
I am trying to create a formula to calculate tax due for salaries.
I need a formula to look at a salary range and then ask:
IF the salary is between £0 to £35,000 tax at 20%, anything above this £35,000 and between £150,000 is taxed at 40% and anything above £150,000 is taxed at 50%.
E.g. if your annual salary was £250,000 THEN:
The first £35,000 is taxed at 20%
The next range from (35k to 150k= £115,000) is taxed at 40%
The final range of anything above £150k(i.e. £100,000) is taxed at 50%.
So:
First 35K tax is £7000 (@20%)
Next £115k tax is £46,000 (@40%)
Final anything above 150k i.e. £100k tax is £50k (@50%)
Total tax payable = £103,000


thanks

fcfcfc

 

[GlennUK]

Have a look at this:

Excel Workbook
	A	B	C	D	E
1	Salary	Tax Payable		Brackets	Rates
2	250,000	103,000			0%
3				0	20%
4				35,000	40%
5				150,000	50%
6				9,999,999
Sheet

 

[anandvarma]

try this

Excel Workbook
	A	B
1	250000	103000
Sheet1

 

[littleiitin]

or

=IF((D6-150000)>=0,((D6-150000)*50)/100,0)+IF(AND((D6-35000)>1, (D6-35000)<=115000),((D6-35000)*40)/100,IF((D6-35000)>115000,(115000*40)/100))+IF(D6>=35000,(35000*20)/100,(D6*20)/100)

 

[barry houdini]

One more.....

=A1*50%-MIN(A1,150000)*(50%-40%)-MIN(A1,35000)*(40%-20%)

or if you want it short...

=A1/2-MIN(A1,150000)/10-MIN(A1,35000)/5

 

[fcfcfc]

Thanks to all formulae, I tried them all and they either did not calculate correctly of gave a #NA error message.

BUT thanks to barry houdini ...YOU GET THE CIGAR! perfect

Thanks to all for the assistance


regards

fcfcfc