Payroll projections in Excel let you forecast your total employee costs – salaries, employer’s National Insurance, pension contributions, and benefits – so you can budget accurately and plan for hiring decisions. While dedicated payroll software automates this, Excel remains a popular choice for small UK businesses that want a free, flexible forecasting tool.
FREE QUOTE COMPARISON
Compare Payroll Quotes from Trusted Suppliers
✓ Plans from £4/employee per month
100% free • No obligation • Takes under 2 minutes
This guide walks you through building a payroll projection spreadsheet in Excel step by step, covering UK-specific calculations for tax, NI, and auto-enrolment pensions. We also cover when it makes sense to move from Excel to dedicated payroll software.
Why Are Payroll Projections Useful?
Payroll projections show your total employment costs per employee and across the business, helping you budget, plan hiring, and spot savings opportunities.
Payroll projections go beyond just tracking wages. They give you a full picture of what each employee actually costs your business, including:
- Gross salary – the headline figure before deductions
- Employer’s National Insurance – currently 15% on earnings above £175/week (2025/26 rates)
- Pension contributions – minimum 3% employer contribution under auto-enrolment
- Benefits in kind – company car, private health insurance, cycle-to-work schemes
- Overtime and bonuses – variable costs that impact monthly budgets
An employee on a £30,000 salary actually costs around £35,000–£36,000 once employer NI and pension contributions are included. Without projections, these hidden costs can catch businesses out – especially when planning new hires.
Step 1: Download a Payroll Calculator Template
Start with Microsoft’s free payroll calculator template in Excel – it includes salary, tax, and hours-worked worksheets ready to customise.
Rather than building a spreadsheet from scratch, start with a template. Microsoft offers free payroll calculator templates that include:
- Salary worksheet – employee salary and tax information
- Hourly worksheet – calculates payroll based on hours worked plus overtime
- Payslip worksheet – generates pay stubs for individual employees
Search for “payroll calculator” in Excel’s template gallery (File > New > Search) or download from the Microsoft Office template library. For UK-specific calculations, you will need to adjust the tax and NI bands to match HMRC rates.
Step 2: Gather Your Employee Data
Compile each employee’s salary, tax code, NI category, pension opt-in status, student loan plan, and any benefits before entering data.
Before you start entering data, gather the following for every employee:
- Full name and payroll reference number
- Annual salary or hourly rate
- Tax code (from HMRC – usually 1257L for standard personal allowance)
- National Insurance category letter (usually A for most employees)
- Pension scheme membership – opted in or opted out, contribution percentage
- Student loan plan (Plan 1, 2, 4, or Postgraduate)
- Benefits in kind – anything reported on P11D
Having this organised upfront saves significant time. Most of this information is on your employees’ P45s, P60s, or HMRC tax coding notices.
Step 3: Set Up Your Projection Columns
Structure your Excel sheet with columns for gross pay, income tax, employee NI, employer NI, pension (employee + employer), net pay, and total cost to company.
Your spreadsheet should include these columns for each employee:
| Column | What It Contains |
|---|---|
| Employee name | Full name and reference |
| Monthly gross pay | Annual salary ÷ 12 |
| Income tax | PAYE based on tax code and band |
| Employee NI | 8% on earnings £242–£967/week, 2% above |
| Employer NI | 15% on earnings above £175/week |
| Employee pension | Minimum 5% of qualifying earnings |
| Employer pension | Minimum 3% of qualifying earnings |
| Student loan | 9% above threshold (varies by plan) |
| Net pay | Gross minus all employee deductions |
| Total cost to company | Gross + employer NI + employer pension |
- Excel works for payroll projections under 5 employees - breaks down once you need RTI submissions, payslip generation or pension file output
- Employer National Insurance adds 13.8% above the £9,100 threshold - the largest hidden line item in any UK payroll forecast you build
- Auto-enrolment pension contributions add 3% employer minimum - mandatory for staff earning over £6,240/year under UK pensions law
- Building a 12-month payroll forecast in Excel takes 2-4 hours - and needs annual updates for tax thresholds, pension rates and minimum wage changes
- Switching to dedicated payroll software saves 5-10 hours per month - for businesses with 5+ employees, HMRC submissions become fully automatic
The key projection figure is the total cost to company column – this is what you actually spend per employee, not just what they take home. Sum this column for your total monthly payroll cost, then multiply by 12 for your annual projection.
Step 4: Enter Data and Build Formulas
Use Excel formulas for tax bands and NI thresholds so projections update automatically when you change salaries or HMRC rates change.
Input your employee data and build formulas for the deduction columns. For UK payroll, the key formulas are:
- Income tax: Use nested IF statements for the 20%, 40%, and 45% bands, accounting for the personal allowance (£12,570 for 2025/26)
- Employee NI: 8% between the primary threshold and upper earnings limit, 2% above
- Employer NI: 15% on earnings above the secondary threshold (£175/week)
- Pension: Percentage of qualifying earnings (between £6,240 and £50,270 for 2025/26)
Store the HMRC thresholds in a separate “rates” tab so you only need to update them once each April when rates change. This keeps your projections maintainable year to year.
Step 5: Run Projections and Scenarios
Use your completed spreadsheet to model hiring scenarios, pay rises, and budget forecasts – adjusting inputs to see how total payroll costs change.
With your spreadsheet built, you can now run projections for different scenarios:
- New hire modelling: Add a row with the proposed salary to see the true cost to company
- Pay rise impact: Adjust gross pay to see how NI and pension contributions increase
- Headcount planning: Project 3, 6, or 12 months ahead by multiplying monthly totals
- Budget comparison: Compare projected vs actual payroll costs each month
For example, hiring someone at £35,000 actually costs around £40,500 once you add employer NI (~£3,700) and employer pension (~£1,800). Your Excel projection makes these hidden costs visible before you commit to the hire.
Excel vs Payroll Software for Projections
Excel works for basic forecasting under 10 employees, but payroll software automates HMRC submissions, RTI, auto-enrolment, and payslips – which Excel cannot do.
| Feature | Excel | Payroll Software |
|---|---|---|
| Cost | Free (with Office) | From £90/year (Moneysoft) |
| HMRC RTI submissions | No – manual filing | Yes – automated |
| Auto-enrolment | No | Yes – built in |
| Payslip generation | Manual | Automated |
| Tax code updates | Manual each April | Automatic |
| Error risk | High (formula errors) | Low (validated) |
| Cost projections | Flexible – build your own | Some include reporting |
| Best for | Budget forecasting, 1–10 employees | Running actual payroll, any size |
Excel is useful for payroll projections (forecasting costs), but it cannot run your actual payroll – you still need software or a bureau for HMRC submissions, RTI, and payslips. Many businesses use Excel for budgeting alongside payroll software for processing.
If you are currently running payroll in Excel and have more than a few employees, it is worth switching to dedicated software. Moneysoft starts at £90/year, and Xero Payroll costs £1.50/employee/month – both handle everything Excel cannot.
When to Move Beyond Excel
Move to payroll software when you hit 5+ employees, need HMRC RTI submissions, or spend more than 2 hours per month on payroll admin.
Excel payroll projections hit their limits when:
- You have 5+ employees – formula errors and manual updates become risky
- You need HMRC compliance – RTI, FPS, and EPS submissions cannot be done via Excel
- Auto-enrolment is required – all UK employers must auto-enrol eligible employees
- You spend more than 2 hours/month – software automates what you are doing manually
- You have variable pay – overtime, commissions, and bonuses are hard to track in spreadsheets
For budget forecasting specifically, many payroll platforms include built-in reporting that replaces the need for a separate Excel sheet. Staffology and Sage Payroll both offer payroll cost reports.
Our Verdict
Excel is a solid free tool for payroll cost projections and hiring budgets, but it cannot replace proper payroll software for HMRC compliance and employee payments.
Excel payroll projections are genuinely useful for budgeting and scenario planning – modelling the true cost of new hires, forecasting annual payroll spend, and comparing headcount scenarios. For very small businesses with 1–5 employees, it can be a practical starting point.
However, Excel cannot handle the compliance side of UK payroll. You still need software or a bureau for HMRC submissions, auto-enrolment, payslips, and year-end reporting. If you are looking for affordable payroll software to complement your Excel budgeting, see our best payroll software UK comparison or our guide to outsourced payroll companies.
