TEMPLATES & BUSINESS WORKFLOWS

Payroll Calculator in Excel: Gross to Net Pay with Overtime, Deductions and Payslips (Free Template)

A payroll spreadsheet has to get three things right every pay day: what each employee earned, what is deducted, and what lands in their bank account. In this guide you will build a payroll calculator in Excel that handles salaried and hourly staff, overtime, capped contributions, an income tax bracket table and employer costs, and prints a payslip for any employee. Every rate lives on one Settings sheet, so you can replace the example rates with the rules that apply to you.

Payroll calculator in Excel showing Ben Cruz with gross pay of 1,761.25, deductions of 298.94 and net pay of 1,462.31, and total net pay of 12,628.53.
Gross pay, deductions and net pay for one employee.

Gross to net pay in one table

LineHow it is calculated
Basic paySalary for the period, or hourly rate x regular hours
Overtime payOvertime hours x hourly rate x overtime multiplier
Gross payBasic pay + overtime pay + allowances
Employee deductionsPension and health contributions (a % with a cap), income tax, other deductions such as loans
Net payGross pay – employee deductions
Employer costGross pay + employer contributions
Use your own rates: The rates, caps and tax table in the template are round example numbers, not any country's official rates. Replace them on the Settings sheet with the rules that apply to your business, and check with your tax office or payroll adviser.
Free download: Get the payroll calculator (.xlsx). It has Settings, Employees, a Payroll register, a Payslip and a How It Works sheet. Works in Excel 2010 and later, Microsoft 365 and Excel for the web.

Step 1: Put every rate on one Settings sheet

Hard-coded rates inside formulas are the most common payroll spreadsheet mistake: when a rate changes, some formulas get updated and some do not. Put every rate in one place instead:

SettingCellExample
Standard hours in the periodB588 (11 working days x 8 hours)
Overtime multiplierB61.25
Employee pension, % and maximumB9:B104.5%, up to 180.00
Employee health insurance, %B112.5%
Employer pension, % and maximumB13:B149.5%, up to 380.00
Employer health insurance, %B152.5%

Below them is the income tax table for one pay period: each row is a bracket with the taxable pay it starts from, the tax on everything below it, and the rate on the excess.

Taxable pay overBase taxRate on excess
0.000.000%
1,000.000.0010%
2,000.00100.0020%
4,000.00500.0030%
Excel payroll settings sheet with 88 standard hours, an overtime multiplier of 1.25, pension, health and employer contribution rates with caps, and an income tax bracket table.
All rates and the tax table in one place.

Step 2: Enter employees and hours

On the Employees sheet, each employee has a pay type (Salaried or Hourly, from a drop-down), a rate, regular hours, overtime hours, a non-taxable allowance and other deductions such as a loan repayment. For hourly staff, the hours often come from a timesheet; see the hours worked formula with breaks. Column H flags regular hours above the standard:

=IF(A5="","",IF(D5>Settings!$B$5,"Check","OK"))
Excel employees sheet with five salaried and hourly employees, their rates, regular and overtime hours, allowances, other deductions and an hours check showing OK.
Salaried and hourly employees on one sheet.
Checkpoint: Ben Cruz is hourly at 18.50, with 80 regular hours and 10 overtime hours. All five employees show OK.

Step 3: Calculate gross pay

The Payroll register works row by row. Overtime needs an hourly rate for everyone, so B5 converts salaries:

=IF(Employees!B5="Salaried",Employees!C5/Settings!$B$5,Employees!C5)

Basic pay in C5 is the salary, or the hourly rate x regular hours. Overtime pay in D5:

=ROUND(Employees!E5*B5*Settings!$B$6,2)

Gross pay in F5 is =C5+D5+E5. All formulas are wrapped in IF($A5="","",...) so empty rows stay blank; if IF is new to you, see how to build an IF formula.

Excel payroll register calculating hourly rate, basic pay, overtime pay, allowance and gross pay, with Ana Reyes earning 204.55 of overtime and 2,704.55 gross.
Basic pay + overtime + allowance = gross pay.
Checkpoint: Ana Reyes earns 2,400.00 per period: 2,400.00 / 88 = 27.2727 per hour, so 6 overtime hours x 27.2727 x 1.25 = 204.55. Gross pay 2,400.00 + 204.55 + 100.00 = 2,704.55.

Step 4: Deductions and income tax

Pension is a percentage of basic + overtime, up to the maximum. MIN does the capping in G5:

=MIN(ROUND((C5+D5)*Settings!$B$9,2),Settings!$B$10)

Health insurance in H5 is =ROUND((C5+D5)*Settings!$B$11,2) with no cap. Taxable pay in I5 is basic + overtime minus both contributions. Then income tax in J5 uses VLOOKUP with an approximate match, which finds the last bracket that is not greater than the taxable pay:

=ROUND(VLOOKUP(I5,Settings!$A$19:$C$22,2,TRUE)+(I5-VLOOKUP(I5,Settings!$A$19:$C$22,1,TRUE))*VLOOKUP(I5,Settings!$A$19:$C$22,3,TRUE),2)

