Payroll settlement template for Excel
Payroll settlement template for Excel
On the last working day of the month, a small company's payroll tends to look like this: someone opens last month's file, types over the salaries, adds the column up on a phone calculator and compares the number with the one the accountant mentioned. When the two disagree, the search starts. Nine times out of ten the trouble sits in a single column: a deduction copied from a rate that has since moved, or an allowance folded into a base it never belonged to.
The deeper problem is not arithmetic. It is that payroll is rebuilt from scratch every month on a sheet that does not separate what was typed from what was calculated. When an employee asks why the net went down, nobody can point to the cell where the money was taken. When the accountant asks for the period's backup, out comes a loose file with no employee code, no days and totals that do not agree with each other.
This template goes after that specific gap: one row per employee, earnings split by concept, deductions typed as amounts rather than as rates, and three columns that fill themselves so the net can be checked against the contract instead of against whoever built the sheet.
⬇ Download the template (Excel .xlsx)What it is and who it is for
It is a two-sheet Excel workbook. The payroll sheet carries a header block with company, period and payment date, then the settlement table with one row per employee, then a totals row that adds days, earnings, deductions and net. The second sheet walks through the procedure in plain text, explains how contributions are handled and carries the disclaimer: this is an internal-control tool, not an official accounting or tax document.
It is built for anyone settling payroll from a handful of people up to a few dozen, all paid by the same employer: the owner of a hardware store paying six, the manager of a warehouse with twenty operators and two supervisors, the outside accountant who receives headcount information and turns it into net figures. It also suits a business with no software yet that wants to know, before buying any, which data a pay period actually requires.
What it is not, plainly: it does not compute contributions, it does not replace the official settlement, and it ships without rates. That omission is deliberate and is explained where the calculation is.
What the template includes
| Block | What it holds | What it is for |
|---|---|---|
| Payroll sheet, header | Company, period and payment date | Fixes the cut-off and keeps months apart |
| Payroll sheet, table | One row per employee, from earnings to net | The settlement itself |
| Payroll sheet, totals row | Sum of every numeric column | Period check at a glance |
| Grey columns | Gross pay, total deductions and net pay | Calculated; never typed by hand |
| Instructions sheet | Procedure, contribution note and disclaimer | Lets someone else close the period without calling you |
The columns, one at a time
Fifteen columns, filled in from left to right. The first four identify the person and the period; the next five are earnings; the three after that are deductions; the final three are results and compute themselves.
| Column | What you type | What is calculated |
|---|---|---|
| Code | Internal employee code, stable over time | Nothing; it is the key that exposes duplicate rows |
| Employee | Full name | Nothing |
| Position | Position as stated in the contract | Nothing |
| Days | Days settled in the period | Nothing; the sheet does not prorate on its own |
| Base salary | Salary for the period, allowance excluded | Nothing |
| Allowance | Allowance amount where it applies | Nothing; kept in its own column on purpose |
| Overtime | Settled overtime amount | Nothing |
| Commissions | Commissions earned in the period | Nothing |
| Other earnings | Bonuses, shift premiums and similar items | Nothing |
| Gross pay | Leave it empty | Sum of the five earnings columns |
| Health | Employee's health contribution amount | Nothing; the amount goes in, not the rate |
| Pension | Employee's pension contribution amount | Nothing; the amount goes in, not the rate |
| Other deductions | Loans, garnishments and authorized deductions | Nothing |
| Total deductions | Leave it empty | Health plus pension plus other deductions |
| Net pay | Leave it empty | Gross pay minus total deductions |
How the calculation runs, figure by figure
The example shipped inside the sheet has two employees and is there to test the three formulas. The figures are the ones in the file, so they can be followed on screen without opening anything else.
| Concept | E-01 | E-02 | Period total |
|---|---|---|---|
| Base salary | 1,800,000 | 2,400,000 | — |
| Allowance | 200,000 | — | — |
| Overtime | — | 120,000 | — |
| Commissions | 150,000 | — | — |
| Other earnings | — | 50,000 | — |
| Gross pay | 2,150,000 | 2,570,000 | 4,720,000 |
| Health, pension and other | 158,000 | 212,000 | 370,000 |
| Net pay | 1,992,000 | 2,358,000 | 4,350,000 |
E-01 earns 1,800,000 in base salary, plus 200,000 in allowance, plus 150,000 in commissions. Gross pay comes to 2,150,000. Deductions for that person add up to 158,000 across health, pension and other items, which leaves a net of 1,992,000. It is a plain subtraction, and it is exactly the subtraction that breaks when someone merges the allowance into the base and then applies the deduction to the whole.
E-02 has neither allowance nor commissions: 2,400,000 in base salary, 120,000 in overtime and 50,000 in other earnings, which gives 2,570,000. Deductions total 212,000, so the net is 2,358,000.
The totals row is not decoration; it is the proof that the period closes. The two gross figures add to 4,720,000, the two deduction totals to 370,000, the two nets to 4,350,000. And the final check is the same in any sheet: gross pay minus total deductions must equal the sum of net pay exactly. If it does not, a value has been typed over a calculated column, or an empty row has been pulled into the sum.
Now the design decision that raises the most questions. The health and pension columns ask for an amount, not a rate. Two reasons. First, the rate is set by current rules and it moves: if it lived inside the template, an old file would keep calculating with the rate of its own year and nobody would notice until a complaint arrived. Second, the base is not the same for everyone, because two people on the same salary can carry different deductions after sick leave, unpaid leave or contribution ceilings. Typing the settled amount leaves the figure recorded and auditable; the rate lives outside the file, in whatever document applies, and whoever settles payroll looks it up before typing. That is why the column asks for an amount: it is an instruction, not a limitation of the sheet.
Step by step
- Download the file and save a copy with the period in the name before typing anything. The original then stays clean for next month.
- Fill the header with company, period and payment date. Three fields, but they are the ones that answer "which month is this?" six weeks later.
- One row per employee, starting with the internal code. Use the code, not the name, as the identifier: two people can share a name and one person can hold two contracts.
- Enter the days settled, then each earning in its own column. The allowance does not go into base salary; the columns are separate on purpose.
- Type the deductions as amounts, exactly as they were settled for that person. If a concept does not apply, leave a zero instead of an empty cell.
- Check the three grey columns row by row, then the totals row. Gross minus deductions must equal net, in every row and in the sheet as a whole.
- Save with the period and payment date in the file name, and hand it over together with the month's change log. Without that log, the net cannot be explained.
Mistakes that keep coming back
- Merging the allowance into base salary to save a column. Gross pay still looks right, but deductions land on an inflated base and the net comes out short.
- Typing a rate into the health or pension column. The cell accepts any number, so nothing warns you: it shows up later as an absurd or laughably small deduction.
- Leaving the days column blank. Without days the net cannot be matched to the contract, and it cannot be prorated for someone who joined or left mid-month.
- Identifying people by name. It is the usual route to paying the same row twice and finding out when the employee calls.
- Deleting or inserting rows inside the table block. The totals row stops covering the full range and the period check fails without a sound.
- Copying last month's file and dragging the values along. Fixed earnings get updated, commissions and overtime do not: the old month's figures stay behind.
- Treating net pay as the cost of the employee. Net pay is what the person receives; the cost also includes what the employer contributes and never appears on this sheet.
When software starts to make sense
Worth saying plainly: for a small company with few employees, a single employer and an outside accountant who reviews every month, this sheet is faster and cheaper than any system. The file can be read, audited and fixed without a support ticket.
The switch is justified by concrete signals: changes every month (sick leave, unpaid leave, hires and exits mid-period), several cost centres or locations, batch bank transfers, two people working in the same file, or the need to rebuild a period closed a year ago. At that point Excel starts costing hours and the error stops being arithmetic and becomes one of control. Kardex Tauro belongs in that scenario when payroll has to talk to labour cost per warehouse and to inventory; where that conversation does not exist, there is no reason to move yet.
The screenshot shows the sheet as it looks once the example is typed in: the two rows with their codes, earnings spread across their columns and the three grey columns already resolved by the formula, with the totals row underneath. Before using it with real data, look at two things: that each net matches the one produced by the settlement the accountant delivered, and that the totals row adds up to exactly what a manual sum of the visible rows gives.
Closing the period without surprises
An orderly payroll does not remove every problem, but it leaves a trail: what was typed, what was calculated and which figures closed the month. This template covers that part and leaves out the one that is not its job, which is setting rates. The rest — days, changes and settled amounts — remains company information, and it is the only part worth guarding.
Download the file, save it with the period in the name and fill it in once. Next month gets copied and updated, not rebuilt.
⬇ Download the template (Excel .xlsx)
