How to Build, Read and Act on an Accounts Receivable Aging Report Every Week

An accounts receivable aging report is the list of who owes you money and how late they are. For a small business, it is often the most direct view of cash you have already earned but not yet collected. Built correctly and read every week, it tells you exactly who to call on Monday morning.

This guide covers how to build the report from an invoice export, how to make sure it ties to your books, how to read it, and a weekly routine to collect overdue invoices in order of dollar impact.

What an Accounts Receivable Aging Report Shows

Xero describes an AR aging report as one that groups unpaid customer invoices into date ranges to show how long each amount has been outstanding. Each row is a customer. Each column is an age bucket, measured from the invoice due date:

• Current: not yet due, or due today.
• 1-30 days past due.
• 31-60 days past due.
• 61-90 days past due.
• 90+ days past due.

Those are the standard buckets in Xero’s guide, and other platforms label them in similar ways. Most accounting software produces an AR aging report in a few clicks, but many small businesses invoice from a job system, POS or spreadsheet and need to build it themselves.

How to Build an AR Aging Report From an Invoice Export

Start with an export of every open invoice, plus every payment and credit memo. You need these columns: customer, invoice number, invoice date, due date, original amount, payments applied, credits applied and open balance.

1. Age by due date, not invoice date. An invoice dated October 1 on net 30 terms is current until October 31. Days past due equals the report date minus the due date. If you age by invoice date, every net 30 customer looks a month late.
2. Use the open balance after partial payments. A $5,000 invoice with $2,000 paid belongs in its bucket at $3,000. The age stays tied to the original due date; a partial payment does not reset the clock.
3. Apply credits to the invoices they belong to. A credit memo for a return or price adjustment should reduce a specific invoice. Floating credits make one customer look worse than they are.
4. Find unapplied payments. Money received but not matched to an invoice is a common source of error. The customer has paid, but the invoice still shows open. List these on their own line until they are applied.
5. Set one report date. Run the report as of a fixed date (the close of business Friday, for example) so the buckets mean the same thing every week.

In a spreadsheet, a simple formula assigns each invoice to a bucket based on days past due, and a pivot table by customer produces the grid.

Checks So the Report Ties to the Balance Sheet

The total of your AR aging report must equal the accounts receivable balance on your balance sheet as of the same date. If it does not, the report is not ready to act on. Common reasons for a difference:

• Different dates. The aging is as of today and the balance sheet is as of month-end.
• Journal entries posted straight to AR. These change the balance sheet but are not tied to any customer invoice.
• Unapplied payments or credits that the report shows differently from the ledger.
• Cash basis reporting. A balance sheet run on a cash basis may not show receivables at all. Use accrual basis for this check.
• Voided or duplicate invoices still sitting open in the export.

Write down the difference and the reason each week. Done means the difference is zero or every dollar of it is explained. This same tie-out is a step in our month-end close checklist.

How to Read an AR Aging Report: A Worked Example

Here is an illustrative report for an invented company. The customers and amounts are made up for this example.

Customer (example)	Current	1-30	31-60	61-90	90+	Total
Customer A	$8,000	$6,000	$0	$0	$0	$14,000
Customer B	$2,500	$0	$4,200	$3,100	$0	$9,800
Customer C	$1,200	$1,800	$0	$0	$0	$3,000
Customer D (disputed)	$0	$0	$0	$0	$2,600	$2,600
Customer E	$3,000	$900	$0	$0	$0	$3,900
Total	$14,700	$8,700	$4,200	$3,100	$2,600	$33,300

Read it in three passes:

• Concentration. Customer A holds $14,000 of $33,300, about 42% of everything owed. One slow payer at that size can squeeze cash for the whole business. Most of A’s balance is current, so the risk is size, not lateness.
• Customers sliding buckets. Customer B has $7,300 past 30 days and still placed a new order this month. Compare to last week’s report: if B’s 31-60 amount moved to 61-90 with no payment, the pattern is getting worse.
• Disputed invoices. Customer D’s $2,600 is over 90 days because of a disagreement about the work. Reminders will not fix it. It needs a decision: resolve the dispute, issue a credit, or take it to your accountant.

Past due in total is $18,600 ($33,300 minus $14,700 current). That is the pool your weekly routine works on.

A Weekly Routine to Collect Overdue Invoices

Rank past-due customers by dollars at risk, with older buckets weighted higher, and work down the list. In the example, the order is B, then D, then A, then C and E.

1. Call the largest old balances first. Customer B gets a phone call, not an email. Ask when payment will be made and for how much, and write the promised date in the report.
2. Resolve disputes directly. For Customer D, the owner or project lead calls to settle what is in question. Each week a dispute sits, it gets harder to collect.
3. Send reminders for the 1-30 bucket. Customers A, C and E get a polite reminder with the invoice attached and a payment link. Most accounting software can automate these.
4. Hold new orders past an agreed threshold. Set a rule in writing, such as no new work for a customer with a balance over 60 days until it is paid or on a plan. Apply it the same way to every customer.
5. Offer a written payment plan. For a customer who wants to pay but cannot clear the balance, agree to fixed amounts on fixed dates. Track each installment on the report.
6. Log the outcome. Add a notes column with the date, who was contacted, what was promised and the next step. Next week starts from those notes.

The whole routine usually fits in under an hour once the report builds cleanly. Questions about write-offs or bad debt reserves belong with your accountant.

Track DSO Alongside the Aging Report

Days sales outstanding (DSO) summarizes the aging report in one number. Xero defines it as the average number of days it takes to collect payment after a credit sale, with this formula:

DSO = (Accounts receivable ÷ Total credit sales) × Number of days in the period

Use credit sales only, since cash sales have no collection period, and keep receivables and sales for the same period. Continuing the example, if the company made $45,000 in credit sales in a 30-day month and ends with $33,300 in receivables, DSO is $33,300 ÷ $45,000 × 30, or about 22 days. Compare that to your standard terms. If terms are net 30 and DSO keeps rising toward them, collections are slipping. DSO is one of the eight measures in our guide to building a small business KPI dashboard.

How We Help

We build AR aging reports that pull from your invoicing system, tie out to the balance sheet and rank the weekly call list by dollar impact. See our reporting and data services for details.

Frequently Asked Questions