Final settlement template in Excel
Final settlement template in Excel
Somebody hands in their notice on a Monday, works out the month, and on the last Friday a number has to be on the table: what the company owes them for leaving. That number gets challenged almost every time, not out of bad faith but because nobody wrote it down before the meeting. A figure that first appears on the back of an envelope, with a handwritten sum and a scrap of paper about holidays beside it, is very hard to defend.
Three things cause most of the trouble. Accrued benefits depend on the days worked, and nobody remembers whether the year is counted as 360 days or as the full calendar. Pending holiday depends on how many days were taken and how many are left, and that balance usually lives in one person's head. And the salary for the final days gets skipped altogether, because a departure feels like a closing entry rather than a short payroll run.
⬇ Download the template (.xlsx)
This template puts all three on one line. Each employee takes a row of fifteen columns: name, dates, days worked, salary, allowance, total base, severance, interest, bonus, holiday days, holiday pay, days of salary due, salary due, and the total to pay. You type the inputs and the sheet does the arithmetic. Three parameters at the top drive every row, which is what makes the file reusable month after month instead of something rebuilt from scratch each time.
What it handles and what it leaves out
It is the document that closes an employment contract: one row per person, every item broken down, and a total. It is not a payroll run. There are no health or pension deductions here, no withholding, and no employer contributions. What it shows is what the employee is owed, not everything the company has to provide for, and mixing those two up accounts for a good share of the arguments that happen at year end.
It works for resignation, mutual agreement, termination without cause or a fixed term running out: the reason goes in the header and stays as context for the file, but it changes no formula. Nor does the sheet decide whether compensation applies. If it does, add it as a new item with its own column or its own row and include it in the total; what you should not do is hide it inside a column that already means something else.
It is for the HR person who runs four settlements a year and does not want to rebuild the file each time; for the accountant who receives the sheet and needs to see where every amount comes from; for the owner of a small company with no HR function, who ends up doing this on a Saturday afternoon; and for the employee too, who can ask for the breakdown and read it item by item instead of accepting a single figure with no explanation.
What the template contains
The file has two sheets and a single working table. The first is called Settlement and it is where you type; the second is called Instructions and it is what keeps the file from depending on whoever built it. That second sheet looks like decoration until the day the person who used to run the settlements goes on holiday.
| Block | What it holds | What it is for |
|---|---|---|
| Header | Company and reason for leaving | Identifying the case before printing or filing |
| Sheet parameters | Days in the year (360), severance interest rate (0) and vacation days per year (15) | Three cells that drive every row at once |
| Employee table | Three rows of fifteen columns, one left blank with the formulas in place | One row per person being settled |
| Total to pay column | Severance plus interest, bonus, holiday pay and salary due | The number handed to the employee |
| Closing row | The sum of the Total to pay column | The cost of the period's batch of departures |
| Instructions sheet | What it calculates, the parameters, the formulas, the example and the errors to avoid | So the template makes sense without a phone call |
The fifteen columns, one at a time
Eight columns are typed and seven are calculated. The distinction is not cosmetic: type a value over a cell that holds a formula and the formula is gone, so that row stops responding to the parameters. Split the work from the start, the same way you would with any shared sheet: whoever enters the data never touches the calculated columns, and whoever reviews never corrects a figure by hand.
| Column | What goes in it | Typed or calculated |
|---|---|---|
| Employee | Full name | Typed; it is the row label |
| Hire date | The day the contract started | Typed; used to check the days worked |
| Leaving date | The last day worked | Typed; it closes the period |
| Days worked | The days covered by the contract | Typed; it is the divisor for every benefit |
| Base salary | The employee's monthly salary | Typed; it feeds the total base |
| Allowance | The monthly allowance if there is one, zero if not | Typed; it is added to the base |
| Total base | Salary plus allowance | Calculated |
| Severance | Base times days worked, divided by the days in the year | Calculated |
| Interest | Severance times the rate, times days worked, over days in the year | Calculated; it stays at zero when the rate is zero |
| Bonus | Base times days worked, divided by the days in the year | Calculated |
| Vacation days | The days still pending, after subtracting those already taken | Typed; it feeds the holiday pay |
| Vacation pay | Base divided by 30, multiplied by the pending days | Calculated |
| Days of salary due | The days of the last period still unpaid | Typed; it feeds the salary due |
| Salary due | Base divided by 30, multiplied by those days | Calculated |
| Total to pay | Severance plus interest, bonus, holiday pay and salary due | Calculated |
How it is calculated, using the example in the file
The file ships with two settlements already filled in. The first is Ana Maria Ruiz, who worked 360 days on a base of 1,800,000 and leaves on 31 December. The second is Carlos Pena, who started on the first of July and worked 180 days on a base of 1,200,000. Those two rows explain every calculated column, with no need to invent a case.
| Item | Ana Maria Ruiz | Carlos Pena | How it is obtained |
|---|---|---|---|
| Days worked | 360 | 180 | Between the hire date and the leaving date |
| Total base | 1,800,000 | 1,200,000 | Salary plus allowance; the allowance is zero in both cases |
| Severance | 1,800,000 | 600,000 | Base times days worked, divided by 360 |
| Interest | 0 | 0 | The rate is zero, so the item comes out at zero |
| Bonus | 1,800,000 | 600,000 | Same as severance: base times days over days in the year |
| Vacation days | 7.5 | 0 | Pending after subtracting the days already taken |
| Vacation pay | 450,000 | 0 | 1,800,000 divided by 30, times 7.5 days |
| Days of salary due | 15 | 0 | Days of the last period left unpaid |
| Salary due | 900,000 | 0 | 1,800,000 divided by 30, times 15 days |
| Total to pay | 4,950,000 | 1,200,000 | The five items added together |
| Sheet total | 6,150,000 | 4,950,000 plus 1,200,000 |
Severance: the base of 1,800,000 times 360 days, divided by the 360 days in the year, gives back the full base. The bonus runs exactly the same expression and lands on the same figure, which is correct and is also why people often think the sheet is counting something twice. It is not: they are two different benefits that happen to coincide when the year is complete. Interest is severance times the rate times days worked over days in the year; with the rate at zero the column reads 0, and it is better to leave it that way when the item is not being settled, because the formula stays in place for the case where it is.
Holiday pay: 1,800,000 divided by 30 gives 60,000 a day, and 7.5 days give 450,000. The divisor of 30 is how the sheet turns a monthly salary into a daily value, which is why the days must be entered in the same unit: whole days, not weeks or half months. Salary due repeats the operation with a different day count: the same 60,000 times 15 days gives 900,000. The total adds 1,800,000 of severance, 1,800,000 of bonus, 450,000 of holiday pay and 900,000 of salary, with interest at zero, and arrives at 4,950,000.
The order of the holiday calculation is where most mistakes happen. Subtract the days already taken from what the employee is entitled to first, and only then type the result into the vacation days column. Type the gross accrual and the sheet pays for days that were already enjoyed; type a balance nobody computed and there is no way to reconstruct where it came from. What goes into the column is the pending figure, not the historical one.
Carlos shows the other half: 600,000 of severance and 600,000 of bonus add up to 1,200,000, with holiday pay and salary due at zero because no days were left of either. The sheet cannot tell a zero that means nothing is owed from a zero that means somebody forgot to type it, so that zero gets checked, not assumed. The closing row adds 4,950,000 and 1,200,000 and gives 6,150,000, the cost of the batch: that figure goes into the month's cash flow and will not match what any single employee receives, because every employee has a row of their own.
Step by step
- Open the file and fill in the company name and the reason for leaving in the header. The reason is context: it changes no formula, but it documents the file for whoever reviews it later.
- Check the three parameters before touching the table: days in the year, severance interest rate and vacation days per year. They belong to the sheet, not to the row, and a change there moves every settlement at once.
- Type the name, the dates and the days worked. The sheet does not derive the days from the dates, so if you would rather not count them by hand, count them elsewhere and type the result.
- Enter the base salary and the allowance. The total base builds itself, and if there is no allowance you type zero rather than leaving the cell blank.
- Subtract the holiday already taken and type only the pending days in their column. After that the holiday pay column fills itself in.
- Type the days of salary still owed for the last period. If the leaving date is the final day of the pay period, this value is zero.
- Read the Total to pay column and the closing row. Save a copy named after the employee and the date, and leave the used row in place as a record instead of deleting it.
Mistakes that keep coming back
- Copying the total accrued holiday instead of the pending balance. It is the most expensive of the seven, because it pays for days already enjoyed and nobody notices until the employee says so.
- Changing the parameters from one row to the next. All three belong to the sheet: change one and it changes for everybody, and a sheet running on two different criteria cannot be audited.
- Leaving the allowance blank instead of typing zero. The base still calculates, but the row total is incomplete and the error carries into the sheet total.
- Typing over total base, severance, interest, bonus, holiday pay, salary due or the total. Doing so wipes the formula and that row stops responding to the parameters.
- Forgetting the salary for the last period, especially when the departure falls mid-month and the payment had already gone out for the full period.
- Counting days worked on a different basis in each row: one at 360, another at the real calendar days. The comparison between employees is lost even when every formula is correct.
- Deleting the row once it has been paid. The sheet is also the record of the departure: delete it and the supporting detail disappears, and the sheet total no longer matches what went out.
Which program opens it
It is an Excel file and it opens in Microsoft Excel on Windows, on Mac or in the web version, in LibreOffice Calc and in Google Sheets. The formulas it carries are conditional and rounded to two decimals, and all three applications understand them. What changes is presentation: in a Spanish-language regional setting the decimal separator is a comma, which is why 7.5 days shows as 7,5, while an English setting shows the same value as 7.5 with no change to the arithmetic. The only real precaution is not to convert the sheet to another format when saving, because the formulas get replaced by values and the file stops recalculating. If you need to share it, export a PDF copy and keep the original intact.
The sheet holds up well in a company with a handful of departures and one person doing the maths. It starts to fall short when the holiday balance lives somewhere else and has to be hunted down every time somebody resigns, or when HR needs the departure recorded once so that payroll, the provision and the report all come from the same place. Kardex Tauro covers that part: it keeps each employee's movements and balance, so the settlement stops depending on someone remembering how many days were taken. If that problem has not shown up yet, the template is enough and there is no reason to complicate it.
The screenshot shows the Settlement sheet with the example loaded: the header with the company and the reason for leaving, the three parameters on the second row, and the two employee rows across their fifteen columns. Ana Maria Ruiz, at 360 days, shows 4,950,000 in the total; Carlos Pena, at 180 days, shows 1,200,000. Below them the closing row carries the sheet total to 6,150,000. Two things are worth checking in the image: that the interest column reads zero because of the rate and not because somebody cleared the cell, and that the next row is empty but already holds the formulas, ready for the following departure.
A final settlement is not paperwork that can wait until the last day. With fifteen columns, three parameters and a row per employee, the file turns an awkward conversation into a sum you can show, explain and file away. What gets paid is still a decision for the company; what changes is that the figure stops being an opinion and starts having a trail anyone can check line by line.
