# Formula to calculate emergency tax 2015/16

#### alastair77

##### Board Regular
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>

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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

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

Excel 2007
AB
1Gross pay18750
2Allowance883.34
3Taxable income17866.66
4
5Tax Breakdown
6Up to 2649529.8
7Up to 9850.992880.796
8Over 98513607.0515
9
10Tax to be paid7017.6475
Sheet1
Cell Formulas
RangeFormula
B3=B1-B2
B6=2649*0.2
B7=(9850.99-2649)*0.4
B8=(B3-9850.99)*0.45
B10=SUM(B6:B9)

A friend got it now

Using min and max formulas .

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

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)

Replies
18
Views
10K
Replies
9
Views
951
Replies
1
Views
806
Replies
3
Views
676
Replies
0
Views
530

1,202,913
Messages
6,052,527
Members
444,588
Latest member
ViJN

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back