Accounts Receivable Aging Template: How to Build One in Excel or Google Sheets

Most small businesses know, roughly, how much they're owed. Far fewer know how old that money is — and the age of an unpaid invoice matters more than its size. A $500 invoice that's 95 days overdue is a bigger problem than a $5,000 invoice that's 12 days overdue, because the older an invoice gets, the less likely it ever gets paid. An accounts receivable aging report is the spreadsheet that sorts your unpaid invoices by exactly this, so the oldest, riskiest ones surface first instead of getting lost in a long list.

What an AR aging report actually is

It's your invoice list, unpaid ones only, grouped into buckets by how many days past due each one is. The standard buckets, used almost universally:

The value isn't just seeing the total owed — it's seeing how it's distributed. $20,000 outstanding where 90% is Current and 1–30 is a healthy business. $20,000 outstanding where a third of it is sitting in the 90+ bucket is a collections problem wearing a revenue number's clothes.

Step 1 — Start from your invoice list

You need one row per invoice with, at minimum, these columns: invoice number, client, amount, due date, date paid (blank if unpaid), and status. If you're already tracking invoices for any other reason — cash flow forecasting, client profitability — this is the same list; an aging report is a view on top of it, not a separate system to maintain.

Step 2 — Calculate days outstanding

For each unpaid invoice, days outstanding is today's date minus the due date. The formula logic:

• If the invoice is marked paid, days outstanding is 0 (or blank) — it's no longer aging.
• If it's unpaid, days outstanding = today's date − due date.

In a spreadsheet that's a single conditional formula: check the status column first, and only calculate today-minus-due-date if the invoice is still open. This is what lets the number update itself every day the sheet is opened, with zero manual work.

Step 3 — Assign the aging bucket

Once you have days outstanding, the bucket is a nested set of conditions, checked in order: paid or written-off invoices get their own label and drop out of the aging entirely; anything with 0 or fewer days outstanding (not yet due) is Current; then 1–30, 31–60, 61–90, and 90+ days, in that order. Getting the order right matters — check "is it paid" and "is it not yet due" before you start comparing day counts, or a paid invoice can accidentally get aged as overdue.

Step 4 — Summarize by bucket

Once every invoice has a bucket, a simple sum-by-category table turns the row-level list into an at-a-glance view: total dollar amount in each of the five buckets, and optionally a count of invoices in each. This is the view you actually look at weekly — the row-level list is where you go to act on it.

A rough health check: if Current plus 1–30 is holding above 80–90% of total AR, collections are in reasonable shape. If the 60+ buckets are climbing as a share of the total month over month, that's the trend to interrupt before it compounds.

Step 5 — Highlight the overdue rows

Conditional formatting that colors a row red once it passes into the 31–60 bucket (or whatever threshold matters to you) turns a table you'd have to read carefully into one you can scan in five seconds. The point of an aging report is speed — knowing who to call today without re-deriving it from a due-date column every time.

Step 6 — Turn buckets into action, on a schedule

The report only pays off if it drives a routine. A workable weekly pass:

1. 1–30 bucket: a short, friendly reminder email — most of these clear on their own once nudged.
2. 31–60 bucket: a firmer follow-up, ideally a phone call or direct message rather than another email that's easy to ignore.
3. 61–90 bucket: a direct conversation about what's going on, and whether a late fee applies under your payment terms — see our guide on invoice due dates and late fees for how to calculate one.
4. 90+ bucket: decide, per invoice, whether it's a payment plan, a formal collections step, or a write-off. The longer this decision is deferred, the more it costs you in time spent chasing money that was never coming.

Common mistakes

• Only looking at the total owed, not the age distribution — two businesses with identical AR totals can be in very different positions.
• Letting paid invoices keep aging because the status column wasn't updated — makes the report look worse (or better) than reality.
• No routine attached to the buckets. A report nobody acts on is just a more organized way of ignoring the problem.
• Treating every overdue invoice the same regardless of age — a 5-day-late invoice and a 95-day-late invoice need different responses.
• Forgetting to feed this into cash flow. Your cash flow forecast should treat aging invoices as increasingly uncertain the older they get, not as guaranteed income arriving on schedule.
• No clear write-off point. Decide in advance roughly when an invoice moves from "chase it" to "write it off" so 90+ day invoices don't sit forever in limbo.

Excel vs Google Sheets for AR aging

Both handle this well — it's a handful of conditional formulas and a summary table, nothing either tool struggles with. Google Sheets is the easier choice if you want to share the live view with a bookkeeper or business partner without emailing updated files back and forth. Excel handles a longer invoice history a little more comfortably as the row count grows. Whichever you pick, keep the aging report reading from the same invoice list your other financial sheets use — a separate copy that has to be updated twice is a copy that eventually goes stale.