A simple wage calculator in Excel is one of the most useful tools a small employer can build. It turns hours and a pay rate into gross pay in seconds, and with a couple of extra rows it can give you a rough idea of take-home pay too. In this guide I will show you how to build one, while being clear about where Excel ends and proper payroll software begins.
One honest caveat first: real UK payroll involves PAYE tax codes, National Insurance, pensions and Real Time Information reporting to HMRC. A spreadsheet is brilliant for working out gross pay and giving an estimate, but the official deduction figures should come from HMRC-recognised payroll software. Treat this calculator as a planning aid. I want to be straight with you about that from the outset, because there is a lot of well-meaning but risky advice online suggesting you can run full payroll in a spreadsheet. You can model it; you should not file it.
So why build one at all? Because gross pay, overtime, and a rough sense of the total wage bill are exactly the things you need for budgeting, quoting jobs and cash flow planning, and those do not require HMRC-grade accuracy. Knowing roughly what a new hire will cost you, or what this month’s wage run adds up to, is hugely useful when you are making decisions. The calculator below gives you that quickly, and it doubles as a clear record of hours that you can hand to whoever runs your formal payroll.
Step 1: Set up the inputs
Start with the figures you control: hours worked and the hourly rate. Keep them as plain input cells you can change.
| Employee | Hours | Rate (£) | Gross (£) |
|---|---|---|---|
| A. Smith | 37.5 | 13.50 | 506.25 |
| B. Jones | 30.0 | 12.00 | 360.00 |
| C. Patel | 40.0 | 15.00 | 600.00 |
Step 2: Work out gross pay
Gross pay is just hours times rate. With hours in column B and rate in column C, the gross in column D is =B2*C2. If you pay overtime at time and a half beyond a set number of hours, you can split it: =MIN(B2,37.5)*C2+MAX(0,B2-37.5)*C2*1.5. The first part pays normal rate up to 37.5 hours, the second pays the overtime premium on anything above. Adjust the 37.5 to match your standard working week, and change the 1.5 if your overtime premium is different, such as 2 for a double-time bank holiday rate.
It is worth adding a check against the National Minimum Wage, since paying below the legal rate for someone’s age band is a serious matter. A simple flag such as =IF(C2<11.44,"CHECK RATE","") warns you if an hourly rate looks too low, using whichever minimum applies at the time. Treat the figure as a prompt to verify against the current published rates rather than a definitive ruling, because the minimum changes and varies by age and apprenticeship status.
Step 3: Add a rough tax and NI estimate
For a planning estimate only, you can apply a flat percentage to approximate deductions. For example, =D2*0.2 for a rough income tax slice and =D2*0.08 for a National Insurance slice. I cannot stress enough that these are estimates: real PAYE depends on the employee's tax code and the cumulative position across the year. Use them to budget, not to pay people.
Step 4: Estimate take-home pay
Take-home is gross minus your estimated deductions. With gross in D2, estimated tax in E2 and estimated NI in F2, use =D2-E2-F2. Round it to pennies with =ROUND(D2-E2-F2,2) so you never show fractions of a penny on a payslip mock-up.
Be careful how you present this number to an employee. Because it is only an estimate, label the column clearly as "estimated take-home" rather than implying it is the figure that will hit their bank account. The real amount comes from their tax code and year-to-date position, which only your payroll software knows. I have seen well-intentioned owners cause unnecessary worry by quoting a spreadsheet estimate as gospel, so a clear label and a quick word of caution save a lot of awkward conversations.
Where the spreadsheet ends and software begins
It helps to be clear about the dividing line so you stay on the right side of it. Your Excel calculator is the right tool for working out gross pay, modelling overtime, comparing scenarios and keeping a clean record of hours. The moment you need to deduct the correct PAYE and National Insurance, handle a workplace pension, produce a compliant payslip, or report to HMRC under Real Time Information, you have crossed into territory that needs HMRC-recognised payroll software. Many such tools are free or low cost for small employers, and several integrate happily with a spreadsheet so you can do your planning in Excel and your filing in the software. Think of the two as partners rather than rivals: the spreadsheet asks the cheap, fast "what if" questions, and the software gives the legally correct answers when it matters.
Step 5: Total the wage bill
At the foot of the gross column, =SUM(D2:D20) gives your total gross wage bill for the run, which is the figure you want for your cash flow forecast template. Knowing the wage bill a week ahead is one of the best reasons to keep this sheet up to date. Remember that your true cost as an employer is higher than gross pay, because you also pay employer's National Insurance and, in most cases, pension contributions on top. A rough rule of thumb is to budget a little above gross to cover those, then confirm the exact figures from your payroll software.
Used this way, the calculator becomes a planning tool you will reach for often. Try a new employee's hours and rate to see the effect on your wage bill before you commit. Model a busy week with overtime to check your cash flow can take it. None of this replaces proper payroll, but it answers the questions that proper payroll software is too slow to ask casually, and that is exactly what a good Excel model is for.
Common mistakes
- Treating estimates as real deductions. Flat percentages are for planning. Use HMRC-recognised payroll software for actual pay and RTI filing.
- Forgetting the National Minimum Wage. Always check your rates meet the legal minimum for the employee's age band.
- Mishandling overtime. If you pay a premium, split normal and overtime hours rather than applying one rate to everything.
- Rounding too early. Round only the final figures, not the inputs, to avoid drift across many rows.
Frequently asked questions
Can I run UK payroll entirely in Excel?
Not for the official side. Excel is great for working out gross pay and estimates, but PAYE, National Insurance and pension deductions, plus Real Time Information reporting, must go through HMRC-recognised payroll software.
How do I calculate gross pay in Excel?
Multiply hours by the hourly rate: =Hours*Rate. For overtime, split the hours and apply the premium to the overtime portion only.
How do I add overtime at time and a half?
Use a formula like =MIN(Hours,37.5)*Rate+MAX(0,Hours-37.5)*Rate*1.5, adjusting 37.5 to your standard week.
Is my take-home estimate accurate?
It is a rough guide only. Actual take-home depends on the employee's tax code and the year-to-date position, which only proper payroll software calculates correctly.
