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
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