How to Prepare an Accounts Receivable Aging Report

An accounts receivable aging report can help you identify overdue invoices and collection priorities—but only if the underlying information is accurate.

Incorrect due dates, unapplied payments and duplicate invoices can make the report look worse than the actual situation. Missing invoices can make it look better.

This step-by-step process will help you prepare a more reliable aging report.

Step 1: Choose the reporting date

Select the date on which you want to measure receivables.

Examples include:

• Last day of the month
• Last day of the quarter
• Current date
• Weekly collection-review date

Every invoice should be aged using the same reporting date.

If the report is prepared as of September 30, do not use October payments or later account activity unless you clearly label them as subsequent events.

Step 2: Gather all open invoices

Create a list of invoices with an unpaid balance as of the reporting date.

Include:

• Customer name
• Invoice number
• Invoice date
• Payment terms
• Due date
• Original amount
• Payments received
• Credits applied
• Remaining balance

Do not assume the original invoice amount is still outstanding.

Use:

Outstanding Balance = Original Invoice Amount − Payments − Credits

Step 3: Verify the due date

The due date determines whether the invoice is current or overdue.

For example:

• Invoice issued August 1
• Payment terms: Net 30
• Due date: August 31
• Reporting date: September 15

The invoice is 15 days overdue.

If the agreement requires payment upon receipt, the calculation will differ.

Use the contractual terms applicable to that invoice. Do not overwrite agreed terms merely because the company later changed its standard policy.

Step 4: Calculate days overdue

Use:

Days Overdue = Reporting Date − Due Date

If the result is zero or negative, the invoice is current.

If the result is positive, assign it to the appropriate overdue bucket.

Step 5: Assign each balance to an aging bucket

A common structure is:

Days overdue	Aging category
Not yet due	Current
1–30	1–30 days
31–60	31–60 days
61–90	61–90 days
91 or more	90+ days

Keep your bucket definitions consistent from one reporting period to another.

Read Understanding Accounts Receivable Aging Buckets for a detailed explanation of each category.

Step 6: Account for partial payments

If a customer paid part of an invoice, only the remaining balance belongs in the aging report.

For example:

• Original invoice: $10,000
• Payment received: $4,000
• Remaining balance: $6,000

Age the remaining $6,000 using the original due date unless an approved agreement changes how the balance should be handled.

Step 7: Apply credits correctly

Unapplied credits can overstate accounts receivable.

Review:

• Credit memos
• Customer deposits
• Returns
• Pricing adjustments
• Duplicate payments
• Write-offs
• Unapplied cash

Do not remove or apply credits without appropriate accounting support and authorization.

Step 8: Separate disputed invoices

A disputed invoice is still important, but it should not remain buried in a routine reminder process.

Add fields for:

• Dispute status
• Amount disputed
• Reason
• Date raised
• Internal owner
• Expected resolution date
• Undisputed amount
• Next action

A disputed invoice requires resolution—not repeated generic reminders.

Step 9: Record collection information

Add operational fields such as:

• Last contact date
• Last response
• Promise-to-pay date
• Assigned employee
• Next action
• Next follow-up date
• Escalation status

This transforms the aging report from a passive accounting document into a usable collection-management tool.

Step 10: Reconcile the report

The total aging report should reconcile with the accounts receivable balance in the general ledger.

If the numbers do not match, investigate:

• Missing invoices
• Duplicate entries
• Unapplied payments
• Timing differences
• Incorrect credits
• Write-offs
• Posting errors

Do not use an unreconciled report to make major collection or credit decisions.

Step 11: Analyze the totals

Calculate:

• Total accounts receivable
• Total overdue
• Percentage overdue
• Percentage over 60 days
• Percentage over 90 days
• Largest customer balances
• Largest overdue invoices
• Total disputed amount

Use the free Accounts Receivable Aging Calculator to analyze balances across the standard aging categories.

Step 12: Assign priorities

The oldest invoice is not automatically the highest priority.

Consider:

• Amount
• Age
• Last contact
• Payment history
• Broken promises
• Dispute status
• Customer concentration
• Likelihood of payment
• Cost of delay

Read How to Prioritize Overdue Invoices before assigning collection tasks.

Spreadsheet formula example

If the due date is in cell C2 and the reporting date is in cell H1, a basic spreadsheet formula for days overdue may look like:

=MAX(0,$H$1-C2)

The exact formula may differ by spreadsheet program, date format and report structure. Test it before applying it to the complete file.

How often should the report be updated?

Frequency depends on invoice volume and collection risk.

A small business might review aging:

• Weekly for active collections
• Monthly for financial reporting
• More frequently during cash-flow pressure
• Immediately before extending additional credit

An outdated aging report can create false priorities.

Move beyond the report

Preparing the report is only the beginning.

PaymentPilot helps small businesses organize invoices, identify accounts requiring attention and manage follow-up activity with AI assistance and human control.

Use the report to find the problem, then assign every significant overdue balance a documented next step.

Review other business risks

Use Island Insurance Group’s free Small Business Assessment Tool to review additional operational and insurance concerns.

You can also schedule a conversation with Samuel Bennett.

Samuel Bennett, Licensed Insurance Agent
sam@islandinsurancegroup.com
954-804-8144

This article provides general information and is not accounting, legal or financial advice.