Aged debtors report in Excel: how to make one and read it
An aged debtors report lists every unpaid invoice grouped by how overdue it is: not yet due, 1 to 30 days late, 31 to 60, 61 to 90 and over 90 days. In South Africa it is often called a debtors age analysis. It is the quickest way to see who owes you, how much, and which invoices need a phone call today.
On this page: how to read one · make one in Excel · what to do in each bucket · make one in Zuvo · questions
How to read an aged debtors report
Each invoice goes into a column by how many days it is past its due date:
- Current: not yet due. Nothing to do.
- 1 to 30 days: late, usually forgotten. A friendly reminder is enough.
- 31 to 60 days: a real problem is starting. Phone, and send a statement.
- 61 to 90 days: serious. A firm written reminder, and think about stopping further work for that client.
- Over 90 days: at risk. The older an invoice gets, the harder it generally is to collect.
Read it two ways: by client, to see who your problem payers are, and by column, to see how much of your money is old. If most of what you are owed is in the first two columns, you have a slow-payment habit. If one client holds most of the old money, you have a client problem.
Make one in Excel
Short cut: download the free aged debtors template. It has the formulas, the age groups and a summary by client already built. Or follow the steps below to build your own.
Put your unpaid invoices on a sheet with these columns: Invoice (A), Client (B), Due date (C), Balance owed (D). Then add two more.
Days overdue in column E:
=TODAY()-C2
Age in column F, which puts each invoice in a bucket:
=IF(E2<=0,"Current",IF(E2<=30,"1-30 days",IF(E2<=60,"31-60 days",IF(E2<=90,"61-90 days","Over 90 days"))))
On a second sheet, list each client in column A and use SUMIFS to add up what they owe in each bucket. For the "31-60 days" column of the client in cell A2:
=SUMIFS(Invoices!$D:$D,Invoices!$B:$B,$A2,Invoices!$F:$F,"31-60 days")
Add a total column with =SUM(B2:F2) and a total row at the bottom. On some South African computers Excel expects semicolons instead of commas between the parts of a formula, so if Excel refuses a formula, swap the commas for semicolons.
This works, but you have to re-type the unpaid invoices every time something is paid, which is why most people stop doing it.
What to do in each bucket
- 1 to 30 days: a polite reminder with the invoice attached. See how to follow up on a late invoice.
- 31 to 60 days: phone them, and send a statement showing everything outstanding.
- 61 to 90 days: a firm written reminder that states what you will do next, and no new work on credit.
- Over 90 days: a formal demand. You may be able to charge interest on the overdue amount, and for smaller amounts the small claims court is an option.
Make one in Zuvo
Zuvo works this out from your invoices and payments, so it is always current. Open Reports and you see the unpaid invoices by age, with a list by client and the oldest invoices first. A payment or a credit note moves an invoice out of the report straight away.
- Download Excel gives you the workbook described above, already built: the unpaid invoices on one sheet and a "Debtors by client" sheet whose figures are live
SUMIFSformulas over it, so your accountant can click any cell and see how it was reached. It is on every plan. - Ask your books answers questions such as "who owes me more than 60 days?" from the same figures, and shows the report each answer came from.
- On Starter and above, Zuvo can also send the reminders to your clients for you.
See who owes you, without the spreadsheet
Zuvo keeps your debtors by age up to date and downloads it as an Excel workbook your accountant can check.
Try Zuvo free for 14 days, no cardGeneral guidance, not legal advice. Whether you can charge interest or use the small claims court depends on your agreement and the amount, so check the details for your situation.
Questions people ask
What is an aged debtors report?
A list of every unpaid invoice grouped by how overdue it is: not yet due, 1 to 30 days late, 31 to 60, 61 to 90 and over 90 days. It shows who owes you, how much and for how long. In South Africa it is often called a debtors age analysis.
How often should I look at my aged debtors?
At least monthly, and weekly if cash is tight. The point is to catch an invoice at 30 days, not at 90.
How do I make an aged debtors report in Excel?
List each unpaid invoice with its client, due date and balance. Work out the days overdue with =TODAY()-the due date, put each invoice in a bucket with a nested IF, and then add up by client and bucket with SUMIFS. The formulas are in this guide.
Does Zuvo make an aged debtors report?
Yes. Reports shows unpaid invoices by age and by client on every plan, and you can download it as an Excel workbook with live SUMIFS formulas. Ask your books can also answer questions like who owes you over 60 days.
What do I do about invoices over 90 days?
Send a formal demand, stop extending credit to that client, and consider charging interest or going to the small claims court. See the guides linked below.