Formula to calculate emergency tax 2015/16

[alastair77]

hi - hope you can help .

if i enter in any amount, i am trying to calculate the emergency tax. here i enter in 18750 and the tax should be 6885.20.

emergency tax gets the personal allowance then the rest gets taxed at 20% then the rest up to 9850.99 gets taxed up to 40% then the remainder at 45%

stayed up all night and can't do it so hoping you can help

Gross amount <colgroup><col></colgroup><tbody> </tbody>	18750 <colgroup><col></colgroup><tbody> </tbody>
Personal allowance (month) deduct <colgroup><col></colgroup><tbody> </tbody>	883.34 <colgroup><col></colgroup><tbody> </tbody>
to be taxed at 20% : £2649 <colgroup><col></colgroup><tbody> </tbody>	(formua should equal to 529) <colgroup><col></colgroup><tbody> </tbody>
to be taxed at 40 % : £9850.99 <colgroup><col></colgroup><tbody> </tbody>	(formula should equal to 3940.40) <colgroup><col></colgroup><tbody> </tbody>
to be taxed at 45% : excess / remaining <colgroup><col></colgroup><tbody> </tbody>	(formula should equal to 2415) <colgroup><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Michael M]

Try this
Duplicate post here

http://www.mrexcel.com/forum/excel-questions/856672-formula-percentage-up-amount.html

Excel 2007
	A	B
4	Gross pay	18750
5	Allowance	883.34
6	Taxable income	17866.66
7	Tax to be paid	6885.198
Sheet6

Cell Formulas
Range	Formula
B6	=B4-B5
B7	=(2649*0.2)+(9850.99*0.4)+((B6-(2649+9850.99))*0.45)

 

[alastair77]

Many thanks !!!

i was looking to get the 20% figure then the 40% then the 45% separately but will try to split your formula to get that ..



if i had brains i would be dangerous

 

[Michael M]

OK, here 'tis, but I think your figure is flawed.
Shouldn't it be

Excel 2007
	A	B
1	Gross pay	18750
2	Allowance	883.34
3	Taxable income	17866.66
4
5	Tax Breakdown
6	Up to 2649	529.8
7	Up to 9850.99	2880.796
8	Over 9851	3607.0515
9
10	Tax to be paid	7017.6475
Sheet1

Cell Formulas
Range	Formula
B3	=B1-B2
B6	=2649*0.2
B7	=(9850.99-2649)*0.4
B8	=(B3-9850.99)*0.45
B10	=SUM(B6:B9)

 

[alastair77]

https://www.scottishwidows.co.uk/secure/retirement-planning/calculators/tax-calculator/

Many thanks for the formulas - was trying to get something i clould put any figure into and get the emergency tax figure - the same as the website does but it isnt happening still !

 

[alastair77]

A friend got it now

Using min and max formulas .

it has all been a good learning experience .. thank you

 

[SpillerBD]

So anyone with a similar question nets an answer, one solution is
=SUM(IF(LBound-(Gross-Deduction)<0,LBound,MAX(LBound-(Gross-Deduction),0))*Rate1,IF(UBound-(Gross-Deduction)<0,UBound,MAX(UBound-(Gross-Deduction),0))*Rate2,IF((Gross-Deduction)-UBound-LBound<0,0,(Gross-Deduction)-UBound-LBound)*Rate3)

Where in OPs situation:
LBound = 2649
UBound = 9580.99
Rate1 = 0.2
Rate2 = 0.4
Rate3 = 0.45
Deduction is the monthly deduction amount (Standard Allowance is 10,600 annually or 883.3333 rounded to 883.34)