The accounts receivable aging report is the number one management tool for debt collection. Used by all CFOs and credit managers, it instantly reveals the health of a receivables portfolio and identifies at-risk invoices before they become uncollectable. Yet many SMEs still lack this tool or fail to use it effectively. This guide explains how to build, read and leverage your aging report — and how to automate its update for real-time monitoring.
Definition of an Accounts Receivable Aging Report
Simple Definition
An accounts receivable aging report (called “balance âgée” in French) is an accounting schedule that classifies all customer receivables by age of delay. Its purpose is to provide, at a given point in time, a complete and structured view of all amounts owed by your customers.
Concretely, each row represents a customer, and each column represents an age bucket:
| Customer | Total Owed | Not Yet Due | 0-30 d | 31-60 d | 61-90 d | 91-120 d | 120+ d |
|---|---|---|---|---|---|---|---|
| Customer A | €12,500 | €8,000 | €2,500 | €2,000 | — | — | — |
| Customer B | €6,300 | — | — | €1,800 | €4,500 | — | — |
| Customer C | €3,200 | — | — | — | — | €3,200 | — |
The aging report serves simultaneously as a cash flow tool (which amounts to collect as priority?), a risk management tool (which customers are in difficulty?), and an accounting tool (provisions for doubtful debts).
Accounting and Regulatory Context
From an accounting standpoint, the aging report is essential for establishing provisions for doubtful debts in accordance with the prudence principle. In France, under the Plan Comptable Général (PCG), a receivable overdue by more than 6 months must in principle be depreciated, and the aging report provides the calculation basis.
Auditors and banks (particularly for financing or factoring operations) regularly request an up-to-date aging report. It is a reference document for assessing the quality of a company’s receivables portfolio.
How to Build an Accounts Receivable Aging Report
Standard Age Buckets
The standard age buckets used in an aging report are:
- Not yet due: invoices whose due date has not yet been reached
- 0 to 30 days overdue: low delay, first-level amicable reminder
- 31 to 60 days overdue: moderate delay, urgent follow-up
- 61 to 90 days overdue: significant delay, formal demand letter recommended
- 91 to 120 days overdue: doubtful receivable, legal action to consider
- Over 120 days overdue: potentially uncollectable, mandatory provision
These buckets can be adapted to your industry and collection policy. Some companies use 15-day buckets for more granular monitoring.
Building Your Aging Report in Excel
To build an aging report manually in Excel, here is the method:
Required source data:
- Invoice number and due date
- Customer name
- VAT-inclusive amount
- Status (unpaid / partially settled / settled)
Formula to calculate days overdue:
= MAX(0, TODAY() - due_date)Formula to classify into the correct bucket:
=IF(days_overdue=0, "Not due",
IF(days_overdue<=30, "0-30 d",
IF(days_overdue<=60, "31-60 d",
IF(days_overdue<=90, "61-90 d",
IF(days_overdue<=120, "91-120 d", "120+ d")))))Our dedicated article provides a complete, free Excel template: Excel aging balance template — simplify your accounts receivable tracking.
Worked Example: An SME with €50,000 in Receivables
Consider a services SME with €50,000 in customer receivables at a given point in time. Here is a typical distribution that its aging report might reveal:
| Bucket | Amount | % of total | Status |
|---|---|---|---|
| Not yet due | €18,000 | 36% | Normal |
| 0-30 days | €12,000 | 24% | Acceptable |
| 31-60 days | €8,500 | 17% | Monitor |
| 61-90 days | €6,500 | 13% | Urgent |
| 91-120 days | €3,200 | 6.4% | Doubtful |
| 120+ days | €1,800 | 3.6% | Potentially uncollectable |
Analysis: 23% of receivables exceed 60 days (€11,500), which is a warning signal. The 120+ day bucket (€1,800) should be subject to an accounting provision. Collection priority should focus on the 61-90 day bucket (€6,500) before it slides into the doubtful category.
Impact on Your Business
Practical Consequences
The aging report has concrete implications across multiple dimensions of business management:
Cash flow and DSO: A well-monitored aging report enables prioritisation of follow-up on the oldest and largest receivables, mechanically reducing DSO (Days Sales Outstanding) and improving cash flow. For more, see our article on DSO calculation methods.
Accounting provisions: Receivables overdue by more than 6 months require depreciation. Without an aging report, provisions are often inaccurate, distorting financial results and potentially creating surprises at year-end close.
Customer credit decisions: A customer whose 90+ day receivables bucket grows month after month is an early warning signal of financial difficulties. The aging report enables decisions to limit credit exposure before a default occurs.
Collection KPI monitoring: The aging report is the calculation basis for collection rate, doubtful receivables rate and other key indicators. Our article on collection performance KPIs details the metrics to track.
Common Mistakes to Avoid
Mistake 1: Only updating the aging report monthly A monthly aging report is insufficient for companies with significant receivables volumes. Weekly — or even daily — updates are recommended.
Mistake 2: Including disputed receivables Disputed receivables should not be treated as ordinary overdue receivables. They must be identified separately to avoid distorting the analysis.
Mistake 3: Not distinguishing not-yet-due receivables from 0-30 day delays These two categories have very different natures. Mixing not-yet-due receivables with recent delays masks the true picture of the portfolio.
Mistake 4: Ignoring customer concentration An aggregated aging report can mask excessive concentration on a single customer. It is important to analyse not only the buckets but also the distribution by customer (one customer representing 80% of 90+ day receivables changes everything).
Tools for Managing Your Aging Report
From Excel to Automation
Excel is a good starting point, but its limitations quickly become apparent: tedious manual updates, formula error risks, no automatic alerts. From around 30 customers or a significant receivables volume, automation becomes necessary.
Billabex generates a real-time aging report, automatically updated as soon as an invoice is created or a payment is recorded. The platform enables you to:
- View your aging report in real time, by customer or by bucket
- Receive automatic alerts when a receivable moves into a critical bucket
- Automatically trigger appropriately-timed reminders based on delay age
- Export the aging report for your bank, auditor or factoring partners
- Monitor your DSO and collection KPIs in a dedicated dashboard
No more manually updating Excel files: Billabex integrates with your invoicing software and keeps your aging report permanently up to date. Discover Billabex →
For a free Excel aging report template, visit: Excel aging balance template — simplify your accounts receivable tracking.
