Debtors Ageing Report in Excel: 0–30, 31–60, 61–90 and 90+ Days

📎 This article includes 1 downloadable practice file ↓

Every month-end someone asks, “How much is stuck beyond 90 days, and with whom?” A debtors (receivables) ageing report answers that. It groups each unpaid invoice by how long it has been overdue: not yet due, 1–30, 31–60, 61–90 and over 90 days. Banks ask for it for working-capital limits, auditors check it for provisions, and sales teams use it to chase payments.

Start with clean data

One row per invoice, as a Table (Ctrl+T):

Balance = Amount − Received. Put the as-on date in a single cell (say Setup!B2) instead of using TODAY(), so the report for 30 September still shows 30 September figures when you open it in October.

Days overdue

=MAX(0, Setup!$B$2 - [@[Due date]])

Ageing is measured from the due date, not the invoice date. MAX(0, …) shows invoices not yet due as 0 instead of negative days.

Bucket label

=LET(d, Setup!$B$2 - [@[Due date]],
  IF([@Balance] = 0, "Paid",
  IF(d <= 0, "Not due",
  IF(d <= 30, "1-30",
  IF(d <= 60, "31-60",
  IF(d <= 90, "61-90", "90+"))))))

Prefer a lookup table if your buckets change often: a small table of lower limits (−99999, 1, 31, 61, 91) with labels, and =XLOOKUP(d, Limits, Labels, , -1) picks the right band.

The summary: totals by bucket

SUMIFS on the due date gives each bucket directly, with no helper column:

Not due:  =SUMIFS(T[Balance], T[Due date], ">=" & AsOn)
1-30:     =SUMIFS(T[Balance], T[Due date], "<" & AsOn, T[Due date], ">=" & AsOn - 30)
31-60:    =SUMIFS(T[Balance], T[Due date], "<" & AsOn - 30, T[Due date], ">=" & AsOn - 60)
61-90:    =SUMIFS(T[Balance], T[Due date], "<" & AsOn - 60, T[Due date], ">=" & AsOn - 90)
90+:      =SUMIFS(T[Balance], T[Due date], "<" & AsOn - 90)

Add a customer condition (T[Customer], $A5) and copy across to get the classic customer × bucket grid. The bucket totals must add up to the total balance; put that check under the report.

Customer grid in one formula (Excel 365)

=PIVOTBY(T[Customer], T[Bucket], T[Balance], SUM)

On older Excel, insert a pivot table: Customer in Rows, Bucket in Columns, Balance in Values. Sort bucket columns by right-clicking › Move so “90+” sits on the right.

Useful extras

=SUMIFS(T[Balance], T[Due date], "<" & AsOn - 60) / SUM(T[Balance])    ' share over 60 days
=COUNTIFS(T[Balance], ">0", T[Due date], "<" & AsOn - 90)               ' invoices over 90 days
=XLOOKUP(MINIFS(T[Invoice date], T[Balance], ">0"), T[Invoice date], T[Invoice])   ' oldest unpaid

Conditional formatting on the 90+ column (anything above zero in red) makes the report readable in two seconds.

Unapplied receipts and credit notes

Real ledgers have advances and credit notes that aren’t matched to an invoice. Keep them as rows with a negative balance and bucket them as “Unapplied”; otherwise the customer looks worse than they are. Clear them with the sales team each month.

Where people go wrong

Practice

Download the practice workbook below. It has 40 invoices with part payments and a fixed as-on date of 30-Sep-2026. Each task has an automatic ✓ check, and the Answers sheet has a working formula if you get stuck.

See also: ageing buckets formula combos.