Aged debtors report template for Excel, South Africa
Short answer: an aged debtors report lists every unpaid invoice and sorts it by how long it is overdue, so you can see who to chase first. This free Excel template does the sorting for you. Type in your unpaid invoices and it shows what is current, and what is 1 to 30, 31 to 60, 61 to 90 and over 90 days overdue, in total and for each client.
Download
| File | What is in it | Download |
|---|---|---|
| Aged debtors template (.xlsx) | A Debtors sheet for 500 invoices, a Summary sheet by age group and by client, and a How to use sheet. Live formulas, drop-down checks, sample rows to overwrite. | Download Excel |
How to use it
- Open the Debtors sheet and replace the six sample rows with your unpaid invoices: invoice number, client, invoice date, due date and the amount still owed.
- Leave the grey-green columns alone. Days overdue and the age group work themselves out from today's date, so the report is current every time you open it.
- When an invoice is paid, clear its row. If a client paid part of it, type only what is still owed.
- On the Summary sheet, type each client's name once, exactly as it appears on the Debtors sheet. The table adds up what each client owes in each age group, and a check line warns you if an invoice belongs to a client you have not listed.
How to read the age groups
The template counts age from the due date, not the invoice date. An invoice due on 17 November is not late on 14 November, so it sits in Current. The sample rows give you every group at once:
| Age | Invoices | Amount owed |
|---|---|---|
| Current (not yet due) | 2 | R47,035.00 |
| 1 to 30 days overdue | 2 | R11,272.50 |
| 31 to 60 days overdue | 1 | R3,277.50 |
| 61 to 90 days overdue | 0 | R0.00 |
| Over 90 days overdue | 1 | R8,400.00 |
| Total owed | 6 | R69,985.00 |
Of R69,985, R22,950 (32.8%) is overdue and R8,400 (12.0%) is over 90 days. That last invoice is where to start. The older an invoice gets, the harder it is to collect, so the useful habit is to look at the 1 to 30 group every week and deal with those invoices before they move down the list.
What to do with each group
- Current: nothing yet. A reminder a few days before the due date is polite and often enough.
- 1 to 30 days: a friendly reminder with the invoice attached. Most of these are forgotten, not disputed. See how to follow up on a late invoice.
- 31 to 60 days: send a statement of account and phone them. Ask for a payment date.
- 61 days and over: a formal reminder, and consider charging interest on the overdue amount if your terms allow it. If they will not engage, read about the small claims court.
Skip the spreadsheet
Zuvo keeps this report up to date from your invoices and payments, shows it on your overview, and downloads it as an Excel workbook with live formulas. It can also send the reminders for you.
Try Zuvo free for 14 days, no cardHow we checked it
Every formula was recalculated with a separate calculation engine and compared with the figures saved in the file, including the edges: an invoice due today is Current, one day late is in 1 to 30, and 91 days late is in over 90. We have not opened it in every spreadsheet program. It should work in Excel, Google Sheets and LibreOffice. In a South African copy of Excel the formulas show with semicolons, which is normal.
Questions people ask
What is an aged debtors report?
An aged debtors report (also called an accounts receivable ageing or debtors age analysis) lists the invoices clients still owe you and groups them by how overdue they are. It shows who owes you, how much, and for how long, so you know who to chase first.
Should I age from the invoice date or the due date?
Both are used. This template ages from the due date, so an invoice only counts as late once it is late. Some businesses and accountants age from the invoice date instead. Either is fine as long as you use the same one every time. If your accountant prefers the other, ask them.
How often should I check my debtors?
Weekly is a good habit for a small business, and monthly is the minimum. Overdue invoices are easiest to collect in the first 30 days, so the aim is to catch them early.
Does this include VAT?
It uses whatever amount you type. Normally that is the full amount the client owes you, including VAT, because that is what they have to pay. Use the same basis throughout.
Can I use it in Google Sheets?
It should open in Google Sheets and the formulas are standard, but we tested the calculations with a separate engine rather than in every program. Upload the file to Google Drive and open it with Google Sheets.
A spreadsheet template and general business guidance, not legal or accounting advice. Check your own figures before you act on them.