How to Track Accounts Receivable Aging (Free Template & Formulas)

Managing unpaid invoices effectively is the difference between a thriving business and a cash flow crisis. To stay on top of outstanding payments, businesses use Accounts Receivable (AR) Aging — a system that categorizes invoices based on how far past due they are.

By implementing a strict AR aging system, you can prioritize collections and identify at-risk accounts before they turn into bad debt. Let’s break down the 5-tier aging bracket, the exact spreadsheet formulas you need, and a 3-stage collection protocol.

The 5-Tier Aging Bracket Guide

Accounts receivable aging schedules divide outstanding invoices into five standard time buckets based on the invoice due date:

Current (Not Yet Due): The invoice has been issued, but the payment terms (e.g., Net 30) haven’t expired yet.
1–30 Days Past Due: The invoice is officially late. This is the highest priority bucket for gentle follow-ups.
31–60 Days Past Due: Payment is significantly delayed. Follow-ups should become more assertive.
61–90 Days Past Due: This is a danger zone. Direct phone calls and a hold on future services are often necessary.
90+ Days Past Due: These accounts carry a high risk of default. These may require collection agencies or write-offs.

The Spreadsheet Formulas: `=IFS()` and `=SUMIFS()`

You can automate this categorization in Excel or Google Sheets.

Categorizing the Invoice with `=IFS()`

Assuming your Invoice Due Date is in cell B2 and today’s date is calculated with =TODAY(), you can use the =IFS() function to automatically assign the aging bucket:

=IFS(
  B2 >= TODAY(), "Current",
  TODAY() - B2 <= 30, "1-30 Days",
  TODAY() - B2 <= 60, "31-60 Days",
  TODAY() - B2 <= 90, "61-90 Days",
  TRUE, "90+ Days"
)

Aggregating Outstanding Balances with `=SUMIFS()`

To see how much cash is tied up in each aging bracket, use =SUMIFS(). Assuming column C has the invoice amounts and column D has the aging bracket (calculated above):

=SUMIFS(C:C, D:D, "1-30 Days")

(Repeat this for each bracket to build a complete AR Aging Dashboard).

The 3-Stage Payment Collection Protocol

Don’t let aging invoices sit idle. Implement a standardized collection workflow:

Stage 1 (Day 1-15 Past Due): The Gentle Nudge. Send an automated, friendly email reminder attaching the original invoice. Assume they simply forgot.
Stage 2 (Day 30-45 Past Due): The Firm Request. Send a formal email and make a direct phone call to the accounts payable contact. Confirm receipt and ask for an expected payment date. Pause new work.
Stage 3 (Day 60+ Past Due): The Escalation. Send a final demand letter indicating that the account will be sent to a collection agency or legal counsel if payment is not received within 7 days.

Automate Your Invoicing and Aging

If you don’t want to build this from scratch, our Automated Invoice Generator & Aging Spreadsheet comes with the 5-tier aging brackets and dashboard pre-built. Simply log your invoices, and the system will automatically highlight overdue accounts so you know exactly who to follow up with today.