Vacation payout calculator template for Excel
Vacation payout calculator template for Excel
Almost every small business has a file somewhere —a notebook, a shared folder, a long chat thread— where somebody wrote down who went on holiday and when they came back. It works while the team is small and everyone remembers. It stops working the day an employee hands in their notice and you have to put a number on the days they never took.
The hard part is not the addition. Holiday is not earned “per month” and it is not paid “for whatever is left of the year”: it builds up day by day while the person works, it gets used up when they take it, and it is valued at the salary in force on the cut-off date. Three separate clocks that Excel can reconcile and memory cannot.
This template handles the last part of the problem: the payout. It does not schedule shifts and it does not build the department calendar; it takes the days each person still has pending and turns them into an amount to pay, with the cut-off date written into the header so the snapshot stays frozen and can be reopened months later when somebody asks where the figure came from.
⬇ Download the template (Excel .xlsx)
What the format is and who it is for
It is a single-sheet Excel workbook built to settle holiday for a list of employees as at a given date. The header holds the parameters that apply to the whole sheet: the cut-off date, the days earned for every 360 days worked, and the month-length convention used to arrive at the day rate. Below that, each row is one person. In the footer, the group total. No hidden tabs and no macros: what you see is everything it does.
It fits the owner of a workshop who runs payroll alone, the accountant at a twenty-person company, and the HR assistant who fields “how much do you owe me if I never take them?” twice a week. You do not need to know any formulas: you type four items per employee and everything else calculates. What you do need is to understand what the sheet is actually calculating, which is what follows.
What the template includes
The sheet is arranged in four blocks. This table sums up what sits in each one and what it is for when you settle.
| Block | What it holds | What it is for |
|---|---|---|
| Header | Cut-off date plus editable accrual and day-rate parameters | Freezes the snapshot and lets you repeat the calculation at another date without touching formulas |
| Employee data | Name, hire date, salary and days taken | The only things typed in by hand each time |
| Calculated columns | Days worked, days accrued, days pending, day rate and amount to pay | Carry out the settlement row by row |
| Footer and notes | Group total and one free-text note per employee | Close the report and explain the odd cases |
Date and currency formats are already applied, so nobody has to fight with the thousands separator or the day-month order. The notes column exists because in practice there is always a case that does not fit: a rehire, a day taken that nobody recorded in time, a leave of absence in the middle of the period.
The columns, one at a time
There are eleven columns and only four are typed by hand. The rest carry formulas and should stay blank until a row has complete data: a half-filled row produces an amount to pay that looks valid and is not.
| Column | What you type | What it calculates |
|---|---|---|
| Employee | The person's name or code | — |
| Hire date | The day the current employment started | — |
| Cut-off date | Comes from the header | Pulls the header value down |
| Days worked | — | Days between hire date and cut-off date |
| Days accrued | — | Days worked times the accrual parameter |
| Days taken | Days actually taken and recorded | — |
| Days pending | — | Days accrued minus days taken |
| Salary | The monthly salary that serves as the base | — |
| Day rate | — | Salary divided by the month length set in the header |
| Amount to pay | — | Days pending times day rate |
| Notes | Free note from whoever prepares the sheet | — |
How the calculation works, figure by figure
The example shipped with the template has two employees with different histories: Ana has completed a full year and has already taken part of her days; Carlos has half a year behind him and has taken none. The table shows what each calculated column returns in both cases.
| Employee | Days worked | Days accrued | Days taken | Days pending | Salary | Day rate | Amount to pay |
|---|---|---|---|---|---|---|---|
| Ana | 360 | 15 | 5 | 10 | 2,400,000 | 80,000 | 800,000 |
| Carlos | 180 | 7.5 | 0 | 7.5 | 1,800,000 | 60,000 | 450,000 |
| Total | — | — | — | — | — | — | 1,250,000 |
Ana has 360 days worked. With the header parameter —fifteen days earned for every 360 days worked— her accrued days come to 15. She has taken 5, so 10 are still pending. Her salary is 2,400,000; divided by a 30-day month the day rate is 80,000; multiplied by the 10 pending days that gives 800,000 to pay.
Carlos worked 180 days, half the period, so he accrues 7.5 days. He took none, which means all 7.5 remain pending. His day rate is 60,000, because 1,800,000 divided by 30 is 60,000, and 7.5 days at that rate come to 450,000. The footer adds 800,000 and 450,000 and closes at 1,250,000, the figure that goes into the monthly accrual or into the payment, depending on why the settlement was run.
Two things are worth watching along that path. The first: the day rate is built from the salary, not from what the person happened to receive last month, because last month's payment may have carried overtime or commission that does not belong in this base. The second: if days pending come out negative, it is because more days were recorded as taken than were accrued, and there is nothing to pay in that case — there is a recording error that the sheet surfaces early, before it turns into a complaint.
The sheet total is not decoration. It is the figure you need when the accountant asks how much is accrued for holiday at closing, or when three people leave in the same month and all three have to be paid. That is why the footer carries days and money together: the days say how much absence is still committed, the money says what that absence costs.
Step by step
- Open the template and type the cut-off date into the header. The whole calculation hangs off that cell: leave it empty and there is no settlement.
- Check the two header parameters before you load any people. The accrual one and the month-length one are editable and must match the value set by the rule in force for your case; the sheet applies them exactly as written.
- Add one row per employee with name, hire date and salary. If somebody was rehired, use the date the current employment began and say so in the notes.
- Count the days taken against the absence record, not from memory. One badly counted day becomes wrongly paid money, and nobody notices until the employee reads their payslip.
- Read the calculated columns and stop at any row where days pending come out at zero or below: zero means the person is up to date, below zero means something was recorded wrong.
- Check the footer. If you are paying several people, the total has to match a manual sum of three or four rows picked at random.
- Save a copy with the cut-off date in the file name and file it away. Today's settlement is the supporting document somebody will ask you for in six months.
Common mistakes
None of these are formula mistakes. They are judgement mistakes, and they all end in a figure that will not reconcile with payroll.
- Mixing calendar days and working days in the same column. Days worked and days accrued run on the calendar; days taken usually arrive counted in working days. Mix the two and the pending figure comes out short.
- Loading the salary with everything from the month. If an allowance or a variable commission slips into the salary column, the day rate rises and the total stops matching payroll.
- Using the hire date from the first time round. For somebody who resigned and came back, the old date inflates accrued days wildly.
- Leaving a negative pending figure unexplained. It is almost always a day taken that was recorded twice, or a cut-off date typed wrong.
- Rounding the day rate before multiplying. Round at the end, on the amount to pay; round earlier and the error gets multiplied by the days.
- Reusing last quarter's file. A template dated three months ago returns an old figure that looks perfectly fine.
- Confusing the settlement with the payment. The sheet says what the entitlement is worth; any deductions that apply are handled afterwards, in the payroll run for the period.
The screenshot shows the sheet loaded with the two cases from the example: the header with the cut-off date at the top, the Ana and Carlos rows with their accrued, taken and pending days, and the footer with the total to pay. Before you put your own data in, stop at two cells: the accrual parameter in the header, which moves every accrued figure at once, and the days pending column, which is where recording errors show up first.
When it is time to move to software
This template does its job while there are few people, one cut-off date at a time, and one pair of hands on the file. Fifteen employees settled once a year do not need anything more: opening a system for that would be more work than the sheet.
The picture changes when the question stops being “how much do I pay Ana” and becomes “how much holiday have we accrued across the whole company, by department, at month end”. At that point the sheet runs out of room: it keeps no history per employee, it will not warn you when somebody nears the accrual limit, and it does not cope with three people editing at once. That is when payroll software stops being a luxury, and it is where Kardex Tauro earns its place: not to throw the template away, but so the holiday figure lives where the rest of payroll lives.
In the meantime the rule is simple: one cut-off date per file, one owner per sheet, and a saved copy every time you settle.
Closing thoughts
Pending holiday is a quiet liability: it never shows up in today's cash, nobody claims it until somebody leaves, and it grows on its own while the business keeps working. Having it calculated as at a date —days, day rate and total— changes the conversation: it stops being a “we'll see” and becomes a number you can accrue, negotiate or pay on purpose. Download the template, set your cut-off date and load your people; the first full settlement takes less time than a meeting.
