The AR aging report is the snapshot of how much money you’re owed, sliced by how late each invoice is. The four standard buckets — 0-30, 31-60, 61-90, 90+ days past due — correspond to different statistical recovery rates and trigger different collection actions.
This template builds the aging report automatically: you fill in the invoice list, the formulas compute days overdue and assign each invoice to a bucket. Conditional formatting flags 31-60 yellow, 61-90 light red, 90+ dark red. Summary section at the bottom totals each bucket via SUMIFS and counts invoices via COUNTIFS.
How to use
- Download the template (link below). Open in Excel, Google Sheets, or LibreOffice Calc.
- Cell K1 holds today’s date — change it to back-date the report if needed.
- Fill rows 9+ with your open invoices. Don’t overwrite columns F (Days overdue) or G (Bucket) — those are formulas.
- The bucket totals at the bottom update automatically.
- To see per-client totals, sort the table by Customer or add a SUMIF formula in a side column.
When this template stops working
At low invoice volume (10-30/month), the template is honest. Past 50 open invoices, three failure modes hit:
- Amount collisions — two clients each owe $1,500, three $1,500 deposits this month, which deposit matches which invoice? You check the wrong box, send a dunning email to a client who already paid, lose the relationship.
- Partial payments — client pays $1,800 against $2,000 invoice, “Paid?” is binary. You either flag paid (missing $200) or unpaid (have $1,800 in bank). Either way your AR is wrong.
- VAT-net payments — Polish/German/Dutch B2B clients sometimes wire the net amount. Invoice says €1,230 (€1,000 net + 23% VAT), wire is €1,000. Template doesn’t recognize ±23% as a valid match.
At that point a deterministic reconciliation tool earns its keep. Drop bank CSV + invoice CSV, get the four-bucket result (Paid / Likely paid / Unpaid / Anomalies) with explicit match reasons. Try checkunpaidinvoices.com free with sample data first.
Why is the AR aging file separate from my invoicing tool?
QuickBooks, Xero, FreshBooks all build aging reports built in. But they only mark invoices “paid” when payment comes through their integrated payment processor. Bank transfers, ACH, wires — anything outside the platform — stays “unpaid” forever. The aging report your tool produces is wrong in proportion to how often your clients pay by bank transfer.
This template is a workaround if you’re managing payments manually and want a quick aging view that reflects reality.
FAQ
What’s the difference between AR aging and DSO?
AR aging is the snapshot — how much you’re owed right now, by bucket. DSO is the trend — average days to collect across a period. AR aging tells you which clients to chase today; DSO tells you whether collections are improving over time.
Why count days from due date, not invoice date?
Counting from invoice date conflates “client has had time to pay” with “client is late.” Counting from due date isolates the lateness signal. Always use due date.
Can I send the aging report to my clients?
No. The aging report is internal — it shows your private cash position and other clients. To a single client, send a remittance advice or statement of account showing only their open invoices.
Does this work in Google Sheets?
Yes. Open the .xlsx in Google Sheets via File → Import → Replace spreadsheet. All formulas translate. Conditional formatting may need re-applying on Sheets — the rules use $G references which work identically.