National Insurance Formula

[bobzy20]

Hi

I'm looking to get help creating a National Insurance Formula which is based on the factors below:

• you pay National Insurance contributions if you earn more than £162 a week
• you pay 12% of your earnings above this limit and up to £892 a week
• the rate drops to 2% of your earnings over £892 a week.

For example, if you earn £1,000 a week, you pay:

• nothing on the first £162
• 12% (£87.60) on the next £730
• 2% (£2.16) on the next £108.

Any help would be great.

Thanks

Bob

 

[footoo]

=if(a1<=162,0,if(a1<=892,(a1-162)*0.12,87.6+((a1-892)*0.02)))

 

[bobzy20]

Thanks, works nicely.

Bob

 

[mmara]

I've updated the formula to use the 2019/2020 rates and also made it monthly, instead of weekly.
=IF(A1<=719.01,0,IF(A1<=4167,(A1-719.01)*0.12,(4167-719.01)+((A1-4167)*0.02)))
If doing it for a different year/rate/period then just fill in the values from:

National Insurance rates and categories

Class 1 National Insurance (NI) contribution rates for the current tax year, what NI category letters mean.

www.gov.uk

Then fill them into the formula (without the "<<" and ">>")
<<Primary Threshold>> 719.01
<<Upper Earnings Limit>> 4167
=IF(A1<=<<Primary Threshold>>,0,IF(A1<=<<Upper Earnings Limit>>,(A1-<<Primary Threshold>>)*0.12,(<<Upper Earnings Limit>>-<<Primary Threshold>>)+((A1-<<Upper Earnings Limit>>)*0.02)))

 

[MARK858]

Just a different method using the figures from post number 1.

Book1
	A	B
1	1000	89.76
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(--(A1>{0;162;892}), (A1-{0;162;892}), {0;0.12;-0.1})

or with the figures from post number 4 (weekly)...

Book1
	A	B
1	1000	96.28
Sheet1

Cell Formulas
Range	Formula
B1	B1	=SUMPRODUCT(--(A1>{0;166;962}), (A1-{0;166;962}), {0;0.12;-0.1})