Aged Receivables Report: How to Read One and Build It in Excel or Google Sheets

Aged receivables are your unpaid invoices grouped by how long they've been outstanding, usually into current, 1–30, 31–60, 61–90 and 90+ days overdue. The aged receivables report (in Australia often called an aged debtors report) shows each customer's balance in those columns, so you can see at a glance who owes what and how old it is. The rule for reading it is simple: the further right the money sits, the less likely you are to collect it, so the oldest and largest balances get your attention first.

What is an aged receivables report?

An aged receivables report is a list of everyone who owes you money, with each balance split by how overdue it is. It's a snapshot on a particular date, normally month-end or the day you run it. Accounting software calls it "aged receivables", accountants often say "aged debtors", and US guides call it an "invoice aging report". They're the same report.

Your total accounts receivable tells you how much you're owed. The aged report tells you whether that money is healthy or rotting. Two businesses can each be owed $20,000, but if one has most of it in "current" and the other has most of it past 60 days, they're in very different positions.

What are the aging buckets in an aged debtors report?

Most Australian software uses five columns: current (not yet due), 1–30 days overdue, 31–60, 61–90 and over 90 days. Each unpaid invoice sits in exactly one column based on how many days have passed since its due date.

Days overdue or days since invoice?

Some software ages invoices by invoice date rather than due date. On 30-day terms, an invoice in the "31–60 days" column by invoice date might only be a few days overdue. Check which method your report uses and label it at the top. This guide ages by days past the due date, because that's what tells you when to start chasing.

What does an aged receivables report look like?

Here's a worked example for a GST-registered cabinet-maker, run on 31 October 2026. All amounts include GST, because that's what the customer owes.

How do you read an aged receivables report?

Read it in four passes: the total, the share that's overdue, the share that's old, and which customers own the old money. Each pass takes seconds once you know what you're looking for.

1. Total owed: $22,880. Compare with last month. If the total is climbing while sales are flat, customers are taking longer to pay.
2. Overdue share: 55.8%. Everything outside "current" adds up to $12,760, more than half the ledger. That's high for a business on 14-day terms.
3. Old share: 30.3%. The 61–90 and 90+ columns hold $6,930. This is the money most at risk.
4. Who owns it. Two customers account for all of the old money: Patel Property Group ($4,180 at 61–90 days) and Coastline Interiors ($2,750 at 90+).

Now the action plan. Harbour Builders owes the most but is only slightly late, so a reminder is enough. Patel is the priority: a single large invoice two months overdue needs a phone call this week and a final notice if that fails. Coastline is the warning sign. It has a 90-day-old debt and yet a new $1,320 invoice is sitting in "current", which means you're still extending credit to a customer who hasn't paid for a job from July. Pause new work until the old invoice is settled or a payment plan is agreed.

There's a GST angle too. If you account for GST on an accruals basis, you've already paid the ATO the GST inside those balances: $250 of Coastline's $2,750 is GST ($2,750 ÷ 11). That's another reason old debts cost more than they look.

How do you build an aged receivables report in Excel or Google Sheets?

You need one list of invoices with a due date and balance, a formula for days overdue, and a formula that drops each invoice into a bucket. A summary table with SUMIFS then totals each customer by bucket. The same formulas work in Excel and Google Sheets.

Step 1: Set up the invoice list

On a sheet named Invoices, put the report date in cell C1. Use =TODAY() for a live report, or type a fixed date such as 31/10/2026 for a month-end snapshot. Then add these headings in row 3 and one invoice per row from row 4:

With a report date of 31 October 2026, the example invoice shows 77 days overdue, which lands it in the 61–90 bucket. Make sure columns C and D are real dates, not text, or the subtraction returns an error.

Step 2: Add the bucket formula

In I4, paste this and copy it down the column:

If you're on Excel 2019 or later, Microsoft 365 or Google Sheets, the shorter IFS version does the same job:

Step 3: Build the summary by customer

On a second sheet named Summary, type the bucket labels across row 1, exactly as the formula writes them: Current in B1, 1-30 in C1, 31-60 in D1, 61-90 in E1, 90+ in F1 and Total in G1. In A2, list your customers. In Google Sheets or Microsoft 365, =UNIQUE(Invoices!B4:B500) fills the list for you; otherwise type them in.

Prefer a pivot table? Select the invoice list, insert a pivot table, put Customer in rows, Bucket in columns and Balance in values (sum), then filter out "Paid". You get the same grid without the SUMIFS.

Two habits keep the report honest. Enter part payments in column F the day they land, so the balance is right. And never delete a cancelled invoice: issue a credit note and record it as a payment against the invoice, so the balance drops to zero and your numbering stays intact.

What should you do with each bucket?

Match the action to the age of the debt. Early buckets need reminders; later buckets need written notice and a decision about whether to keep chasing or write the debt off.

For scripts and timings, see our guide on how to chase overdue invoices. For a repeat customer with several open invoices, a statement of account puts everything on one page. If a customer genuinely can't pay in one go, a written customer payment plan usually recovers more than a stand-off. When reminders fail, our letter of demand template is the last step before legal action. If the dispute is with a larger business, the Australian Small Business and Family Enterprise Ombudsman offers dispute support.

When should you write off a debt in the 90+ bucket?

Write it off once you've made reasonable efforts to collect and decided the money isn't coming. Writing off is a bookkeeping decision, not forgiveness, so you can still recover it later. The ATO sets out when a bad debt is deductible on its page about deductions for bad debts. On the accruals GST basis you can also claim back the GST you paid, through a decreasing adjustment on your BAS, once the debt is written off or has been overdue for 12 months or more (see the ATO's GST adjustment types). Our guide to writing off bad debts covers the records to keep.

How often should you run an aged receivables report?

Weekly if you invoice most days, and at least monthly otherwise. A weekly report catches invoices while they're in the 1–30 column, where a reminder still works. A month-end-only report means some invoices are already six weeks late before you notice.

Watch for these patterns from one report to the next:

• Money sliding right. The same invoice moving from 1–30 to 31–60 to 61–90 means your follow-up isn't working.
• One customer dominating. If a single customer holds more than a third of your overdue balance, losing them would hurt, and so would their not paying.
• "Current" shrinking. Fewer current invoices while overdue grows means new sales are slowing and old ones aren't being collected.
• Small balances ignored. A string of $200 invoices in the 90+ column adds up, and it trains customers to treat your invoices as optional.

The aged report is the action list; the accounts receivable turnover ratio and DSO are the scorecard that tells you whether it's improving over time. Feed the expected payment dates from your aged report into a cash flow forecast so you know when the money will actually arrive.

This article is general information, not legal, tax or financial advice. For guidance specific to your situation, see business.gov.au and the ATO, or speak to a registered tax agent or BAS agent.

Frequently asked questions