Accounts Receivable Aging Report: How to Build and Use One

An accounts receivable (AR) aging report lists every unpaid customer invoice and groups the balances by how long they have been outstanding. It shows at a glance who owes you money, how much of it is overdue and which debts need attention first. This guide explains how the report works, how to build one in Excel, a fully worked example you can check line by line, and how to use the results.

What is an AR aging report?

An aging report (also called an aged receivables report or aged debtors report) takes the open balance on every customer invoice at a chosen date, works out how many days each one is past its due date, and places it in an aging bucket. The most common buckets are:

• Current — not yet due
• 1–30 days past due
• 31–60 days past due
• 61–90 days past due
• Over 90 days past due

The report is usually summarised by customer, with a total for each bucket. The grand total should equal the accounts receivable balance in your general ledger at the same date.

Why the aging report matters

• Collections. It tells you which customers to contact and in what order.
• Cash planning. Overdue balances are the least reliable receipts in a cash forecast. See the 13-week cash flow forecast guide for how receipts feed a weekly forecast.
• Credit control. A customer whose balance is drifting into older buckets may need a lower credit limit, or orders placed on hold.
• Month-end reporting. Lenders, auditors and managers often ask for an aged debtors report, and the aging profile is a common basis for estimating doubtful debts.

Information you need

For every open invoice you need:

• Customer name
• Invoice number and invoice date
• Payment terms (for example 30 days), or the due date itself
• Original invoice amount, less any payments, part-payments and credit notes applied to it
• The as-at date you are aging to — usually the last day of the month

Use open balances, not original invoice amounts. A part-paid invoice should appear only for the amount still owed, and unapplied customer payments or credit notes need to be matched to invoices before you age them.

How to build an AR aging report, step by step

1. List every open invoice

Export open invoices from your accounting system, or list them by hand, one row per invoice. The formulas below assume this layout: column A customer, B invoice number, C invoice date, D terms in days, E due date, F open balance, G days past due and H bucket.

2. Work out the due date

Due date = invoice date + payment terms in days. If some customers have special terms, enter the due date directly for those invoices.

3. Calculate days past due

Days past due = as-at date − due date, with anything not yet due shown as zero. In Excel, with the as-at date in cell J1, the due date in column E and the open balance in column F:

To check a single invoice past its due date, the free Date Difference Calculator counts the days between the due date and your as-at date, which is the number of days the invoice is overdue.

4. Summarise by customer and bucket

Build a summary table with customers down the side and the five buckets across the top, then fill it with SUMIFS. If the summary sits below the invoice list with customer names in A21 downwards and the bucket names in B20:F20:

Adjust the ranges to your own layout. Add a total row, a total column and a row showing each bucket as a percentage of the total.

5. Reconcile to the ledger

The report's grand total must equal the accounts receivable balance in the general ledger at the as-at date. If it does not, look for unapplied payments, credit notes not matched to invoices, invoices posted after the as-at date, or journals posted directly to the receivables control account.

Worked example: aging a small wholesaler's receivables

The figures below are fictional. A small office-supplies wholesaler ages its open invoices at 30 September 2026. Brookside Café has part-paid INV-0987, so only the open balance is aged.

Check one line: INV-1009 was issued on 28 July with 30-day terms, so it fell due on 27 August. From 27 August to 30 September is 34 days, which places it in the 31–60 bucket.

Percentages are rounded to one decimal place, so they may not add to exactly 100%.

Total receivables are 29,360. Of this, 13,630 (46.4%) is not yet due and 15,730 (53.6%) is overdue. Each column total adds across to the grand total, and the grand total should now be agreed to the receivables balance in the ledger.

How to read and act on the report

In the example, three things stand out:

• Concentration. Cedar Builders owes 16,030, or 54.6% of all receivables. A problem with one customer would hit cash hard.
• The oldest debt. Cedar's INV-0951 for 5,600 is 103 days past due. The older a debt gets, the harder it usually is to collect, so this is the first call to make.
• A part-payment pattern. Brookside Café paid part of a 68-day-old invoice. A part-payment can signal a dispute or cash pressure, and it is worth asking which.

A practical priority order is: largest balances in the oldest buckets first, then any broken payment promises, then accounts that are close to or over their credit limit. Record each contact — date, who you spoke to, what was agreed and the next action — so follow-up does not depend on memory.

Using aging to estimate doubtful debts

Many businesses estimate an allowance for doubtful debts by applying a higher percentage to older buckets. The rates below are illustrative assumptions only, not a standard; each business should set its own rates from its collection history and the accounting framework it reports under.

On these assumptions the estimated allowance is 3,794.60. Specific debts known to be uncollectible should be considered individually rather than left to a percentage.

Aging and DSO

Days sales outstanding (DSO) summarises how long, on average, customers take to pay. A simple version is:

If the wholesaler's credit sales for July to September (92 days) were 96,000, DSO is 29,360 ÷ 96,000 × 92 = 28.1 days. That is close to the 30-day terms most of these customers are on, yet the aging report still shows 53.6% of the balance overdue, including one debt over 90 days. DSO is an average, so use it alongside the aging profile, not instead of it.

DSO is also one of the inputs to the cash conversion cycle. You can work out the full cycle — DSO plus days inventory outstanding, less days payable outstanding — with the Cash Conversion Cycle Calculator.

Common mistakes and practical checks

• Aging original amounts. Always age the open balance after payments and credit notes.
• Leaving cash unapplied. A payment sitting on account makes the oldest invoices look unpaid. Apply it before running the report.
• Changing the as-at date. Use the same date as the ledger balance you reconcile to, usually month-end.
• Not reconciling the total. If the report total does not agree to the ledger, the report cannot be relied on.
• Ignoring credit balances. A negative balance usually means an overpayment or an unapplied credit note. Investigate it rather than letting it reduce the total silently.
• Running it too rarely. Monthly is the minimum. Weekly works better for active collections.

Frequently asked questions

Related resources

This guide is general information, not financial, accounting or legal advice. The worked example uses fictional figures. Written and reviewed by Ghayas Uddin, MS (Finance), financial analyst. Last reviewed 15 October 2026. Found an error? Email support@babadigit.com.