Accounts receivable aging report template
Receivables ageing turns a stack of unpaid invoices into a clear picture of cash already earned but not yet collected. An accounts receivable aging report template gives small businesses and finance teams a repeatable way to spot late accounts, compare customer balances and focus credit control effort where it matters most.
A useful ageing schedule sorts each customer balance into current, 1–30, 31–60, 61–90 and 91+ days overdue. It identifies who owes money, how long each debt has remained outstanding and which balances are most likely to need intervention. A clean template also makes month-to-month movement easier to see.
For Australian businesses, the numbers should reconcile to the accounting ledger, carry an Australian dollar value and clearly separate GST from the amount payable. The format works for a sole trader, a bookkeeping team or a credit controller and can be maintained in Excel or another spreadsheet program.
What the report measures
The report converts a customer ledger into an operational view of collection risk. At its simplest, it totals each unpaid invoice, subtracts payments and valid credit notes, then groups the remaining balance by due-date status. This produces a reliable picture of accounts receivable rather than relying on a sales total that ignores whether cash has arrived.
Each row should represent one invoice, with its invoice number, customer ID, issue date, due date, gross amount, GST component, payments, adjustments and status. Ageing is calculated from the due date, not the invoice date, so a deposit or part-payment does not accidentally hide the original overdue period.
Customer summaries then reveal where value is concentrated. A business may have a modest total receivables balance but a high proportion overdue, or a large current balance that remains healthy. Totals by ageing band should therefore sit alongside customer-level balances, overdue percentages and any disputed amounts.
Building a practical workbook
Start with an invoice register containing invoice number, customer ID, issue date, due date, gross amount, GST amount, payments, credit notes and final status. Store one invoice per row and use consistent customer identifiers. This structure supports filters, pivot summaries and customer statements without creating duplicate balances.
Use formulas for ageing rather than typing days overdue. An overdue-days calculation can compare TODAY() with the due date, while the customer balance subtracts recorded payments and approved credit notes from the invoice amount. Lock source cells, label assumptions and test one known invoice after each change so an error does not spread through every ageing bucket.
Create summary views for the total balance, each overdue band and the largest customer exposures. Add conditional formatting so serious delays stand out, but retain the underlying values for filtering and reconciliation. Keep charts simple: total receivables, overdue receivables and the value in each ageing band answer most management questions without overcrowding the dashboard.
Spreadsheet design principles transfer across subject areas. For example, the season statistics template shows how consistent source rows, calculated fields and summary outputs can turn raw records into a practical review sheet.
A single workbook can support two views:
| Report element | Ledger-oriented view | Action-oriented view |
|---|---|---|
| Time basis | All open balances at the reporting date | Past-due balances ranked by urgency |
| Ageing | Current through 91+ days overdue | Priority band based on age, value and dispute status |
| Customer detail | Invoice numbers, due dates and credits | Owner, contact date, next action and commitment |
| Cash context | Invoices and payments received | Expected cash-flow effect of collection |
| Governance | Audit trail and source reconciliation | Escalation route and documented resolution |
Australian invoicing and GST context
Australian invoices should record the amount in Australian dollars and identify the GST treatment. For GST-registered entities, show the standard 10% GST separately on taxable supplies, while noting that some goods and services are exempt or input taxed. Include the ABN where relevant, together with the customer’s billing details, invoice date, due date and a clear payment method.
Credit control should align with the business’s reporting calendar. Most eligible small and medium-sized businesses lodge their Business Activity Statement quarterly with the ATO, although some entities report monthly. The standard Australian financial year ends on 30 June, so a debtors review at that date can support annual accounts, while a quarterly review supports BAS reconciliation and cash-flow planning.
Payment habits vary by customer and location. A Sydney, Melbourne or Brisbane corporate customer may settle through BPAY, electronic funds transfer or a purchasing portal. Regional trades and service businesses may invoice after travel, installation or completed work, creating uneven due dates. Capturing the payment method and a promised payment date helps distinguish a genuine delay from a processing error.
Prioritising collections
Prioritise balances using more than age alone. Combine days overdue, amount outstanding, customer history, disputed status and the likelihood of repeat business to create a simple priority field. A large current invoice may need light monitoring, while a smaller 61–90-day balance with a broken payment promise may deserve the first call.
Use a reminder cadence matched to the agreement: an initial courtesy notice near the due date, a clear overdue notice after it passes and an escalation for an unresolved account. State the invoice number, amount due, payment options and consequence of continued delay. Link each message to the ledger so sent dates, responses and receipt of payment remain visible.
Record every promise to pay as a dated task, not an informal note. When money arrives, match the payment to the oldest eligible invoice unless the customer specifies otherwise, then update the customer statement and aged balance. If a customer disputes part of an invoice, separate the disputed and undisputed amounts so staff can collect what is not in question.
Reconciliation, privacy and accountability
Reconcile the ageing output to the general ledger and bank feeds before circulating it. Totals should agree with open accounts receivable, and any difference should be documented rather than forced into an ageing band. Review customer credit notes, unapplied payments and invoices created after the reporting cut-off, then retain the dated source files used for the final report.
Treat the report as confidential commercial information. Give staff only the access needed for their role, protect exported customer lists and remove personal data when it is no longer required. Where the Privacy Act 1988 applies, follow relevant collection, storage and disclosure practices while also checking obligations in the state or territory where the business operates.
Clear ownership prevents a spreadsheet from becoming an orphaned task. Assign responsibility for preparing, checking and following up the report, with a second person reviewing material adjustments. Broader professional career resources can help teams define role expectations and training needs, while the report’s notes should capture the reviewer, decision and next due date.
Implementation and regular review
Roll out the template with a small pilot using live but controlled data. Begin with clean customer records, archived invoices and one agreed cut-off date. Remove duplicate customer IDs, confirm how partial payments are recorded and run a sample balance back to the accounting system. Keep the first version stable before adding formulas, charts or automated reminders.
Review the report on a fixed schedule, usually monthly and before key reporting dates. Track days sales outstanding, the percentage of receivables overdue, the value in each ageing band and the average time from due date to payment. Compare these measures with prior months and targets, but investigate unusual movements rather than treating a trend as a performance verdict.
Keep the workbook current by recording its version, reporting date, currency and preparer details. Revisit ageing bands when customers, payment terms or service models change, and preserve earlier reports for comparison. If the business moves beyond a spreadsheet, the same fields can map to accounting software, while the ownership and reconciliation controls should remain unchanged.
Download the free receivables ageing template from MyFundRazor, enter your current open invoices in Australian dollars and schedule the first review. Start with balances over 60 days overdue, assign an owner to each action and turn the completed report into a weekly collections routine.