Accounts Receivable Aging Report: Free Excel Template
An accounts receivable aging report should tell you more than how much sits in 1-30, 31-60, 61-90, and 91+ day buckets. It should show which customer creates the exposure, whether a promise was broken, whether the invoice is disputed, who owns the collection, and what happens next.
This Excel workbook combines the aging report with a collection queue. It also reconciles invoice detail to the general-ledger receivable balance. If those totals do not match, the report stops at FIX INPUTS. A polished collections meeting built on an unreconciled number is worse than no meeting because it gives the team false confidence.
What is inside the accounts receivable aging template?
The workbook has Setup, Invoices, Summary, Customer Aging, Checks, and Sources. Invoices is the operating queue. Summary is the management view. Checks decides whether the report is safe to use.
| Sheet | What it controls |
|---|---|
| Setup | Report date, ledger balance, exposure and concentration thresholds |
| Invoices | Invoice age, balance, promise, contact, owner, dispute, priority, queue flag |
| Summary | Aging totals, overdue percentage, concentration, broken promises, disputes |
| Customer Aging | Exposure by customer and age bucket |
| Checks | Reconciliation, duplicates, missing dates, owners, and actions |
Get the Excel workbook with reconciled invoice aging, customer concentration, disputes, broken promises, owners, and a collection queue. Enter your details and I’ll email the secure download link. The newsletter checkbox is optional and can be cleared if you only want the workbook.
The sample ledger balance does not reconcile on purpose. Change the general-ledger balance to the invoice-detail total, or replace all sample data, and watch Checks move only when the report is complete.
How does an accounts receivable aging report work?
The report starts with one row per open invoice. Outstanding balance equals original amount minus payments applied through the report date. Days overdue equals the report date minus the due date, but never falls below zero.
| Bucket | Meaning | Typical first question |
|---|---|---|
| Current | Not yet past due | Is the invoice accepted and scheduled? |
| 1-30 | Up to 30 days late | Was the invoice received, approved, or disputed? |
| 31-60 | 31 to 60 days late | Who has promised payment and on what date? |
| 61-90 | 61 to 90 days late | Should credit or new work be restricted? |
| 91+ | More than 90 days late | What escalation, reserve, or legal review is needed? |
QuickBooks accounts receivable aging guidance distinguishes an AR Aging Summary from an AR Aging Detail report. The summary answers how much each customer owes by age. The detail shows the invoices behind it. This workbook keeps both views tied to the same invoice rows.
Use due date, not invoice date, for collection aging. A 60-day-old invoice on agreed 90-day terms is not overdue. A 20-day-old invoice due on receipt may already need attention.
Reconcile the aging report before prioritizing collections
Reconcile the accounts receivable aging report to the general-ledger receivable balance for the same report date and accounting basis. Checks compares that balance with the open-invoice total. The difference must be zero before the report gets a PASS.
- Confirm payments and credit notes are applied through the report date.
- Find duplicate invoice numbers.
- Add missing due dates.
- Remove written-off or closed items from open receivables according to your accounting process.
- Confirm the ledger and invoice detail use the same entity, currency, and cutoff.
A reconciliation difference often comes from timing, unapplied cash, credit notes, a different report cutoff, or invoices posted to another entity. Do not hide it inside a generic ‘other’ row. Find the source.
Prioritize the collection queue by exposure and context
Age matters, but age alone is a weak priority rule. A $500 invoice at 91 days and a $50,000 invoice at 31 days should not automatically receive the same sequence. The workbook adds balance, customer concentration, dispute status, and broken promises to the priority score.
| Signal | Why it raises priority |
|---|---|
| Large outstanding balance | The cash impact is material |
| High customer concentration | One customer controls too much of total AR |
| Broken promise | A dated commitment has already failed |
| Dispute | Ordinary reminders will not solve a delivery or PO issue |
| Next action due | The collection plan is ready to execute |
| Missing owner | No one is accountable |
Filter Queue Flag to ACTION, then sort Priority Score from high to low. That gives the weekly meeting a starting point. Judgment still decides the call, credit hold, dispute path, or relationship conversation.
Do not treat every dispute as customer avoidance. A wrong purchase order, missing delivery evidence, bad invoice address, or unclear milestone can be your process failure. Separate those cases so collections does not keep sending reminders for a problem operations must fix.
Run the weekly AR meeting in 20 minutes
A short meeting works when the accounts receivable aging report is updated before the call. Do not spend the meeting reconstructing contact history. Use it to decide what changes next.
- Reconcile first. If Checks is not PASS, assign the reconciliation owner and stop.
- Review the five largest overdue exposures. Confirm balance, age, concentration, and dispute status.
- Review broken promises. Decide escalation or a revised dated commitment.
- Review disputes separately. Name the internal resolver and evidence needed.
- Confirm next actions. Every ACTION row needs an owner and date.
- Set the next review. Record it on Summary before closing the workbook.
For the wider cash impact, feed realistic collection dates into the cash flow forecast template and use the published cash-flow killers guide when overdue invoices are only one part of the shortfall.
Use customer concentration beside the aging buckets
Aging tells you lateness. Concentration tells you dependency. The Customer Aging sheet calculates each customer’s share of total receivables, so a current invoice can still deserve management attention when one customer controls 35% of the book.
Set a concentration threshold that fits the business. A young agency with three clients will naturally look concentrated. That does not make the metric useless. It tells you that a collection delay, scope dispute, or lost account can hit both cash and revenue at once.
Old receivables create collection risk. Concentrated receivables create business risk.
Where the AR aging workbook stops
The accounts receivable aging report is a management and collections layer. It does not replace the receivables ledger, invoice system, legal process, bad-debt policy, or country-specific tax treatment.
- Do not write off debt from this workbook without the accounting and approval process.
- Do not threaten legal action from a spreadsheet rule.
- Do not extend or remove customer credit based on age alone.
- Do not share customer financial data without appropriate access controls.
The U.S. Small Business Administration finance guidance names accounts receivable, accounts payable, available cash, bank reconciliation, and payroll as core finance functions someone must manage. The workbook improves the AR review. It does not merge those functions into one file.
Frequently asked questions
What is an accounts receivable aging report?
It is a report of open customer invoices grouped by how long they are past due. A useful report also reconciles to the ledger and shows the customer, balance, dispute, owner, promised date, and next action.
What are the standard AR aging buckets?
Common buckets are Current, 1-30 days, 31-60 days, 61-90 days, and 91+ days. Use the invoice due date and a consistent report date.
Should AR aging use invoice date or due date?
Use due date for collection aging. Invoice date still matters for audit and context, but agreed payment terms decide when a balance becomes overdue.
How often should a small business review accounts receivable aging?
Review it weekly when overdue balances are material or cash is tight. Reconcile it at least monthly with the general ledger on the same date and basis.
Can the AR aging report replace accounting software?
No. It is a collection and management workbook. It does not issue invoices, post journal entries, reconcile the bank, or decide write-off and tax treatment.
What to do next
Replace the sample invoices, set the report date, and enter the matching general-ledger balance. Do not start the collection meeting until Checks says PASS.
Then filter Queue Flag to ACTION and choose the largest balance that can move this week. Name the owner, promise, and next date. An aging bucket describes the problem. A dated action collects the cash.