An invoice aging report turns an open-invoice list into a practical collections plan. This guide explains how to build an accounts receivable aging template, assign invoices to current and overdue buckets, identify concentration and dispute risks, and run a repeatable weekly follow-up process.
Overview
An invoice aging report groups unpaid invoices by how long they have been outstanding. Instead of reviewing every invoice in date order, you can see which balances are current, which require routine reminders, and which need immediate attention.
A useful report is more than a list of overdue customers. It should help answer five operational questions:
- How much is unpaid in total?
- How much is current, 1–30 days overdue, 31–60 days overdue, 61–90 days overdue, or more than 90 days overdue?
- Which customers or accounts represent the largest share of outstanding receivables?
- Which invoices are delayed by a genuine dispute, missing information, or an unanswered payment request?
- What action, owner, and next date are assigned to each material balance?
The most useful starting point is an invoice aging spreadsheet or accounting-software report with consistent fields. Use one row per invoice rather than one row per customer. This keeps partial payments, credit notes, multiple due dates, and separate disputes visible.
For reliable aging, calculate days outstanding from the invoice due date unless your internal process specifically requires invoice-date aging. Due-date aging better distinguishes invoices that are unpaid but not yet late from balances that have missed their agreed payment terms. Document the method at the top of the report so everyone interprets the buckets consistently.
What to track
Build the accounts receivable aging template with enough detail to support action without turning it into a second accounting system. These columns cover the core calculation, collection context, and ownership:
| Field | Purpose |
|---|---|
| Customer or account | Groups invoices for follow-up and concentration review. |
| Invoice number | Provides the reference needed for reminders, reconciliation, and customer queries. |
| Invoice date | Shows when the charge was issued. |
| Due date | Provides the basis for overdue calculations. |
| Original invoice amount | Records the face value before payments or credits. |
| Payments and credits | Prevents the report from overstating the amount still owed. |
| Open balance | Shows the amount currently outstanding. |
| Current | Captures invoices not yet past their due date. |
| 1–30 days | Captures recently overdue invoices that often need a prompt reminder. |
| 31–60 days | Highlights balances requiring more deliberate follow-up. |
| 61–90 days | Flags aging balances that may need escalation or a payment plan discussion. |
| 90-plus days | Separates the oldest balances for focused review and risk assessment. |
| Dispute status | Distinguishes a documented dispute from a simple lack of response. |
| Promised payment date | Records a specific commitment and allows missed promises to be followed up. |
| Next action and owner | Prevents responsibility from remaining ambiguous. |
| Risk notes | Captures relevant context such as repeated late payment, a pending approval, or a credit concern. |
Add a report date at the top and preserve the original export separately. If you adjust a balance manually, record why and retain supporting evidence. The report should reconcile to your accounting records, not become an unexplained alternative ledger. An invoice reconciliation template can help match payments, credits, and outstanding balances before the aging review.
Useful spreadsheet formulas
Assume the report date is in cell B1, the due date is in column D, and the open balance is in column G. A basic days-overdue formula is:
=MAX(0,$B$1-D2)
This returns zero for an invoice that is not yet overdue. To calculate a current balance, use a separate test for the due date:
=IF(D2>$B$1,G2,0)
For an invoice 1–30 days overdue:
=IF(AND($B$1>=D2,$B$1-D2<=30),G2,0)
Repeat the logic for 31–60, 61–90, and 90-plus days. If your spreadsheet uses a different date convention, test the boundaries with sample dates before relying on the report. The bucket totals should add up to the total open balance:
=SUM(CurrentTotal,Days1to30Total,Days31to60Total,Days61to90Total,Days90PlusTotal)
A simpler design uses one “days overdue” column and a pivot table or summary formulas to produce the buckets. That approach reduces the risk of accidentally placing one invoice in more than one aging column.
Customer concentration and context
Include a customer-level summary below or beside the invoice detail. Show each customer’s total open balance, oldest invoice, amount over 60 days, amount over 90 days, dispute status, and share of total receivables. A concentration percentage can be calculated as:
=CustomerOpenBalance/TotalOpenBalance
This does not predict whether a customer will pay. It simply shows where a delay could have the greatest effect on cash planning. Review large balances alongside contract terms, recent communications, delivery evidence, purchase orders, and any approved payment arrangement.
Cadence and checkpoints
Use the report on a weekly rhythm for active collections, then perform a fuller review at month-end and quarter-end. The exact day matters less than using the same sequence consistently.
Weekly follow-up workflow
- Refresh the source data. Export open invoices, payments, credit notes, and adjustments from the accounting system. Check that the report date, currency, and customer identifiers are consistent.
- Reconcile before contacting customers. Remove settled invoices, apply known payments, and investigate balances that do not agree with the ledger. Avoid sending a reminder for an amount already paid or credited.
- Sort by action priority. Consider age, open balance, customer concentration, dispute status, promised payment date, and relationship or operational context. Age alone should not determine priority.
- Separate disputes from silence. A documented pricing, delivery, approval, or quantity dispute needs an owner and resolution path. An unanswered invoice needs a communication sequence and a next contact date.
- Assign every follow-up. Record the person responsible, action taken, contact date, response, promised payment date, and next step. “Follow up” without an owner is not a workflow.
- Escalate missed commitments. Move a missed promised payment date back into the active queue. Review whether a manager, account owner, or formal notice is appropriate under the agreed terms and internal policy.
Use a short status vocabulary such as uncontacted, reminder sent, awaiting customer response, disputed, payment promised, payment received, and escalation review. Consistent labels make filtering and handoffs easier.
At month-end, compare the current report with the previous report. Confirm that totals reconcile to the accounting system, review movements between buckets, and list balances requiring management attention. At quarter-end, examine recurring late-payment patterns, customer concentration, credit terms, and whether invoice delivery or approval processes are contributing to delays.
Before an invoice enters collections, verify that it was accurate and complete. The invoice checklist template can help reduce avoidable delays caused by missing purchase order details, incorrect tax information, unclear service descriptions, or absent payment instructions.
How to interpret changes
Read the report as a movement pattern rather than a single alarming total. A growing total may reflect higher sales, a recent billing cycle, or a genuine collection problem. Compare balances by bucket and customer before deciding what changed.
- Current balances rise while overdue balances remain stable: this may reflect normal billing growth. Confirm that cash receipts are following the expected invoice cycle.
- The 1–30-day bucket rises: check whether invoices were sent late, customers need reminders, or an approval process is slowing payment.
- The 31–60-day bucket rises: review unanswered reminders, missing documentation, and accounts that made informal promises without dates.
- The 61–90 and 90-plus buckets rise: prioritize resolution, escalation, and documented risk review. Check whether the balances are disputed, partially paid, or affected by credits not yet posted.
- One customer represents a large share of overdue balances: coordinate the account owner and collections owner. The report should support a specific plan, not just a larger number of reminders.
- Disputed balances dominate the oldest buckets: treat dispute resolution as an operational task. Identify the missing evidence, decision-maker, and target resolution date.
Track useful internal measures such as total open balance, overdue balance, balance over 60 days, balance over 90 days, number of overdue invoices, number of active disputes, and amount with a promised payment date. Keep definitions stable from one review to the next. If you change from due-date aging to invoice-date aging, label the change and avoid comparing the figures as if they used the same method.
For accounting-software exports, map the source fields once and save the report layout if the system allows it. Confirm how the system treats unapplied cash, credit notes, voided invoices, foreign currencies, partial payments, and disputed amounts. These settings can affect the report, so document the assumptions beside the template.
When to revisit
Refresh the invoice aging report at least weekly when the business has regular customer billing or meaningful outstanding receivables. Use it at every month-end close and cash-flow meeting, and perform a deeper review at quarter-end. A smaller operation with few invoices may use a less frequent schedule, provided overdue balances are not allowed to sit without an owner.
Revisit the template whenever your invoice numbering system, accounting software, currencies, payment terms, customer structure, or reporting needs change. Update the field mapping after adding a new payment method, changing how credits are applied, or introducing a formal dispute process. Recheck spreadsheet formulas when columns move or when the report date changes from a manually entered date to an automated date.
For the next review, take these practical steps:
- Choose a fixed report date and document whether aging is based on due date or invoice date.
- Export open invoices and reconcile payments, credits, and adjustments.
- Assign each balance to current, 1–30, 31–60, 61–90, or 90-plus-day buckets.
- Add dispute status, promised payment date, risk notes, owner, and next action.
- Sort by age, amount, concentration, and action status rather than age alone.
- Send appropriate reminders, resolve genuine disputes, and record every commitment.
- Compare the report with the prior period and note what moved between buckets.
Keep collection messages aligned with the underlying invoice record. For a structured reminder sequence, see the late payment reminder templates. If recurring services create repeated monthly balances, review whether a clear retainer invoice process would make billing and follow-up easier to track.