Download a free Excel template where each payment is entered once and the overview of who owes what builds itself — with step-by-step instructions, the key formulas explained and the mistakes to avoid.
A membership fee spreadsheet is the first collection tool many clubs build for themselves — and often the first thing to fall apart as the club grows. The reason is usually the same: it was set up as a colour-coded grid of players and months rather than as a record of payments. Below is a free Excel template that works the other way round — you enter each payment once and formulas do the rest — plus a guide to using it.
Why one payment log beats a colour-coded grid
The typical fee spreadsheet has players down the side, months across the top, and a figure or a green fill when someone pays. It looks tidy, but it soon stops telling the truth:
- Colour is not data. A formula cannot add up green cells, so monthly totals get counted by hand.
- No date, no method. When a parent says ‘I paid in March, by bank transfer’, the grid has no answer.
- Part and advance payments don't fit — half a fee, or three months at once, ends up as a cell comment.
- Overwriting erases history. Correct an amount and you have rewritten the past, with no trace of what was there before.
The better model is a payment log: every payment is one row — date, player, the month it pays for, amount and method. The player list holds what each person owes per month, and the monthly grid and balances are calculated from those two sheets. You enter each fact once and see it everywhere.
What's in the free template
The workbook has five sheets. You fill in only two — Players and Payments — and the other three calculate themselves.
| Sheet | What you enter | What is calculated |
|---|---|---|
| How to use | Nothing — short instructions and tips | — |
| Players | Full name, group, date of birth, parent or guardian, phone, email, monthly fee, discount %, status, note | Amount due per month after the discount |
| Payments | Payment date, player (from a list), month, amount, method, receipt number, note | — |
| Fees | Nothing | Payments per player and month, total paid, expected for 12 months, amount owed, and paid, part-paid and unpaid colours |
| Summary | Nothing | Per month: expected, collected, collection rate and members owing, plus a ‘Collected by month’ chart |
How to use the membership fee spreadsheet, step by step
- Delete the examples. Players and Payments each contain two rows marked ‘Example’ so you can see how they are filled in. Remove them before you start.
- Add your players. For each one: name, group from the drop-down (U8 to U18, Seniors, Adults), monthly fee and any discount in per cent. The ‘Due per month’ column applies the discount and rounds the amount. Status is Active or Inactive.
- Enter every payment once on Payments: date, player from the drop-down, the month it pays for, amount and method (cash or bank transfer). Receipt number and note are optional, but they help when you need to trace a payment.
- Check the Fees sheet. Cells colour themselves: green when a month is paid in full, amber when it is part-paid, red when a month has passed with nothing paid. Each row ends with total paid, expected for 12 months and amount owed.
- Watch the Summary. For each month you see the expected and collected amounts, the collection rate and how many members owe, and the chart shows how collection runs through the year.
- Start a new copy each season, with the same player list and an empty payment log.
Two things are worth knowing. First, ‘Expected (12 mo.)’ assumes a fee every month — if you don't charge over the summer, the amount owed at the end of the row will be higher than the real figure, so look at the months individually. Second, the Summary works out expected amounts and members owing from the current list of active players, so for months still to come every active player counts as owing until payments arrive.
The key formulas in plain English
You don't need to understand the formulas to use the template, but knowing what they do makes it easier to extend the sheet or track down a mistake. They are simplified here.
| Where | Formula (simplified) | What it does |
|---|---|---|
| Players → Due per month | =ROUND(G2*(1-H2/100),0) | Takes the discount off the monthly fee and rounds it |
| Fees → each month | =SUMIFS(Payments!D:D, Payments!B:B, player, Payments!C:C, "March") | Adds up every payment by that player for that month, whatever the payment date |
| Fees → Total paid | =SUM(B2:M2) | Adds up the twelve months |
| Fees → Owed | =MAX(0, O2-N2) | Expected minus paid, never below zero |
| Summary → Expected | =SUMIFS(Players!I:I, Players!J:J, "Active") | Adds up the monthly amount due from active players |
| Summary → Collected | =SUM(Fees!B2:B151) | Adds up that month's column on Fees |
| Summary → Collection rate | =C2/B2 | The share of the expected amount that has come in |
| Summary → Members owing | =SUMPRODUCT((status="Active")*(paid<due)) | Counts active players who paid less than they owe for that month |
The colours on Fees are conditional formatting, not manual fills: a cell turns green when payments for the month reach the amount due, amber when they are above zero but below it, and red when they are zero and the month is over.
Common spreadsheet mistakes
- Typing names by hand on the payment log — one letter's difference and the payment ‘disappears’.
- Deleting players who leave. Set them to Inactive instead; a deleted row takes its history with it.
- Typing a number over a formula. A cell on Fees with a typed-in value stops following payments. If it happens, copy the formula back from the cell next to it.
- Several copies of the same file. When the coach has one version and the treasurer another, neither is right. Keep one file in a shared place, with one person entering payments.
- One file for every year. New season, new copy; the old one becomes the archive.
- Merged cells and blank rows in the middle of a list, which break sorting and filtering.
- No backup. A file on one laptop will one day be a lost file. Keep it in cloud storage, or at least in a second place.
Set your fee rules before the first payment
A spreadsheet records the rules you have decided; it does not make them. Before the first payment goes in, agree and write down:
- the monthly fee for each group, and whether new players pay from the month they join;
- discounts — siblings, a second sport, financial hardship — and who approves them;
- what happens in months without training, such as the summer break;
- how parents pay, by which day of the month, and what they receive as proof;
- when a member counts as owing, and who contacts them.
Check the legal side as well. Some countries regulate the fees clubs may charge minors, so confirm what applies where your club operates — and with your governing body — before you set prices.
When a spreadsheet stops being enough
A well-built spreadsheet can serve a club for a long time. These are the signs you have outgrown it:
- two or three people take payments, and one person updates the file when they get round to it;
- parents ask ‘have I paid for October?’ and the answer takes longer than the payment did;
- alongside fees you track kit, entry fees and travel, each in its own file;
- you chase late payers one message at a time;
- you can't give the month's collection rate without an hour of tidying up.
An app keeps the same logic — players, charges and payments — but does the work the spreadsheet leaves to you. In Clubiqo:
- Automatic monthly charges at the player's, group's or club's price, without copying rows.
- Reminders to parents — up to three automatic reminders a month for unpaid fees, on the day and at the time you choose — and, for adult members, an SMS reminder sent from your own phone through the mobile app.
- Parent visibility — in the parent app, parents see which months are paid, unpaid or waived, along with their payment history.
- Receipts with the club's details, printed from the web dashboard (they are not tax invoices).
- For clubs in Serbia, an IPS QR code parents scan in their banking app (dinar payments only). You still mark the payment as received, as there is no automatic bank matching.
- Kit sales and competition costs in the same place — see club kit sales.
Moving over does not mean losing your spreadsheet. The Players sheet can be imported into the web dashboard (the import reads the first sheet of a file, so save Players as a separate file first), and fee records can be exported to Excel or synced to Google Sheets. More on membership fee tracking.
Frequently asked questions
Does the template work in Google Sheets?
It was made for Excel, but as an ordinary .xlsx file it can also be opened in Google Sheets or LibreOffice — if you do, check that the colours and drop-downs have carried over. The formulas are standard (SUMIFS, SUMPRODUCT). If several people share the file, Google Sheets is the more practical choice, because everyone works in the same copy and versions cannot drift apart.
How do I record a payment covering several months?
Add one row per month — three months, three rows, with the same date and receipt number but each with its own month and amount. The formulas add payments up by the month they pay for, not by the date they were made, so each month turns green regardless of when the money arrived.
What if a player pays only part of the fee?
Enter the amount actually paid. That month's cell on Fees turns amber, and the difference shows in the Owed column. When the rest arrives, enter it as a new payment for the same month — the formula adds both rows and the cell turns green. Nothing is deleted or overwritten, so the history stays complete.
How many players and payments does the template handle?
It is prepared for 150 players and 900 payment rows. A club of 75 players paying monthly fills those rows in a year, so a larger club will need to extend the formulas and ranges to new rows. That is also a good moment to consider an app, because automatic charges and reminders save the most time in bigger clubs.
Conclusion
A good membership fee spreadsheet depends on structure, not colour: a player list with the amount due, a single log of payments, and formulas that turn those into a monthly view and balances. The free template is set up exactly that way — download it, delete the examples and enter your first payment.
When the spreadsheet starts to feel small, Clubiqo does most of that job automatically — monthly charges and reminders for late fees — and parents see their payments in their own app. See pricing and try it free for 14 days, no card required, or read our guide to membership fee management.