Creating an Excel template to calculate Employer’s National Insurance changes

I posted on LinkedIn a couple of weeks ago about creating an Excel template to calculate budget impact of the changes to Employer’s National Insurance.

In this post I’ll share a few observations, both about the calculations and also the act of creating the template.

Three changes, not one

In the Budget on 30th October 2024, the Chancellor announced changes to three elements of the Employer National Insurance calculation;

• the headline rate – this will change from 13.8% to 15% in April 2025
• the Secondary Threshold, which will reduce from £9,100 to £5,000 (annualised figures – it’s actually applied weekly / monthly to pay packets)
• the Employment Allowance, which will rise from £5,000 to £10,100

The Secondary Threshold applies to each employee’s earnings and you pay the rate on every £1 above this threshold. The Employment Allowance is calculated on your total Employer NI bill.

So every element has an impact, and it is not as straightforward as saying “salary bill of x = increase of y”.

In particular, the impact of the threshold lowering negatively impacts on organisations with low paid staff. Whereas the impact of the Employment Allowance positively benefits smaller organisations and means that some organisations might be paying less Employer National Insurance next year despite the first two changes.

Creating an Excel template meant that I could play with a few scenarios. I discovered for example that if you employed 4 people at £40,000 each you were better off under the changes, whereas if you employed 8 people at £20,000 each you would be slightly worse off. Same salary bill, very different results.

Creating an Excel template – factors to consider

The only way to effectively model the changes is to replicate what happens at payroll. Firstly, you calculate the amounts of salary over the threshold, then you apply the rate. You do this by employee, total the difference and then subtract the employment allowance.

Of course, your payroll software probably does this for you. But whenever you’re creating a spreadsheet template, it’s always good to think through the business logic before diving in.

In this case, I identified that the template had to be able to expand to deal with different numbers of staff. I decided that using an Excel Table would be best for this. If set up correctly, an Excel Table will autoexpand with new data and will copy the formulas down.

I then set up the Table so that the user can input staff salary and FTE, and then it will calculate the individual ER contribution and then summarise all of them. Finally, I added another calculation to take account of the threshold.

Presentation for a wider audience

I also went through several iterations of the design. When I was just doing it for me to play around with, I didn’t pay much attention to layout or labelling. However, when I realised this would be useful for others, I thought about how to lay it out a little differently. The image at the top of the post is my latest version, but here is a much earlier version:

While the calculation principles are exactly the same, I made a few changes to make it clearer to the end user what was happening. Firstly, I split out the calculations so they could see the impact of the change before and after the allowance. Secondly, I used formatting to distinguish the standing information at the top from the calculations. Thirdly, I relabelled rows and columns to try to make them more clear.

By the way, in my LinkedIn post I offered to share the template I’d created to do this. The offer is still on – if you’re a charity or not for profit, just message me and I’ll send it to you. But I’ve hopefully given you enough information here to create your own!

I will also repeat various caveats; this template won’t work if you have salary sacrifice, or if you employ staff with seasonable hours. And it should not be taken as tax or accounting advice.

If you have a need to create some kind of staff or other modelling tool like this, then please consider me for this work. I charge by the half day so it may not even cost you that much.