The aged trial balance is the number one management tool for debt collection. Adopted by all CFOs and credit managers, it allows you to instantly visualise the health of your customer portfolio and identify at-risk receivables before they become uncollectable. Yet many SMEs still do not have this tool or do not use it correctly. This guide explains how to build, read and exploit your aged trial balance, and how to automate its update for real-time management.
Definition of the aged trial balance
Simple definition
An aged trial balance (also known as an “aging report” or “accounts receivable aging schedule”) is an accounting table that classifies all customer receivables by age bracket. Its objective is to provide, at any given moment, a complete and structured view of all amounts owed by your clients.
In practice, each row corresponds to a client, and each column represents an age bracket:
| Client | Total due | Not yet due | 0-30 d | 31-60 d | 61-90 d | 91-120 d | 120+ d |
|---|---|---|---|---|---|---|---|
| Client A | €12,500 | €8,000 | €2,500 | €2,000 | - | - | - |
| Client B | €6,300 | - | - | €1,800 | €4,500 | - | - |
| Client C | €3,200 | - | - | - | - | €3,200 | - |
The aged trial balance is simultaneously a cash flow tool (which amounts to prioritise collecting?), a risk management tool (which clients are in difficulty?) and an accounting tool (provisions for doubtful receivables).
Accounting and legal framework
From an accounting standpoint, the aged trial balance is essential for establishing provisions for doubtful receivables in accordance with the prudence principle of accounting standards. A receivable overdue by more than 6 months should in principle be subject to an accounting impairment, and the aged trial balance provides the basis for that calculation.
In addition, auditors and banks (particularly during financing or factoring operations) regularly request an up-to-date aged trial balance. It is a reference document for evaluating the quality of a company’s receivables portfolio.
How to calculate and build an aged trial balance
Standard brackets
The standard age brackets used in an aged trial balance are as follows:
- 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 recommended
- 91 to 120 days overdue: doubtful receivable, legal action to consider
- Over 120 days overdue: potentially uncollectable receivable, mandatory provision
These brackets can be adapted according to your sector and collection policy. Some businesses use 15-day brackets for finer monitoring.
Building your aged trial balance in Excel
To build an aged trial balance manually in Excel, here is the method:
Required source data:
- Invoice number and due date
- Client name
- Total amount (including VAT)
- Status (unpaid / partially settled / settled)
Formula to calculate the number of days overdue:
= MAX(0, TODAY() - due_date)Formula to classify into the correct bracket:
=IF(days_overdue=0, "Not yet 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 to download: Manage your receivables with our free Excel aging report template.
Worked example: an SME with €50,000 in receivables
Take a services SME with €50,000 in customer receivables at a given point in time. Here is the typical breakdown that its aged trial balance might reveal:
| Bracket | 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+ days portion (€1,800) should be subject to an accounting provision. Collection priority should focus on the 61-90 day bracket (€6,500) before it moves into the doubtful category.
Impact on your business
Practical consequences
The aged trial balance has concrete implications across several dimensions of business management:
Cash flow and DSO: A well-monitored aged trial balance enables you to prioritise chasing the oldest and largest receivables, mechanically reducing DSO (Days Sales Outstanding) and improving cash flow. For more on this, see our article on DSO and its calculation methods.
Accounting provisions: Receivables overdue by more than 6 months require an impairment. Without an aged trial balance, provisions are often inaccurate, which distorts the accounting result and can create surprises at year-end.
Credit decisions: A client whose 90+ day receivables are growing month on month is an early warning signal of financial difficulty. The aged trial balance allows you to decide to limit the credit extended before a default occurs.
Collection KPI monitoring: The aged trial balance is the basis for calculating the collection rate, doubtful receivables rate and other key indicators. Our article on collection KPIs details the metrics to track.
Mistakes to avoid
Mistake 1: Only updating the aged trial balance monthly A monthly aged trial balance is insufficient for businesses with a significant volume of receivables. A weekly update, or even daily, is recommended.
Mistake 2: Including receivables subject to an ongoing dispute Disputed receivables should not be treated as ordinary overdue receivables. They must be identified separately to avoid distorting the analysis.
Mistake 3: Not distinguishing between not-yet-due and 0-30 day overdue receivables These two categories are very different in nature. Mixing not-yet-due receivables with recent delays masks the reality of the portfolio.
Mistake 4: Ignoring client concentration An aggregated aged trial balance can mask excessive concentration on a single client. It is important to analyse not just the brackets but also the distribution by client (a single client representing 80% of 90+ day receivables changes everything).
Tools for managing your aged trial balance
From Excel to automation
Excel is a good starting point, but its limitations become apparent quickly: tedious manual updates, formula error risks, absence of automatic alerts. Beyond around thirty clients or a significant receivables volume, automation becomes necessary.
Billabex generates a real-time aged trial balance, automatically updated as soon as an invoice is created or a payment is recorded. The platform allows you to:
- Visualise your aged trial balance in real time, by client or by bracket
- Receive automatic alerts when a receivable moves into a critical bracket
- Automatically trigger appropriate reminders based on the age of the delay
- Export the aged trial balance for your banks, auditors 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 aged trial balance permanently up to date. Discover Billabex →
To complete your financial analysis, see also our guide on normative working capital, since a mastered aged trial balance is the first condition for optimising your working capital requirement.
