Accounts Receivable Aging Report: Put a Dollar Figure on “Chasing It”
Aging buckets are a deadline map, not an accounting nicety — every day past due raises the chance the dollar is gone, and a loss rate makes that probability visible.
Every overdue invoice is a bet that gets riskier as it ages, but “I’ll chase those up” is not a number. Anaging reportgroups receivables into time buckets and applies a credit-loss rate to each, turning vague anxiety about unpaid invoices into a single, defensible allowance. It answers two questions at once: how much cash is genuinely at risk this month, and how much of your receivable balance should realistically be written down under current expected credit loss rules.
In this guide, you’ll learn what aging buckets to use, how the calculated loss rate becomes anallowance for doubtful accounts, how that allowance feeds the balance sheet and acontinuity schedule, and how to build an Excel report that flags overdue invoices the moment they cross into a riskier bucket.
Quick Answer: What an Aging Report Does
An aging report sorts accounts receivable into buckets — typicallycurrent,1–30, 31–60, 61–90, and 91+ dayspast due — and applies acredit-loss percentageto each bucket. Everything past due is summed and multiplied by its bucket rate to produce anallowance for doubtful accounts, which is subtracted from gross receivables to report the net realizable value.
For example, a matrix that assigns 5% to current, 15% to 30–59 days, 25% to 60–89, 50% to 90–119, and 90% to 120+ translates a $100,000 past-due mix into a concrete allowance dollar amount. The older the bucket, the higher the assumed loss — so the report doubles as a collection priority list with money attached.
L2 — How Aging and the Allowance Actually Work
Buckets are the straight-line story of collection risk
Companies ageby invoice date(not the customer account), which separates one reliable customer from the few overdue invoices that may never pay. As an invoice moves from current into 31–60 and beyond, both the historical chance of default and the practical cost of chasing rise. Credit insurers and lenders watch the creep from “current” into “more than 90 days” because it is the earliest warning of write-offs.
The loss matrix turns buckets into a number
Under the current expected credit loss model, aprovision matrixassigns a default rate to each aging bucket. The rate is derived from historical loss experience, then adjusted for management’s expectation of current and future conditions. Gross receivables in each bucket × that bucket’s rate = the expected loss for that slice; summing the slices produces the allowance.
| Aging bucket | Estimated credit loss % |
| Current (0–29 days) | 5% |
| 30–59 days | 15% |
| 60–89 days | 25% |
| 90–119 days | 50% |
| 120+ days | 90% |
This is acollectiveestimate — a provision matrix applied to similar customers — the standard approach most companies use for trade receivables. (Rates shown are illustrative of a typical matrix; your real rates should follow your own history under ASC 326.)
Net receivables and the continuity schedule
Net receivables =gross AR − allowance. Movements in the allowance — beginning balance, additions for expected credit losses, write-offs charged against the allowance, and any recoveries — are summarized in acontinuity schedulethat auditors expect to reconcile. A write-off removes the receivable from the booksagainst the allowance already set up, which is why a good aging report feeds straight into both calculations.
L3 — Boundaries, Assumptions, and Limitations
- Rates are estimates, not facts.The matrix reflects management’s judgment of historical experience plus forward-looking expectations. Every entity’s rates differ; a “21% industry average” is a benchmark, not your policy.
- Age by invoice date, net against payables.Some companies reduce aged receivables by customer payables where contractual offset rights exist — know exactly which basis your books use or the buckets are misleading.
- Insured receivables distort raw aging.If a portion of AR is credit-insured, the effective loss rate is far lower than the nominal bucket rate (insured exposure may be ~15% vs 85% of the balance). One-size matrix without the insurance overlay overstates the allowance.
- Write-offs need a continuity trail.Reversals and write-offs must tie to prior additions; an aging report that isn’t reconciled to the allowance continuity schedule will not survive an audit.
L4 — How to Build the Report in Excel
- List every open invoice:customer, invoice date, invoice number, amount, currency, and due date.
- Compute days past duewith=TODAY()-DueDate(store a fixed reporting date to avoid the sheet changing daily), then bucket it: Current (0–29), 30–59, 60–89, 90–119, 120+.
- Apply the loss matrix:sum each bucket and multiply by its credit-loss percentage; total the slices into the allowance.
- Reconcile to the continuity schedule:opening allowance + additions − write-offs (+/− foreign-currency translation) = closing allowance; confirm it equals the aging-derived figure.
- Add a collection view:a “days past due” column and a “subsequent collection %” measure let you see that, say, 26% of the balance was collected since period end — proof of how recoverable the “old” buckets actually are.
Worked example — a $1.2M receivable mix
| Aging bucket | Balance | Loss rate | Allowance slice |
| Current (0–29) | $520,000 | 5% | $26,000 |
| 30–59 days | $300,000 | 15% | $45,000 |
| 60–89 days | $200,000 | 25% | $50,000 |
| 90–119 days | $120,000 | 50% | $60,000 |
| 120+ days | $60,000 | 90% | $54,000 |
| Total | $1,200,000 | $235,000 allowance |
Gross receivables $1.2M minus a $235,000 allowance =$965,000 net. The report now names the problem precisely: $60,000 sitting in the 120+ bucket is effectively written off unless recovered, and the 90–119 bucket is the next escalation.
L5 — Common Mistakes, Limitations & FAQ
Common Mistakes
- Aging by account instead of by invoice.One big customer with one late invoice hides the problem inside a healthy total.
- Using a single flat reserve rate.One “5% of everything” guess ignores that a 120-day invoice is far riskier than a current one.
- Forgetting to reconcile to the allowance.If the aging-derived allowance doesn’t match the continuity schedule, auditors will flag it and your balance-sheet figure is unverifiable.
- Ignoring credit insurance.A materially insured AR book with a nominal bucket rate massively overestimates losses.
- Letting the sheet “age” on live data.Compute days past due against a fixed reporting date, or Monday’s report differs from Friday’s.
Limitations
- A spreadsheet lacks real-time collection status.Payment promises, disputes, and promises-to-pay belong in an AR ledger or system of record, not a static sheet.
- Bucket definitions vary.“Current” may mean 0–29 or 0–30 days, and some reports start past-due at 1 day. Match your buckets to your accounting policy so the numbers are comparable month to month.
- Historical rates need periodic recalibration.A loss matrix built on last year’s experience is stale the moment collections or the economy change.
FAQ
What are standard aging report buckets?Usually Current, 1–30, 31–60, 61–90, and over 90 (91+) days past due; some split more finely, e.g., 90–119 and 120+.
How is the allowance for doubtful accounts calculated?Multiply each bucket’s total by its credit-loss rate (from historical experience, adjusted for future expectations) and sum the results; subtract that from gross AR to get net receivables.
Is an aging report required by GAAP?Aging is a practical input to the expected-credit-loss allowance estimate (ASC 326), and disclosures of aged receivables, the allowance, and its continuity are common in SEC filings under ASC 310/326 — not a mandated single format, but an expected audit support.
Why does “old” money matter so much?Collection likelihood falls the longer an invoice sits; higher loss rates on older buckets make the risk visible and justify escalation before write-off.
Can Excel handle this?Yes, for a normal trade-receivable book. Beyond a few hundred invoices, or when you need real-time payment status, use an accounts-receivable system.
Conclusion
An aging report is how you turn “we’re getting behind on collections” into a number you can act on. Bucket receivables by days past due, apply a defensible credit-loss rate to each, and reconcile the resulting allowance back to the continuity your books require. The report does double duty — it prices the risk in your receivable balance and tells collectors exactly which invoices to chase first.
The same discipline that keeps your receivables honest applies to where they came from: abudget planner templatekeeps the revenue side predictable, and anexpense trackerverifies that every dollar shipped actually left — so the receivables you chase are real and the cash you collect lands in a ledger that matches.Use Office Toolkitgives you the structure to keep the whole cash cycle — bill, track, collect — in one place.