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.

Gross to net pay in one table
| Line | How it is calculated |
|---|---|
| Basic pay | Salary for the period, or hourly rate x regular hours |
| Overtime pay | Overtime hours x hourly rate x overtime multiplier |
| Gross pay | Basic pay + overtime pay + allowances |
| Employee deductions | Pension and health contributions (a % with a cap), income tax, other deductions such as loans |
| Net pay | Gross pay – employee deductions |
| Employer cost | Gross pay + employer contributions |
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:
| Setting | Cell | Example |
|---|---|---|
| Standard hours in the period | B5 | 88 (11 working days x 8 hours) |
| Overtime multiplier | B6 | 1.25 |
| Employee pension, % and maximum | B9:B10 | 4.5%, up to 180.00 |
| Employee health insurance, % | B11 | 2.5% |
| Employer pension, % and maximum | B13:B14 | 9.5%, up to 380.00 |
| Employer health insurance, % | B15 | 2.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 over | Base tax | Rate on excess |
|---|---|---|
| 0.00 | 0.00 | 0% |
| 1,000.00 | 0.00 | 10% |
| 2,000.00 | 100.00 | 20% |
| 4,000.00 | 500.00 | 30% |

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"))

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.

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.
| Employee | Taxable pay | Bracket | Income tax |
|---|---|---|---|
| Ana Reyes | 2,422.24 | Over 2,000: 100.00 + 20% of 422.24 | 184.45 |
| Ben Cruz | 1,591.46 | Over 1,000: 0.00 + 10% of 591.46 | 59.15 |
| Ella Tan | 4,890.00 | Over 4,000: 500.00 + 30% of 890.00 | 767.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:
| Line | Result |
|---|---|
| Net pay = gross – deductions (totals) | Ties |
| No negative net pay | OK |
| Regular hours within the standard | OK |
| Cash needed for net pay | 12,628.53 |
| Payable to the tax office | 1,475.31 |
| Payable to pension and health funds | 2,665.61 |
If the pay date falls after the month end, the unpaid wages belong in your accrued expenses schedule.

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.

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.
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.
