Accounts receivable aging report: build it, reconcile it, act on it

What an aging report tells you—and what it does not

An aging report answers a precise question: which customer balances were still open at this cutoff, and how old were they? It can reveal overdue exposure and help prioritize investigation. It does not tell you why an invoice remains unpaid or when the cash will arrive. Those require invoice and conversation context.

Stripe's explanation of accounts receivable aging covers unpaid invoices, credit memos and age ranges. Its worked example measures time from invoice issue. For the collection workflow here, we measure time from the due date instead. Both definitions can be useful, but they answer different questions. Read your accounting system's settings before treating a bucket as overdue.

For the wider process around billing, ownership and weekly review, use our accounts receivable guide for agencies. This guide focuses on getting the report itself right and turning its exceptions into action.

Set the cutoff before exporting the data

Choose one as-of date, legal entity, currency and accounting basis. A report for 19 September must reflect transactions posted through that cutoff. A payment posted on 20 September can clear today's balance while leaving the invoice open in the 19 September snapshot. Rebuilding a past report from today's remaining balances will miss that distinction.

1. Export invoices and the payment, credit and adjustment allocations needed to reconstruct the cutoff. Use the accounting system's historical aging report where available.
2. Keep a stable invoice ID, customer legal entity, invoice date, due date, original amount, applied payments, applied credits and remaining balance. Include currency on every row.
3. Keep unused customer credits and unapplied receipts visible in a separate reconciliation. Do not silently distribute them across old invoices.
4. Check missing due dates, duplicate invoice IDs, negative balances and allocations dated after the cutoff. Resolve or label exceptions instead of forcing them into Current.
5. Record the report settings and export time. Compare reports with the same basis; a setting change can move every bucket without any money changing hands.

A worked example with partial payments and credits

These six fictional invoices illustrate a service firm's report as of 19 September 2026. All payments and credit notes shown have been posted and applied to the stated invoices by the cutoff. There are no other invoice adjustments. Open balance equals original invoice amount minus applied payments minus applied credits.

Use calendar days. An invoice due today or in the future is Current. An invoice due yesterday is 1 day past due. The overdue ranges below are inclusive and do not overlap: 1–30, 31–60, 61–90 and 91+ days. A partial payment reduces the amount in the original invoice's bucket; it does not restart that invoice's age.

The overdue subtotal is $12,700: $5,000 + $4,000 + $2,500 + $1,200. It is 58.5% of the $21,700 positive open-invoice balance, rounded to one decimal place. That is a description of this fictional ledger, not a benchmark. Label the denominator, particularly when the ledger also contains customer credits.

Reconcile the report before contacting clients

A positive open-invoice total can differ from net AR because the customer ledger also holds credits not yet allocated to invoices. In this example, a $700 unused credit belongs to Pine and a $900 unapplied receipt belongs to Reed. Both have already been posted as credits to the same AR control account and customer subledger; neither is included in the applied amounts above.

Your accounting system may present these credits within its aging report, in a separate column or in another account. Follow the actual postings. A receipt merely visible on a bank statement, but not yet posted to AR, cannot be subtracted in the same bridge. Nor should you subtract a credit again if the report already includes it.

Do not tell Reed to pay $2,500 before investigating its $900 receipt. Confirm the remittance and allocate it under the normal controls. If it belongs to R-505, that invoice becomes $1,600 open and stays in 61–90 days at this cutoff. Allocation changes the positive invoice total and the unapplied-cash line by the same $900; net AR remains $20,100. A credit belonging to one customer cannot offset another customer's debt.

• Check whether one report includes tax, retainage or other receivable accounts that the other excludes.
• Look for direct general-ledger postings that have no matching customer-subledger entry.
• Confirm transaction dates, exchange-rate treatment and whether the system includes draft or unposted records.
• Investigate payments, credits, write-offs and reversals around the cutoff. Keep an explanation and owner for each remaining difference.

Turn the report into a reason-and-owner queue

Age helps identify exposure; the reason unpaid tells you what to do. Add an internal owner, next action and review date to each exception. Prioritize amount, missed commitments, unresolved disputes and client context together. The oldest invoice need not be the first email of the day.

For Pine's administrative issue, use the missing purchase-order invoice guide. For other collection decisions, follow our overdue-invoice workflow. Keep a dispute in the aging report until the accounting record changes; pausing reminders should not make the balance disappear.

Feed confirmed commitments and documented assumptions into a separate 13-week cash flow forecast. A 61-day-overdue invoice is not automatically a receipt next week, and a current invoice is not guaranteed to arrive on time.

Copy the formulas for a small invoice-level report

For a single-currency sheet, put the fixed as-of date in L1 as a real date value. Use A for invoice ID, B for invoice date, C for due date, D for original amount, E for payments applied by the cutoff, F for credits applied by the cutoff, G for open balance, H for days past due and I for the bucket. These formulas assume the simple invoice treatment used above; keep other adjustments explicit when needed.

G2 — Open balance
=D2-E2-F2

H2 — Days past due
=IF(OR(C2="",G2<=0),"",MAX(0,$L$1-C2))

I2 — Bucket
=IF(G2<=0,"Closed / credit",IF(C2="","Missing due date",IF(C2>=$L$1,"Current",IF(H2<=30,"1-30",IF(H2<=60,"31-60",IF(H2<=90,"61-90","91+"))))))

Total for the 1-30 bucket
=SUMIF(I:I,"1-30",G:G)

Total positive open invoices
=SUMIF(G:G,">0",G:G)

Fill the row formulas down and change the quoted bucket label for each summary total. Check that the five age buckets plus any Missing due date exception total equal the positive open-invoice total. Zero and negative balances need separate review. Some spreadsheet locales use semicolons instead of commas. Do not use TODAY() for a report you need to reproduce later: tomorrow's date changes the result.

Common questions when reviewing an aging report

Does an invoice become overdue on its due date?

In this report, an invoice due today is Current and becomes 1 day past due the following calendar day. A contract, local rule or internal policy may affect payment timing and how weekends are handled. Document that separately; do not mix aging conventions between reports.

Should a disputed invoice be removed from aging?

No: a dispute alone does not settle the invoice. Flag the disputed amount and pause unsuitable reminders while the authorized people resolve it. Approved credit notes, payments or other posted adjustments then change the balance under your accounting policy.

Is the average age of this report the same as DSO?

No. An average of open-invoice ages describes the invoices still outstanding at one date. Days sales outstanding relates an AR balance to credit sales over a period. Neither measure is the same as the actual average time taken to pay a set of settled invoices.