This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. To find out more about cookies on this website and how to delete cookies, see our Cookie Policy.

Tools which collect anonymous data to enable us to see how visitors use our site and how it performs. We use this to improve our products, services and user experience.


Tools that enable essential services and functionality, including identity verification, service continuity and site security.

Where Taxpayers and Advisers Meet

Help with NI calculation please ...

Posts: 2
Joined: Wed Aug 06, 2008 3:07 pm

Postby solo7 » Sun Dec 14, 2003 7:18 am

My employer has asked me to put an excel spreadsheet together to itimise National Insurance payments.
I have been given a table of taxable and un-taxable bands which is :-
from £0 to £4004.oo no NI payments
from £4004.01 to £4615.oo Ni credit of -3.50%
from £4615.01 to £30,940.oo NI tax of 9.30%
and from 30940.01 upwards a NI tax of 12.8%

I can get a result on the first example I have been given which is for a wage of £12,720:
£0 - £4004 = £0 NI payment
£4004.01 - £4615.00 = -£21.39 NI payment (I get this)
£4615.01 - £30940.00 = £753.77 NI payment (I get this)
total payment is £732 (rounded) - (I get this)

But when I try and go over the upper limit my calculation seems to fall apart. Either my example is wrong or my calculations need adapting for a wage of £34,413:

£0 - £4004 = £0 NI payment
£4004.01 - £4615.00 = -£21.39 NI payment
£4615.01 - £30940.00 = £2399.87 NI payment (I can't get this)
£30940.01 + = £471.17 NI payment (I can't get this)
Total payment is £2850 (rounded) - (I can't get this)

Can anyone itimise the calculation for the second example, or tell me where I can find a similar example. Or perhaps just give me a clue ...

Thanks in advance

Solo7 ...

Ian McTernan CTA
Posts: 1232
Joined: Wed Aug 06, 2008 3:02 pm
Location: Bedford

Postby Ian McTernan CTA » Sun Dec 14, 2003 9:35 am

Buy a cheap payroll programme and it will calculate the figures for you. There are several good cheap ones on the market for less than £200 which will save you a lot of time and money (and mistakes).

Ian McTernan CTA
McTernan Associates Ltd
Charterd Tax Advisers
McTernan Associates Ltd
Chartered Tax Advisers
Email through link on website:

Posts: 13
Joined: Wed Aug 06, 2008 2:18 pm

Postby madgooner » Mon Dec 15, 2003 9:56 am

If you that committed to achieving the result yourself try the calculation using embedded IF and Max Functions.

E.G.If 'Salary'
Test it thoroughly if you intend to use it for real. At least £200 of management time is probably involved, but the excersise may be theraputic! Hence Ian's response above.

Posts: 2
Joined: Wed Aug 06, 2008 3:07 pm

Postby solo7 » Mon Dec 15, 2003 10:56 am

Thank's for the pointers guys.
The actual formulae isn't my problem. I can get the solutions for the first example I have already. It seems that once the Upper Threshold has been reached the %'s or the earnings band changes. I suppose my question would have been better presented if I had asked What is the difference in the calculation between the middle earnings threshold and the upper threshold (apart from the Percentage moving from 9.3% to 12.8%). eg, In my 2nd example I have a NI contribution of £471.17 for the Upper Threshold but £34,413 - £30,940.01 = £3,472.99 multiplying this by 12.8% gives £444.54.
Is this a 'bum' example or am I missing something?

Solo7 ...

Posts: 13
Joined: Wed Aug 06, 2008 2:18 pm

Postby madgooner » Mon Dec 15, 2003 11:24 am

Has the rates.

12.8% on earnings from 89 pw to 595 pw then 1% for a 'standard' limited co. (e.g. no Pension Scheme)

I'm not sure I understand your Calcs, where does 9.3% come from?

You do not add the lower limit to the upper threshold, anything above 30940 attracts the 1% rate.

Return to “PAYE and Payroll Taxes, National Insurance, NICs”