Blog · Finance metrics and formulas · Finance and FP&A teams
Accounts receivable aging groups unpaid invoice balances by how far they are past due. Build a report from an invoice export, reconcile its buckets to the ledger and identify the customers behind overdue balances.
Accounts receivable aging groups open customer invoices by days past due at a fixed reporting date. It shows where overdue cash sits, while the reconciliation proves that the collection list covers the receivables balance you are reporting.
Export the open-item receivables register with invoice ID, customer ID, entity, invoice date, due date, currency and remaining balance. Include the collection owner and dispute status if available. Use remaining balances after payments and credits posted through the cutoff, rather than original invoice amounts.
An operational aging report is distinct from the aging of accounts receivable method, which applies documented loss assumptions to estimate an allowance. Build and reconcile the operational report first.
The OCC's Accounts Receivable and Inventory Financing handbook describes receivables aging as identifying customer balances and their payment status. Its lending context also explains why concentration and receivables quality matter alongside the headline total.
Use one reporting date for every row. A current open-item download does not necessarily reconstruct a historical month-end: an invoice settled after month-end may have disappeared from today's open list. Request the historical report or a complete transaction history when reviewing an earlier period.
Days past due = Maximum of zero and (Reporting date − Contractual due date)
Overdue share = Open balance past due / Total open receivables
Invoices due on the reporting date remain current in this example. Past due begins the following day. An invoice-date report answers how long a balance has existed; a due-date report answers whether the customer has exceeded agreed terms.
In Excel, put the reporting date in K1. Use columns A through G for invoice ID, customer, due date, open balance, days past due, bucket and validation status. Check that C contains actual dates and D contains numeric balances. In G2:
=IF(AND(ISNUMBER(C2),ISNUMBER(D2)),"OK","CHECK INPUT")
Resolve flagged rows before producing totals. Then E2 is:
=IF(G2="OK",MAX(0,$K$1-C2),"")
In F2, assign the mutually exclusive bucket:
=IF(G2<>"OK","CHECK INPUT",IF(E2=0,"Current",IF(E2<=30,"1-30",IF(E2<=60,"31-60",IF(E2<=90,"61-90","Over 90")))))
Fill all three formulas down. This prevents a missing due date from silently becoming a very old receivable. Confirm the report date itself is valid before running the workbook.
The following fictional distributor reports at September 30, 2026. Every amount is a remaining USD invoice balance; no credits or unapplied cash are present.
| Invoice | Customer | Due date | Open balance (USD) | Days past due | Bucket |
|---|---|---|---|---|---|
| B101 | Birch Supply | October 15, 2026 | 24,000 | 0 | Current |
| C201 | Cedar Stores | September 30, 2026 | 6,000 | 0 | Current |
| B102 | Birch Supply | September 15, 2026 | 18,000 | 15 | 1–30 |
| C202 | Cedar Stores | August 20, 2026 | 12,000 | 41 | 31–60 |
| D301 | Delta Industrial | July 20, 2026 | 8,000 | 72 | 61–90 |
| D302 | Delta Industrial | June 15, 2026 | 7,000 | 107 | Over 90 |
| Total | 75,000 |
For spreadsheet labels, use the plain hyphens shown in the formulas. The display table uses en dashes only for readability. Sum each label to obtain the report:
| Bucket | Calculation from invoice rows | Open balance (USD) |
|---|---|---|
| Current | 24,000 + 6,000 | 30,000 |
| 1–30 days past due | 18,000 | 18,000 |
| 31–60 days past due | 12,000 | 12,000 |
| 61–90 days past due | 8,000 | 8,000 |
| Over 90 days past due | 7,000 | 7,000 |
| Total | Sum of all buckets | 75,000 |
The overdue balance is $18,000 + $12,000 + $8,000 + $7,000 = $45,000. The overdue share is $45,000 / $75,000 = 60.0%, shown to one decimal place; dollar totals and day counts are exact.
The bucket sum must equal the invoice-register total. Then the register must tie to the gross trade receivables control account for the same entity, currency and cutoff. In this example that ledger control balance is supplied as $75,000, so the difference is zero.
Do not reconcile gross invoices directly to a balance-sheet number net of the credit-loss allowance. Show the allowance separately. Retain a reconciliation for credits, unapplied receipts and manual journals instead of deleting those records to make the aging look clean.
For the six example rows, this Excel check must return zero:
=SUM(D2:D7)-SUMIF(F2:F7,"Current",D2:D7)-SUMIF(F2:F7,"1-30",D2:D7)-SUMIF(F2:F7,"31-60",D2:D7)-SUMIF(F2:F7,"61-90",D2:D7)-SUMIF(F2:F7,"Over 90",D2:D7)
This tests bucket coverage. It does not replace the separate ledger tie or a check for duplicated invoice IDs.
Summarize overdue dollars by customer. With a customer name in J2, the formula is:
=SUMIFS($D$2:$D$7,$B$2:$B$7,J2,$E$2:$E$7,">0")
Microsoft documents SUMIFS as summing values that meet multiple criteria. Covirage is not affiliated with Microsoft.
Birch has $18,000 overdue, Cedar $12,000 and Delta $15,000. Birch leads by overdue dollars, while Delta's entire overdue balance is older than 60 days. Assign follow-up by amount, age, dispute cause and promised payment date, rather than sorting only on the oldest invoice.
Use stable customer IDs and an agreed parent and subsidiary hierarchy when several billing accounts belong to one group. The AR turnover ratio provides the broader collection-speed measure; aging identifies the invoices underneath it. A month-to-month flux analysis can explain which new invoices, receipts and adjustments moved the balance.
Upload your invoice register and receivables control totals to Covirage; its tools compute aging buckets, compare overdue customer balances and check the totals. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for finance teams for the supporting reporting context.
Use due date when your purpose is to measure lateness against payment terms. Invoice-date aging measures time since billing instead. Both can be useful, but label the basis clearly and do not compare reports that use different definitions.
A common layout is current, 1–30 days past due, 31–60, 61–90 and over 90. Companies can choose different intervals to match their terms and collection process. Every included invoice must appear in exactly one bucket.
Common causes include different cutoff dates, unapplied cash, credit balances, manual journal entries and different entity or currency filters. Reconcile the subledger to the gross trade receivables control account, then explain every adjustment before using the report.
No. Age signals that a balance needs review; it does not establish an uncollectible amount. Payment disputes, customer circumstances and expected recoveries matter. Allowance estimation applies a separate accounting assessment rather than declaring every overdue invoice a loss.