How to Build an Accounts Receivable Aging Report in Excel or Google Sheets
Short on time? The Freelancer Finance Tracker ($15) is the finished, tested version of what this tutorial builds.
An AR aging report shows who owes you money and how long it has been overdue. Accountants look at it first because the older an invoice gets, the less likely it is to be paid. Here's how to build one with three formulas.
The invoice table
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Client | Amount | Due date | Balance | Days overdue | Bucket |
| 2 | INV-1005 | Bright Path | 1800 | 2026-06-25 | 800 | formula | formula |
Put an as-of date in J1 (=TODAY(), or any date you want to report on). Balance is amount minus payments received.
1. Days overdue
F2: =IF(E2<=0, 0, MAX(0, $J$1 - D2))
Paid invoices get 0. Unpaid invoices get the number of days past the due date, and 0 if they're not due yet.
2. Aging bucket
G2: =IF(E2<=0, "Paid", IF(F2=0, "Current", IF(F2<=30, "1-30",
IF(F2<=60, "31-60", IF(F2<=90, "61-90", "90+")))))
3. Summary by client
Build a small table with clients down column L (L2, L3, …) and bucket names across row 1 (M1:Q1 = Current, 1-30, 31-60, 61-90, 90+). Then:
M2: =SUMIFS($E$2:$E$500, $B$2:$B$500, $L2, $G$2:$G$500, M$1)
Copy across and down. Add a total column =SUM(M2:Q2). An "overdue" column =SUM(N2:Q2) skips Current.
Reading the report
- Current is healthy.
- 1–30 calls for a friendly reminder.
- 31–60 calls for a firm reminder and a phone call.
- 61–90 calls for a final notice. Consider pausing work for that client.
- 90+ means you need a decision: payment plan, collections, or write it off with your accountant.
Common mistakes
- Aging from the invoice date instead of the due date. A 30-day-terms invoice isn't late on day 10.
- Forgetting partial payments. Always age the balance, not the original amount.
Automate the reminders
Two ways to skip the manual work:
- The Freelancer Finance Tracker builds this report automatically from your invoices and payments, including partial payments.
- The Invoice Chaser for n8n reads the sheet every weekday and emails polite 3, 10 and 21-day reminders for you.
Free tool: Bank CSV Categorizer for Schedule E and Schedule C
Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.