Accounts Receivable Aging Spreadsheet Template: Prioritize Overdue Invoices and Improve Cash Collection

Overview

An accounts receivable aging spreadsheet is an invoice tracking spreadsheet organized by how long each unpaid balance has been outstanding. Instead of reviewing a list of invoices in date order, you group open amounts into practical buckets such as Current, 1–30 days overdue, 31–60 days overdue, 61–90 days overdue, and Over 90 days overdue.

The purpose is not simply to produce an aging report. It is to turn accounts receivable data into a repeatable collection workflow. A good sheet helps you answer four operational questions:

• Which customers have unpaid balances?
• How late is each invoice?
• Which accounts represent the greatest cash or collection risk?
• What follow-up action is due next, and who owns it?

For a small business, a well-designed workbook can be sufficient for routine monitoring when invoice volume is manageable and payment records are kept current. Use separate tabs for Invoice Data, Aging Summary, and Collection Log. Keeping source records separate from summary formulas makes the workbook easier to audit and reduces the risk of accidentally overwriting calculations.

What to track

Start with one row per invoice. Avoid combining several invoices into a single customer row because that hides different due dates, payment histories, and follow-up requirements. Recommended columns for an accounts receivable template in Google Sheets or Excel include:

• Customer: The legal or trading name used on the invoice.
• Customer contact: The person or address used for payment follow-up.
• Invoice number: A unique reference for reconciliation.
• Invoice date: The date the invoice was issued.
• Due date: The contractual payment deadline.
• Invoice amount: The original billed amount.
• Payments received: Amounts posted against the invoice.
• Credit or adjustment: Approved credit notes, discounts, or corrections.
• Outstanding balance: The amount still to be collected.
• Payment status: Open, partially paid, paid, disputed, or on hold.
• Last contact date: The most recent collection communication.
• Next action date: The date for the next reminder or escalation.
• Owner: The person responsible for the follow-up.
• Notes: Promised payment dates, disputes, or documentation requests.

Calculate the outstanding balance rather than typing it manually. If invoice amount is in column F, payments in column G, and credits in column H, a basic balance formula is:

=MAX(0,F2-G2-H2)

The MAX function prevents a negative balance when payments or credits exceed the invoice amount. You can retain a separate exception column to flag overpayments for review rather than allowing them to disappear from view.

For aging, use the due date as the primary reference. If the balance is zero or the invoice is marked paid, the row should not be included in open receivables. If today’s date is in cell $B$1, an overdue-days formula can be written as:

=IF(OR(I2<=0,J2="Paid"),0,MAX(0,$B$1-E2))

In this example, column I contains the outstanding balance, column J contains payment status, and column E contains the due date. If you do not use a separate reporting date cell, replace $B$1 with TODAY(). A fixed reporting date is often better for month-end reviews because it preserves the exact basis of the report.

Convert overdue days into an aging bucket with a nested formula such as:

=IF(I2<=0,"Paid",IF(K2=0,"Current",IF(K2<=30,"1-30",IF(K2<=60,"31-60",IF(K2<=90,"61-90","Over 90")))))

Here, column K contains overdue days. Adjust the references to match your workbook. In Excel, format the source range as a table so formulas and filters extend to new invoices. In Google Sheets, use a filter view or a named range to make recurring reviews easier.

Build the aging summary

The summary tab should show both invoice counts and outstanding balances. A comparison table might look like this:

Use SUMIFS to total balances by bucket. If column I contains outstanding balances and column L contains the aging bucket, the total for the 1–30 bucket is:

=SUMIFS($I:$I,$L:$L,"1-30")

To count open invoices in the same bucket, use:

=COUNTIFS($L:$L,"1-30",$I:$I,">0")

A customer-level summary is also valuable. Use a pivot table to group outstanding balances by customer and aging bucket. This distinguishes a customer with five small, recently overdue invoices from one customer with a single large balance over 90 days old. Add conditional formatting to highlight large balances, missed follow-up dates, and invoices with no recorded contact.

You can connect the summary to a broader cash flow forecast spreadsheet. Expected payment dates should be treated as planning assumptions, not guaranteed receipts. Separating confirmed payments from promised payments keeps the forecast more realistic.

Cadence and checkpoints

Refresh invoice records whenever payments are posted and perform a formal aging review at least weekly when cash collection is active. A monthly review is a useful minimum for businesses with lower invoice volume. The important point is consistency: an aging spreadsheet loses value when payment updates and collection notes are delayed.

A practical weekly checkpoint includes:

1. Import or enter new invoices.
2. Post payments, credits, and approved adjustments.
3. Confirm that paid invoices are excluded from open balances.
4. Review invoices that crossed into a new aging bucket.
5. Assign next actions and owners for overdue accounts.
6. Record promised payment dates and check whether earlier promises were kept.
7. Compare the current summary with the previous review.

At month-end, save a dated copy or snapshot of the summary. This creates a simple history of receivables without changing the live workbook. If the sheet is used by several people, protect formula columns and define who may change customer, payment, and status fields.

How to interpret changes

Do not judge collection performance from total receivables alone. A higher balance may result from increased sales, seasonal billing, or delayed payments. Review the direction and composition of the balances instead.

• Current balances rising while overdue balances remain stable: This may reflect healthy billing growth, but confirm that invoices are being sent correctly.
• 1–30 day balances rising: Check for invoice delivery problems, approval delays, or a change in customer payment behavior.
• 31–60 and 61–90 balances rising: Prioritize direct contact and investigate whether disputes or missing documents are blocking payment.
• Over-90 balances concentrated in a few customers: Review exposure by account rather than applying the same collection message to everyone.
• Payments recorded but balances unchanged: Check for duplicate invoice numbers, incorrect customer mapping, or formulas that exclude partial payments.

Days sales outstanding, or DSO, can provide a broader view of collection timing. A commonly used management formula is:

DSO = Average Accounts Receivable ÷ Credit Sales × Number of Days

Use a consistent period and sales definition when comparing DSO over time. Treat it as a directional indicator rather than a replacement for invoice-level review. Pair it with bucket totals, overdue invoice counts, and the percentage of balances past due.

For decision-making, connect the results to a KPI dashboard spreadsheet or a cash planning model. Useful dashboard metrics include total open receivables, overdue percentage, balance over 60 days, largest overdue customer, invoices without a next action, and promised payments due in the next review period.

When to revisit

Revisit the workbook every week or whenever payments are posted. Conduct a more deliberate review monthly, comparing the latest aging summary with prior snapshots. Update the structure when your billing process changes, such as new payment terms, currencies, business units, credit policies, or dispute categories.

Use this action checklist for the next review:

1. Set a reporting date in the summary tab.
2. Verify that every open invoice has a due date, balance, status, and customer owner.
3. Recalculate overdue days and confirm the aging buckets.
4. Sort by outstanding balance and then by overdue days.
5. Assign a specific next action and date to every overdue invoice.
6. Investigate balances that moved into a later bucket since the previous review.
7. Update the cash flow forecast with realistic expected receipt dates.
8. Archive the month-end summary before making the next period’s updates.

An aging spreadsheet works best as a living operating tool, not a report prepared only after cash becomes tight. If you also need to model sales assumptions, compare collection scenarios, or connect receivables to wider planning decisions, review a scenario analysis spreadsheet template. The combination of accurate invoice records, regular checkpoints, and clear ownership gives your team a practical basis for improving collection follow-through.