Statutory employee benefits template for Excel
Statutory employee benefits template for Excel
Someone hands in their notice on a Tuesday morning, and the first question is not about the handover. It is how much the company owes them: severance, the interest that has built up on it, the bonus already accrued and the vacation days they never took. If that answer takes three days and two phone calls, the arithmetic was never the problem.
What fails is almost always the setup. A total base that left out the allowance. A period settled on a different day count from the one payroll uses. A name missing because the person joined mid-month and nobody added a row. By the time the accountant asks for the working papers, the sheet shows a number but no trace of how it got there, and the file gets rebuilt by hand at the worst possible moment.
This template works the other way round. The parameters sit at the top where anyone can see them, each employee gets one row, and all four items are calculated from the same total base, so the figure you hand over and the figure you explain are the same figure.
⬇ Download the template (Excel .xlsx)What this format is and who it is for
It is a benefits settlement sheet: one row per employee, with the hire date, the cut-off date of the period being settled, the days worked in that period and the salary the calculation starts from. Above the table, the header holds three parameters that apply to the whole sheet: the days in the year used for proration, the severance interest rate — which can stay at zero when that cut-off does not settle interest — and the vacation days granted per year.
The days worked column drives everything. If the cut-off is the year end, the figure will be the days in the year; if the person started in July, it will be the days they actually worked. Everything else is prorated on that number, which means an employee who joined halfway through a period needs no separate sheet and no manual adjustment afterwards.
It is built for the small business that settles anywhere from a handful of people to a few hundred and has no full payroll module: the owner who wants to know what a departure will cost before signing it, the HR generalist who has to hand the working papers to the accountant, and the accountant who receives the sheet and needs to see at a glance that the base and the day count are right.
What the sheet contains
| Block | What it holds | What it is for |
|---|---|---|
| Settlement sheet | Parameter header plus a table with one row per employee, twelve data columns and a totals row at the foot | This is the sheet that gets printed and filed as support |
| Parameter header | Company, period, days in the year, severance interest rate and vacation days per year | Changing one parameter recalculates every row at once |
| Calculated columns | Total base, severance, interest, bonus, vacation pay and total per employee | They fill themselves as soon as there is a salary and a day count |
| Totals row | Sum of days, salaries, allowances and each benefit separately | Gives the cost of the cut-off in a single line |
| Spare rows | Extra rows with the formulas already in place, ready for new hires | Stops people from copying formulas by hand and breaking the structure |
| Instructions sheet | What it settles, how each item is calculated, the worked example and the usual mistakes | Read by whoever opens the file months later |
The columns, one by one
One idea before the table saves a lot of argument later: the columns fall into two groups. The first ones you type into; the rest calculate. If someone types over a calculated column to make a figure tie out, the sheet stops being evidence, because the result can no longer be reproduced or explained.
| Column | What you type | What is calculated |
|---|---|---|
| Employee | Full name, spelled as it appears in payroll | Nothing: it is the key of the row |
| Hire date | The actual start date | Nothing: it is there to check the days worked |
| Cut-off date | The last day of the period being settled | Nothing: it sets how far the calculation runs |
| Days worked | The days in that period, from the hire date or from the previous cut-off | Nothing: it is the divisor that prorates everything else |
| Base salary | The monthly salary the settlement is based on | Nothing |
| Allowance | The allowance that forms part of the base; leave it at zero if none applies | Nothing |
| Total base | — | Salary plus allowance |
| Severance | — | Total base times days worked, divided by the days in the year |
| Severance interest | — | Severance times the header rate times days worked, divided by the days in the year |
| Bonus | — | Total base times days worked, divided by the days in the year |
| Vacation days pending | The days still owed, with days already taken deducted | Nothing |
| Vacation pay | — | Total base divided by thirty, multiplied by the pending days |
| Total benefits | — | Severance plus interest plus bonus plus vacation pay |
How the calculation works, figure by figure
The example shipped with the sheet uses three header parameters: days in the year at 360, the severance interest rate at zero, and fifteen vacation days per year. With those in place, every row resolves in one multiplication and one division, with no hidden helper cells and no intermediate steps to reconstruct.
Ana worked 180 days and her total base — salary plus allowance — is 2,100,000. Severance is 2,100,000 times 180, divided by 360: 1,050,000. The bonus runs exactly the same calculation and comes out at another 1,050,000, which is normal when both items accrue over the same period. Interest stays at zero because the header rate is at zero. Vacation is a daily rate: 2,100,000 divided by 30 gives 70,000 a day, and 7.5 pending days come to 525,000. The row total is 2,625,000.
Carlos worked the full year, 360 days, on a total base of 1,600,000. His severance is 1,600,000 times 360 over 360, so 1,600,000, and his bonus the same again. He has 15 vacation days pending, so the day rate — 1,600,000 divided by 30, or 53,333.33 — times 15 gives 800,000. His total is 4,000,000. Add the two rows and the sheet closes at 6,625,000.
| Item | Ana | Carlos | How it is obtained |
|---|---|---|---|
| Days worked | 180 | 360 | Hire date to cut-off date |
| Total base | 2,100,000 | 1,600,000 | Salary plus allowance |
| Severance | 1,050,000 | 1,600,000 | Base times days, over days in the year |
| Severance interest | 0 | 0 | Severance times the header rate |
| Bonus | 1,050,000 | 1,600,000 | Same as severance |
| Vacation days pending | 7.5 | 15 | Typed in, days taken already deducted |
| Vacation pay | 525,000 | 800,000 | Base over 30, times pending days |
| Total benefits | 2,625,000 | 4,000,000 | Sum of the four items |
Two points are worth pausing on. The first is that the interest rate is not part of the example figures: type the value that current regulation sets into the header and the interest column stops being zero, lifting every row's total by the same proportion. The second is the divisor. The 360 is not decoration; swap it for another number in a partial period and the result stops matching payroll, usually right after the papers have already been signed.
Step by step
- Open the sheet and type the company and the period being settled into the header.
- Confirm the three parameters: days in the year, severance interest rate and vacation days per year. If the cut-off does not settle interest, the rate stays at zero and is not touched again.
- Fill one row per employee with name, hire date, cut-off date, days worked, base salary and allowance. If someone has no allowance, leave the cell at zero instead of clearing it.
- Check the days worked against the two dates before going any further. The gap between hire date and cut-off is the figure most often carried over wrong from a previous period's file.
- Verify the total base on each row: salary plus allowance, with no deductions and no overtime, because those were already settled in that period's payroll.
- Enter the vacation days still pending, with days already taken deducted. Type the accrued figure by mistake and the employee is paid twice for the same days.
- Read the totals row and compare total benefits against what has already been accrued in the accounts, which is how missing or duplicated rows surface.
Mistakes that show up often
- Changing the days in the year partway through: the numbers stop matching payroll and nobody can tell which row was the first to drift.
- Leaving the allowance blank because it is "not salary". The total base adds it, and without it both severance and bonus come out short.
- Using a different interest rate per employee. The parameter is single for the sheet: two rates means two sheets.
- Forgetting vacation already taken, the most common way to overpay someone who is leaving.
- Typing over the calculated columns to force a figure to tie out. The number survives; the audit trail does not.
- Settling the same period twice: the cut-off moves forward without the previous file being saved, and the row reappears a month later.
When to move to software
The template holds up while the staff sits in one list and there are only a few cut-offs a year. It stops holding up when you need history per person — every departure, every raise, every period already accrued — when several cost centres settle separately, or when three people open the same file and nobody knows which copy is current. At that point the problem is no longer the formula. It is change control, and a spreadsheet does not solve that.
Until then, the sheet does one thing many systems make surprisingly hard: it shows the whole calculation on one screen, parameters at the top, so whoever signs can follow the arithmetic with a finger. That is the honest use of a format like this, and it is why Kardex Tauro publishes it openly: it is there to get the cut-off in order before anyone decides a bigger tool is needed.
The sheet as it looks
The screenshot shows the full sheet with the two example rows. Look at three things: the header with the parameters, which is where the divisor and the rate change; the total base column, which should match salary plus allowance for each person; and the totals row at the foot, which summarises days, bases and benefits for the cut-off. If those three areas are in order when the file opens, the rest is just filling rows.
One last piece of housekeeping: save a copy per cut-off with the period in the file name, and do not reuse the same sheet for the following month. Then, when somebody asks how a figure was reached six months ago, the answer is in the file rather than in the memory of whoever ran the numbers.
⬇ Download the template (Excel .xlsx)
