← Back to Zuvo
Getting paid

Aged debtors report in Excel: how to make one and read it

Zuvo Team12 November 20266 min read

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:

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

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.

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 card

General 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.