How to Build an Accounts Receivable Aging Report in Excel (30, 60, 90 Days)
An aging report sorts unpaid invoices into buckets by how overdue they are, usually 0–30, 31–60, 61–90 and over 90 days. Two decisions decide whether yours is correct. The first is whether you age from the due date or the invoice date. The second is whether a partly paid invoice shows its full amount or its remaining balance.
Get those two wrong and every bucket total is wrong, which is worse than having no report at all.
This guide covers why the build breaks, the three approaches people use, and where each one stops working. It is a data workflow, not accounting advice, so confirm treatment with whoever owns your ledger.
Why an aging report breaks a spreadsheet
The first problem is the date question. Aging from the invoice date tells you how old the paperwork is. Aging from the due date tells you how late the customer is, and for collections that is the number you want.
Both are defensible and they produce different reports. The failure mode is a spreadsheet where nobody wrote down which one was used.
The second problem is partial payments. An invoice for $10,000 with $7,000 received is a $3,000 receivable, and it must appear as $3,000 in exactly one bucket. Aging reports built off an invoice list rather than an open-items list quietly overstate everything.
The third problem is that the report is a snapshot. Buckets are calculated against today, so yesterday's file is already stale, and every rebuild recalculates every row.
Then there are the awkward rows. Credit notes, prepayments, disputed invoices and multi-currency balances each need a rule. Each rule then has to survive the next person who opens the file.
None of these is difficult in isolation. They are difficult because they arrive together, once a month, under a deadline.
What this costs you
A collections list you cannot act on. The point of bucketing is to know who to call first. A report that overstates balances sends someone to chase money that already arrived.
Rework every single month. Because buckets are relative to today, the aging report is never finished. Every cycle repeats the same joins, the same formulas and the same manual checks.
Totals that do not tie to the ledger. When bucket totals do not sum to the receivables balance, the report loses authority. Finding the reason usually takes longer than the original build.
An aging report is trusted because its total matches the ledger. Nothing else about it matters if that fails.
The workarounds people try
Option 1: Fix the definitions before touching a formula
Write four things at the top of the sheet. Which date you age from, and what the bucket edges are. Whether amounts are gross or net of payments, and what the as-of date is.
This costs ten minutes and prevents the most common dispute. The Journal of Accountancy walks through the same build with the same emphasis on getting the setup right first.
It also decides your source. You want an open-items extract with remaining balances, not a list of every invoice ever raised.
The limit is that definitions do not compute anything. They just stop you computing the wrong thing.
Option 2: Build the bucket column, then pivot the totals
Calculate days overdue as the as-of date minus the due date, then map that number to a bucket label. TODAY gives you a live as-of date, and DATEDIF returns the day count between two dates.
For the label itself, IFS is more readable than nested IF statements six months later. Then total by customer and bucket with SUMIFS, which keeps the calculation auditable row by row.
Use a hard-coded as-of date rather than TODAY when the report gets circulated. A file that silently re-ages itself next week will contradict the version already in someone's inbox.
The ceiling is volume and edge cases. The formulas hold, but credit notes, part payments and disputes still get handled by hand.
Option 3: Keep a rules tab beside the numbers
Put the awkward decisions in one place. How credit notes net off, and whether disputed invoices are excluded or flagged. How foreign-currency balances are converted, and at which rate.
This is what makes the report survivable when someone else runs it. It is also the tab that gets skipped when the month-end deadline is tight.
The limitation is that a rules tab documents judgement without applying it. Somebody still has to implement each rule every cycle. Our guide to reconciling transactions in a spreadsheet covers the matching work that feeds this.
The shared ceiling. All three assume you start from a clean open-items extract. When the source is a raw invoice export plus a separate payments file, the real work is joining them before any bucketing begins.
How to build an aging report with Powerdrill Bloom
Step 1: Upload your invoice and payment data
Upload the open-items export, or the invoice and payment files together. Powerdrill Bloom profiles the columns on arrival, so missing due dates, blank amounts and duplicated invoice numbers surface before any bucket is calculated.
Step 2: Describe the bucketing rules in natural language
State the rules rather than building them. Say you are aging from the due date as of a specific date. Give the bucket edges, and say amounts should be net of payments received.
Then ask for the checks in the same pass. Ask which invoices have payments exceeding the invoiced amount, and which have due dates earlier than their invoice dates. Then ask whether the bucket totals tie to the receivables balance.
Step 3: Export the chart, report, or deck
Take out a per-customer aging table, a chart of the bucket distribution, or a collections list ordered by the oldest balance.
Why this beats rebuilding it every month
| Manual route | Powerdrill Bloom | |
|---|---|---|
| Joining invoices to payments | Lookup formulas per file | Upload both and ask |
| Changing the as-of date | Recalculate and re-verify | State the new date |
| Netting partial payments | Manual balance column | Ask for balances net of payments |
| Tying totals to the ledger | Manual check each cycle | Ask whether the totals tie |
The middle rows are where the month goes. Bucketing is arithmetic; getting to a clean open-items list is the actual job.
Common mistakes
Aging from the invoice date when you meant the due date. For collections, due date is almost always right. Whichever you choose, write it on the report.
Showing invoice amounts instead of remaining balances. A partly paid invoice belongs in a bucket at its unpaid balance. Full amounts inflate every total.
Letting TODAY re-age a circulated file. Freeze the as-of date before you send the report, or two people will read different numbers from the same file.
Ignoring credit notes. An unapplied credit sits against a customer and reduces what they owe. Leaving it out makes the balance look worse than it is.
Bucketing by customer instead of by invoice. Buckets are per invoice, then summed per customer. Averaging a customer's ages hides the oldest item, which is the one you need.
Never checking against the ledger. Bucket totals must sum to the receivables control balance. Skip that check and the report is decoration.
Rebuilding from scratch each cycle. The rules do not change monthly, only the data does. Keep the rules and swap the export, the same discipline as a budget versus actual report.
Conclusion
Decide the aging date, use remaining balances, freeze the as-of date, and tie the totals to the ledger. Those four make the difference between a report people act on and a table people argue with.
What makes it expensive is that the whole thing is relative to today, so it is never done. The joins and the checks come back every cycle.
If that is where your month-end goes, try Powerdrill Bloom on your invoice and payment exports. See also our guide to turning PDF financial statements into charts and the AI cash flow analysis page.
Frequently asked questions
What are the standard buckets in an accounts receivable aging report?
Most reports use 0–30, 31–60, 61–90 and over 90 days, often with a current or not-yet-due column. The edges are a convention rather than a rule, so state which ones you used.
Should I age invoices from the invoice date or the due date?
Use the due date if you want to know how late a customer is, which is the usual goal for collections. Use the invoice date if you want to know how old the paperwork is.
How do I handle partial payments?
Show the remaining balance, not the original invoice amount, and place that balance in one bucket. Working from an open-items extract rather than an invoice list handles this automatically.
Which Excel functions do I need?
TODAY or a fixed date for the as-of date, and DATEDIF for days overdue. IFS assigns the bucket label, and SUMIFS totals by customer and bucket. None of them is complicated; the definitions are the hard part.
How often should the report be rebuilt?
At least monthly, and weekly if collections are active, since every bucket is relative to the as-of date. Freeze that date on each version you circulate.