Read it as: base tax for the bracket + (taxable pay – bracket start) x bracket rate. Only the part above each threshold is taxed at the higher rate. The bracket table must be sorted from the lowest threshold up, and the first row must start at 0.

EmployeeTaxable payBracketIncome tax
Ana Reyes2,422.24Over 2,000: 100.00 + 20% of 422.24184.45
Ben Cruz1,591.46Over 1,000: 0.00 + 10% of 591.4659.15
Ella Tan4,890.00Over 4,000: 500.00 + 30% of 890.00767.00
Excel payroll deductions: pension capped at 180.00, health insurance, taxable pay and income tax from a bracket table, with Ana Reyes paying 184.45 of income tax.
Capped contributions and a bracket-based income tax.
Checkpoint: Ella Tan's pension would be 4.5% x 5,200.00 = 234.00, but the cap limits it to 180.00.

Step 5: Net pay, employer cost and checks

Net pay in L5 is gross pay minus every employee deduction:

=F5-G5-H5-J5-K5

Columns M and N add the employer contributions (employer pension is capped the same way with MIN), and column O is the total cost of each employee. Below the register, the checks and payment summary tell you what to pay and to whom:

LineResult
Net pay = gross – deductions (totals)Ties
No negative net payOK
Regular hours within the standardOK
Cash needed for net pay12,628.53
Payable to the tax office1,475.31
Payable to pension and health funds2,665.61

If the pay date falls after the month end, the unpaid wages belong in your accrued expenses schedule.

Excel payroll net pay of 12,628.53 in total, employer pension and health contributions, a total employer cost of 17,139.45 and checks showing Ties and OK.
Net pay, employer cost and the payment summary.
Try it: In Employees!E6 change Ben Cruz's overtime from 10 to 20 hours. Overtime pay doubles to 462.50, gross pay rises to 1,992.50, income tax to 80.65 and net pay to 1,655.88, and the payslip updates. Change it back to 10.

Step 6: Print a payslip for any employee

The Payslip sheet has a drop-down in B3 with the employee names. Every line uses INDEX and MATCH to pull the right row from the register:

=INDEX(Payroll!$C$5:$C$14,MATCH($B$3,Payroll!$A$5:$A$14,0))

Change the column (C for basic pay, D for overtime and so on) for each line. INDEX and MATCH work in every Excel version; in Microsoft 365 you could use XLOOKUP instead (see INDEX MATCH vs XLOOKUP). The last line checks that the payslip net pay agrees with the register.

Excel payslip for Ben Cruz pulled with INDEX and MATCH: gross pay 1,761.25, total deductions 298.94 and net pay 1,462.31, agreeing with the payroll register.
One payslip per employee, straight from the register.
Checkpoint: Ben Cruz: gross pay 1,761.25 – deductions 298.94 (77.01 + 42.78 + 59.15 + 120.00) = net pay 1,462.31.

Payroll spreadsheet tips

  • Save a copy of the workbook for every pay period, so past payslips never change when rates are updated.
  • Lock the formula cells so only the yellow inputs can be edited; see how to lock formula cells.
  • Round each deduction to cents, as the template does, so payslips and bank totals agree exactly.
  • Track leave separately and adjust the hours; an employee leave tracker keeps balances up to date.
  • Review the Settings sheet whenever a rate, cap or tax table changes.

Frequently asked questions

How do I calculate net pay in Excel?

Calculate gross pay (basic + overtime + allowances), subtract each employee deduction (contributions, income tax and other deductions) and the rest is net pay. In the template it is =F5-G5-H5-J5-K5.

How do I calculate overtime pay in Excel?

Multiply overtime hours by the hourly rate and the overtime multiplier, for example =ROUND(E5*B5*1.25,2). For salaried staff, the hourly rate is the salary divided by the standard hours.

How do I calculate income tax with brackets in Excel?

Put the brackets in a table sorted from lowest to highest, then use VLOOKUP with TRUE to find the bracket: base tax + (taxable pay – bracket start) x bracket rate.

How do I cap a deduction at a maximum in Excel?

Wrap the calculation in MIN with the cap: =MIN(ROUND(pay*rate,2),maximum). The result is the calculated amount or the cap, whichever is smaller.

Can I use this payroll calculator for my country?

Yes, if you replace the example rates, caps and tax table on the Settings sheet with your own and check which earnings are taxable. The formulas read everything from Settings.

Final checklist

  • Settings shows this period's rates and tax table.
  • Every employee has a pay type, rate and hours, and Hours check shows OK.
  • No negative net pay, and Net pay = gross – deductions shows Ties.
  • Payslips agree with the register.
  • Cash, tax and contribution payments match the payment summary.
Practice file: Download the payroll calculator. Replace the yellow inputs with your own employees, hours and rates; up to 10 employees are ready.

Run payroll with confidence

Browse the ready-to-use Excel templates, visit the OneXcel Tutorial Hub for step-by-step guides, or turn your payroll costs into a dashboard with the OneXcel Dashboard Builder.