If you came here looking for a free AR aging report Excel template, you can download a clean one at the end of this post. The version we use has four buckets — 0-30, 31-60, 61-90, 90+ — a per-client total, and conditional formatting that turns rows red after 60 days.
But before you download it: a template that worked at 10 invoices a month is a different tool at 50 invoices a month, and it’s a liability at 200. The break points are not gradual — they hit at specific volumes, and the failure mode each time is “I thought this client paid.”
What an AR aging report does
An AR aging report — sometimes called an aging schedule — lists every unpaid invoice grouped by how long it has been overdue. The standard buckets are 0-30, 31-60, 61-90, and 90+ days past due date. The point of the bucketing is statistical: invoices in the 0-30 bucket recover at ~95% on average; invoices in the 90+ bucket recover below 30%. Bucketing forces you to act before time costs you money.
Every accounting platform builds this report automatically: QuickBooks calls it “A/R Aging Summary,” Xero calls it “Aged Receivables,” FreshBooks calls it “Accounts Aging.” If you have one of those tools and your invoices all get marked “paid” automatically, you don’t need an Excel template — your platform already has this.
The template (and how to use it)
Download: ar-aging-report-template.xlsx (10 KB, no signup). The matching invoice tracker and bank reconciliation worksheets are at /templates.
The structure:
| Column | Purpose |
|---|---|
| Invoice # | Reference for cross-checking |
| Client | Counterparty name |
| Issued | Invoice issue date |
| Due | Due date — usually issue + 14 or 30 days |
| Amount | Outstanding balance |
| Days overdue | =TODAY() - Due if positive, else 0 |
| Bucket | Nested IF returning “0-30”, “31-60”, “61-90”, “90+“ |
| Paid? | Manual checkbox |
Summary section at the bottom uses SUMIFS formulas to total each bucket and a COUNTIFS for the count of invoices per bucket. Conditional formatting flags 31-60 yellow, 61-90 light red, 90+ dark red automatically. For per-client roll-up, sort the table by Customer or add a SUMIF by client name in a side column.
This works fine until it doesn’t. Three things break.
Break #1: the “amount collision” problem
Two clients each owe you $1,500. The bank statement shows three $1,500 deposits this month. Which invoice does which deposit match? With an Excel template, you have to read the counterparty name on the bank line manually. This is fine at 10 deposits/month. At 50, you stop reading carefully, you check the wrong box, and the invoice stays marked unpaid even though it’s been paid. Six weeks later you send a dunning email to a client who already paid you. The client never works with you again.
The fix in a proper reconciliation tool is automated counterparty matching using fuzzy name similarity on the bank’s memo/description column. A spreadsheet can’t do this without a custom macro.
Break #2: partial payments
A client wires you $1,800 against a $2,000 invoice. They tell you on Slack “I’ll send the rest next week” and forget. In your Excel template, the invoice has one Paid checkbox — checked or unchecked. You either mark it paid (wrong — you’re missing $200) or unpaid (wrong — you have $1,800 in the bank). Either way, your AR balance is wrong.
A partial payment needs to track applied amount vs. invoice amount, plus the remainder as a separate “still owed” line. Excel can do this with another column, but the second client who pays partially will not match the same way, and by the fifth one you’ve built a half-working CRM in spreadsheet form.
Break #3: the “they paid net of VAT” problem
This one is geographic. In Poland, Germany, the Netherlands — anywhere with reverse-charge VAT rules — B2B clients sometimes wire you the net amount instead of the gross amount on the invoice. The invoice says €1,230 (€1,000 net + 23% VAT). The wire shows up as €1,000. Your spreadsheet says unpaid because the amount doesn’t match.
This is not a fraud problem; the client genuinely owes you only the net amount under reverse-charge rules, and your accounting system has to record it correctly. But an Excel template doesn’t know that ±23% is a valid match. You manually back-calculate every time, and at 50 invoices a month you start missing some.
When to graduate
The honest rules of thumb:
- Under 20 invoices/month: Excel template is fine. Just be disciplined about checking it weekly.
- 20-50 invoices/month: Excel template plus a recurring 1-hour reconciliation block on your calendar. Errors creep in but you can catch them.
- 50+ invoices/month: graduate to a dedicated reconciliation tool. The 30 minutes you save per week is worth the $24/month, and the error rate drops to near-zero because the matching is deterministic.
checkunpaidinvoices.com is the lightweight option — drop a bank CSV and an invoice CSV, get the four-bucket result in 30 seconds. Free tier handles up to 50 invoices per run. If you’d rather use a full accounting suite, QuickBooks Online and Xero both have AR aging built in.
If the Excel template hits a break point, drop the same CSVs into the bank reconciliation tool — partial payments + VAT/net handled automatically. 30 seconds, no signup.
FAQ
What’s the difference between AR aging and DSO? AR aging is the snapshot — how much you’re owed, sliced by how late it is right now. DSO (days sales outstanding) is the trend — on average, how many days does it take you to get paid. You want AR aging to spot the laggards; you want DSO to know if your collections process is getting better or worse over time.
Should I send the aging report to my clients? No. The aging report is internal. To the client, you send a statement of account — a summary of their open invoices, with totals.
Does QuickBooks have this built in? Yes — “Reports → Sales and customers → A/R Aging Summary.” The report is fine. The problem is QuickBooks marks an invoice “paid” only when payment comes through QuickBooks Payments. Bank transfers, ACH, and wires don’t auto-update. See why your QuickBooks shows paid invoices as unpaid.