Calculating IF formula for Tax & NI Deductions..

promoATkt5DOTcom

New Member
Joined
Apr 17, 2019
Messages
1
Hi All,


Any help is appreciated, I've been playing about with this trying to get the formulas right, assuming an IF formula, but not sure how to separate if it requires an answer between a certain range.


For example, I'm trying to write cell formulas so whatever Gross income is entered it works out what level tax and NI contributions, less any voluntary contributions, resulting in net pay.


I'm hitting a wall with the following:


1) Tax Levels Amount
Basic Rate Up to £32,000.00 (20%)
Upper rate £32,000.01 - £150,000.00 (40%)
Additional Rate £150,000.01 upward (45%)

I can see the following for the basic rate tax as:
=If(B8<=E3,B8*F3,B8*F3)
B8 being Taxable Income after deduction of Personal Allowance


But as soon as i try to qualify a range, ie: If Taxable Income above £32k but below £150k deduct 40%


and the same for 45% on anything above £150k


then i need to figure how to assign each tax bracket deduction based on the level of taxable income, IE, under £32k level 1, over £32k but under £150k=level 2 and so on..






2) National Insurance Levels Lower Upper
0% charged between £0.00 and £154.99
12% charged between £155.00 and £827.00
2% charged anything over £827.00


On a similar basis as above, trying to work out how to apply each at the relevant levels.


3)
If i contribute £1 to my workplace pension, then the employer contributes £2, etc.




ALL & Any assistance is Greatly appreciated.


Many Thanks all in Advance..


KJ
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
766
Office Version
365
Platform
Windows
Hello,

You can nest the IF function so that it fulfils multiple criteria, I assume that if a gross income is say £40,000 then the income tax will be 20%(32000) + 40%(40000 - 32000) which results in 6400 + 3200 = £9600

Going on this basis you can also use a SUMPRODUCT formula and set up some tables before hand to assist with the calculations.

For example to work out the National Insurance based on your values and a 40k gross income:
=SUMPRODUCT((40000/52>{0;155;827})*(40000/52-{0;155;827})*({0;0.12;-0.1}))*52
This returns £3832.80 (National insurance paid per annum) take off the *52 at the end if you want it weekly.

Similar logic can be applied to the income tax part, you can set up an array for the bracket values to make it easier so instead of having {0;155;827} you might want B1:B3 (where B1=0, B2=155, B3=827)

I hope this makes sense. Let me know if you need anything else clearing up.

Also it might be worth taking off the pension from the income before hand as that is free from tax(?)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,097
Office Version
2010
Platform
Windows
N.B.
1. It is not necessary to use the Named Ranges but it makes the formula easier to read.
The 2a part is just the name of the sheet where I prepared the example.
The 3 SumProduct formulas are all the same; the ranges are just shown differently.






<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Amount</td><td style="text-align: right;;">50,000.00</td><td style="text-align: right;;"></td><td style=";">Brackets</td><td style=";">Rate</td><td style=";">Rate_Diff</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Tax</td><td style="text-align: right;background-color: #92D050;;">13,600.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">20%</td><td style="text-align: right;;">20%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">13,600.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">32,000</td><td style="text-align: right;;">40%</td><td style="text-align: right;;">20%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">13,600.00</td><td style="text-align: right;;"></td><td style="text-align: right;;">150,000</td><td style="text-align: right;;">45%</td><td style="text-align: right;;">5%</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;">13,600.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">13,600.00</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:1.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">2a</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=E2-N(<font color="Blue">E1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B1>rB</font>),B1-rB,rDiff</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B3</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B1>{0;32000;150000}</font>),B1-{0;32000;150000},{0.2;0.2;0.05}</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B4</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">--(<font color="Red">B1>D2:D4</font>),B1-D2:D4,F2:F4</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=(<font color="Blue">B1>0</font>)*B1*0.2+(<font color="Blue">B1>32000</font>)*(<font color="Blue">B1-32000</font>)*0.2+(<font color="Blue">B1>150000</font>)*(<font color="Blue">B1-150000</font>)*0.05</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">B1>0,B1<=32000</font>),B1*0.2,IF(<font color="Red">AND(<font color="Green">B1>32000,B1<=150000</font>),(<font color="Green">B1-32000</font>)*0.4+6400,IF(<font color="Green">B1>150000,(<font color="Purple">B1-150000</font>)*0.45+47200</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">'2a'!rB</th><td style="text-align:left">='2a'!$D$2:$D$4</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">'2a'!rDiff</th><td style="text-align:left">='2a'!$F$2:$F$4</td></tr></tbody></table></td></tr></table>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,369
Messages
5,486,448
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top