Guide, 2026-11-04

How to make an accounts receivable aging report in Excel

An accounts receivable aging report answers three questions every business that invoices on credit should be able to answer on any given day: how much are customers holding, how late is it, and who should you call first? You do not need accounting software to produce one. A spreadsheet with three small lists is enough.

What an aging report shows

Each unpaid invoice is placed in a bucket based on how many days it is past its due date. The usual buckets are Current (not yet due), 1-30, 31-60, 61-90 and 90+ days overdue. The report then totals those buckets per customer and for the whole business. The older the bucket, the less likely the money is to arrive without effort, so the report doubles as a priority list.

Step 1: keep three lists

• Customers: name, contact, default payment terms in days.
• Invoices: invoice number, customer, invoice date, amount, and terms if they differ from the customer default.
• Payments: date, invoice number, amount. One row per payment, so part payments are easy to record.

Keeping payments separate from invoices is the key design choice. It stops you overwriting amounts and gives you a history of when each customer actually paid.

Step 2: work out each invoice's balance and due date

Add columns to the invoice list:

1. Paid to date: =SUMIF(Payments!B:B, A2, Payments!C:C), the sum of payments against that invoice number.
2. Balance: amount minus paid to date.
3. Due date: invoice date plus terms in days.
4. Days overdue: =MAX(0, AsOfDate - DueDate), using a single as-of date cell rather than TODAY(), so the report can be rerun for any date, such as a month end.

Step 3: assign the bucket

Only age invoices that still have a balance. A nested IF does the job: if the balance is zero, leave it blank; if days overdue is 0, "Current"; if 30 or fewer, "1-30"; if 60 or fewer, "31-60"; if 90 or fewer, "61-90"; otherwise "90+". Then use SUMIFS on balance by customer and bucket to build the summary table.

A worked example

A small agency runs the report with an as-of date of 30 June 2026. It has three customers and six open invoices.

INV-095 was a $2,400 invoice with a $900 part payment, which is why its balance is $1,500. The summary by customer looks like this:

Of the $7,630 outstanding, $5,830 (about 76%) is overdue. The chase order is clear: Lumen Studio's 105-day invoice first, then both Harbour Cafe invoices, because that customer has nothing current and everything getting older.

Add a simple DSO

Days sales outstanding (DSO) estimates how many days of sales are tied up in receivables. One common version is receivables divided by credit sales for a period, multiplied by the days in that period. If the agency invoiced $15,000 in the last 90 days, DSO is 7,630 ÷ 15,000 × 90, which is about 46 days. If its standard terms are 30 days, customers are taking noticeably longer than agreed. Track the same formula every month so the trend means something.

Common mistakes

• Aging from the invoice date instead of the due date, which makes everyone look late.
• Typing payments into the invoice amount, so part payments and payment history are lost.
• Payments recorded against a mistyped invoice number, which silently leaves the invoice open. Check for payment rows that match no invoice.
• Duplicate invoice numbers, which double the balance.
• Using TODAY() everywhere, so last month's report can never be reproduced.

When a ready-made workbook saves time

The method above takes an hour or two to set up and works well for a handful of invoices. The time goes into everything around it: the data checks, a statement you can send to a customer, a chart, and keeping the formulas intact as the lists grow. If you would rather enter your lists and get the report, AgingBook does the bucketing, a collections to-do list, printable customer statements, a receivables dashboard with DSO and checks for unknown, duplicate or overpaid invoices. It is a tracker, not accounting software, so your books stay where they are.