Ultimate UK Holiday Pay Calculator

Zero-hours, casual and term-time staff build up holiday at 12.07% of the hours they work, and their holiday pay must be worked out from their average pay over the last 52 paid weeks. Get either wrong and staff are underpaid. This calculator does both after every payroll run and checks every holiday payment.

Made for UK small businesses with irregular-hours or part-year staff: pubs, cafés, events, care, cleaning, schools and clubs. Enter each worker's hours, pay and holiday from their payslips, and it works out the holiday they have built up, what each hour of holiday must be paid at, and flags any holiday payment below the 52-week average.

▶ Easy Start Here guide included – video tutorial coming to our YouTube channel

Works in Microsoft Excel and Google Sheets, with no macros.

What it does for you

• 12.07% accrual of the hours worked in each pay period (leave years from 1 April 2024), with the GOV.UK rounding to the nearest hour
• 52-week reference period: average pay per hour over the last 52 weeks in which the worker was paid, with weeks off sick or on statutory leave left out and earlier weeks used, never more than 104 weeks back. Workers with fewer weeks use the weeks they have
• Holiday pay check on every payslip row: holiday pay due vs paid, with Underpaid £x.xx, Enter holiday pay paid, or Rolled-up (in pay)
• Holiday Pay Check tab: pick a worker and a holiday date to see every paid week used, the average weekly pay and hours, and the holiday pay due
• Two methods per worker: accrue & take (paid when taken) or rolled-up holiday pay (12.07% of pay as a separate payslip line)
• Running balance per worker for the leave year, holiday pay owed for the balance in £, and leavers flagged for pay in lieu
• Worker Holiday Statement: pay period by pay period, ready to print or keep as a record
• Dashboard: active workers, hours, holiday accrued and taken, rolled-up pay, a "What needs your attention" list and 3 charts
• Checks for unknown workers, dates that are not dates, duplicates and text in number columns
• Room for 60 workers and 2,500 pay periods

How it works (3 steps)

1. Set up once: Settings (your leave year and rounding) and one row per worker (about 5 minutes)
2. Add every payslip: hours, pay, holiday taken and holiday pay paid, with sick or statutory leave weeks marked
3. Check and pay right: use the Holiday Pay Check before paying holiday, and fix anything the Dashboard flags

What's included

• UK-Holiday-Pay-Irregular-Hours.xlsx: filled with fictional example data (6 workers, 2 years of pay periods) so you can see how it works
• UK-Holiday-Pay-Irregular-Hours-BLANK.xlsx: an empty copy, ready for your own workers
• Works in Google Sheets: upload the .xlsx to Google Drive, right-click > Open with > Google Sheets, then File > Save as Google Sheets (tested: formulas and charts kept)
• Start-here.pdf: one-page quick-start guide, including how to open it in Google Sheets

Compatibility

• Microsoft Excel 2010 or newer (Windows & Mac) and Microsoft 365
• Google Sheets (free Google account). Set File > Settings > Locale to United Kingdom for £ and UK dates
• Not designed for Apple Numbers or mobile-only editing. A computer is recommended
• No macros, add-ons or subscriptions

Instant download

This is a digital product. Nothing physical will be shipped. Your download link appears straight after checkout and is also emailed to you. Stuck or have a question? Email info@phkmedia.co.uk and I'll gladly help you set it up.

Good to know

• Cream (pale yellow) cells are for your input. White and grey cells are worked out for you, and the sheets are protected (no password) so formulas are not overwritten by mistake
• A Health check on the Start Here tab and a Check/Status column on every log flag missing or odd entries
• Rules follow GOV.UK "Holiday pay and entitlement reforms from 1 January 2024" and Acas "Irregular hours and part-year workers" (checked 02/11/2026). The 12.07% method and rolled-up holiday pay apply to irregular-hours and part-year workers in Great Britain only
• Holiday pay for these workers is their average pay over the last 52 weeks in which they were paid. Weeks with no pay are not counted, so earlier weeks are used, but never more than 104 weeks back. Acas says holiday pay includes commission, regular overtime and seniority payments, so include them in the pay column
• From 6 April 2026 employers must keep records of holiday taken, carried over, holiday pay and pay in lieu for 6 years (Working Time Regulations 1998 reg. 16B)
• Not covered: holiday that builds up during sick or family leave (worked out from average hours), and the 28-day yearly cap is not applied for you
• Dates show as DD/MM/YYYY
• FAQ: Does it run payroll? No. It works alongside your payroll: copy the hours and pay from each payslip
• FAQ: Does it work on a phone? You can view it in the Google Sheets app, but entering data is easiest on a computer

DISCLAIMER

This calculator is a calculation aid, not legal or payroll advice. Northern Ireland has its own rules. Check gov.uk or call Acas on 0300 123 1100 if you are unsure. All names and figures in the example file are fictional.

Licence: Licensed for use within one business. Please do not resell, share or redistribute the files.

AI disclosure: This template and its listing images were designed by PHK MEDIA LTD with the help of AI tools. All formulas were tested and the files were reviewed and finalised by the seller.

© PHK MEDIA LTD

Licence: Licensed for use within one business or household. You may edit and print the files for that business. You may not resell, share or upload them for others.