Service charge split template for Excel
Enter the pool for the period, the hours for a full share and a row per person with their hours (or Y for salaried), and every share is worked out: anyone at or over the cap gets a full share, fewer hours earns pro-rata, a role can take a fixed percentage of a share, an admin fee can come off first, and the payouts are rounded so they add up to the pool exactly.
- No sign-up
- Excel, Google Sheets, LibreOffice
- Formulas, no macros
- Prints on A4 or Letter
About this template
A service charge pool is usually shared once per pay period by a written formula, and the formula most Caribbean hotels and restaurants use is hours with a cap: a number of hours that earns a full share, so that everyone who worked a full period gets the same, part-timers and mid-period joiners get a pro-rata share, and overtime does not buy a bigger slice. Salaried staff who do not clock get a full share, and some roles get a fixed fraction of a share by policy. This template is that formula in a file you can keep with the payroll records.
Set the cap and any admin fee once on the Settings sheet. On the Staff sheet enter the pool and a row per person; the Points column shows each person’s fraction of a share before any money is attached, so the team can see why the payouts are what they are, and the Full share figure (distributable pool ÷ total points) is the quickest check. It is the same arithmetic as the service charge split calculator.
Whether service charge belongs to staff, who may be in the pool and what the house may deduct are set by law and by your policy, and differ by country; the template only does the sums. The tips and service charge guide covers the common rules, and for tips shared per shift without a cap there is the tip pool split template.
Background reading: Tips and service charge
How it works
- Download the file and open it in Excel, Google Sheets or LibreOffice.
- On Settings, set the Hours for a full share (cap) and, if your policy has one, the admin / house fee percentage.
- On Staff, enter the Pool for the period and list the people with their role, hours worked, Y in Salaried for a full share without hours, and a Share % for roles that get part of a share (blank = 100%).
- Leave a person’s Hours blank to take their total from the Period sheet, where you type hours per day for up to 16 days.
- Read Points, % of pool and Payout; the Check line should say the payouts add up to the distributable pool. Print or save as PDF for the records.
Frequently asked questions
How are the points worked out?
Points = MIN(hours used ÷ cap hours, 1) × share %. With a 144 h cap, 160 h is capped at 1.00, 72 h is 0.50 and 144 h at 50% is 0.50; Y in Salaried gives 1.00 (times the share % if there is one). The pool is divided by the total points, and each person is paid their points’ worth.
What is the cap for?
It turns hours into shares fairly: everyone who worked a full period earns one full share whether they did 144 or 190 hours, and fewer hours earn a proportionate share. Set it to match your period: 144 for a semi-monthly pool, 160 to 176 for monthly, 40 or 44 for weekly. Set it to 0 to give anyone who worked a full share.
Why do the payouts not add up to the pool?
They do: shares are rounded to cents, which can leave a few cents over or under, and the Payout column adds that difference to the largest share so the total equals the distributable pool. The Rounding left over line shows how much that was. If the Check says the payouts do not match, the pool or the hours are probably blank.
Can I take an admin fee or split by department?
The admin / house fee on the Settings sheet is taken off the pool before the split, where that is lawful and written in your policy. The spreadsheet shares one pool; to split between departments by percentage, use the online calculator’s Advanced section or make one copy of the Staff sheet per department with that department’s slice as its pool.
Do salaried staff and managers get a share?
The template lets you include anyone with Y in Salaried, and a share % lets you give a part share. Whether salaried staff or managers are in the pool at all is a matter of house policy and local law; many businesses exclude management. Write the rule down and apply it the same way each period.
Free to download and use, including at work; no sign-up and no tracking inside the file. The template does the arithmetic with the numbers you give it: it has no tax rates and no legal limits built in, because pay, overtime and leave rules are set by each country's law and by your contract. Check anything that matters with your labour department or payroll.
Do this automatically
PunchClock shares the service charge from the hours actually clocked, with the cap, salaried full shares and role percentages set once, and prints each share on the payslip.