Accounts Receivable (A/R) Aging Tracker & Debt Collection Spreadsheet

1. Operational Challenge & Business Context

The Problem This Solves:When clients pay late, your business acts as an interest-free bank. An A/R Aging schedule categorizes overdue balances by severity so your team can prioritize collection calls before debts become uncollectible.

Statistical research indicates that an invoice 90 days overdue has only a 73% probability of collection, dropping below 50% at 180 days. Implementing a weekly A/R aging review ensures past-due accounts receive structured follow-up calls before debts turn bad.

2. Free Template Download & Cloud Access

Grab the ready-to-use spreadsheet below in either Microsoft Excel or Google Sheets format:

Verified Free Download (v1.9)

File Size: 48 KB • License: Free for Commercial & Personal Use

Small Business Accounts Receivable Aging Report

Download the fully customizable template pre-configured with formulas, validation rules, and summary dashboards. Compatible with all modern versions of Excel and Google Sheets.

Automated Dynamic Aging Buckets (Current, 1-30, 31-60, 61-90, 90+ Days)

Days Past Due (DPD) Calculation Linked to TODAY()

Customer Balance Rollup & Collection Call Notes Log

Color-coded Aging Severity Indicators

Print-ready Cash Collection Worklist

Download for Excel (.xlsx)Open in Google Sheets (Make a Copy)

3. Step-by-Step Setup & Customization Guide

Enter Issued Invoices

Input Invoice Number, Client Name, Invoice Date, Due Date, and Original Invoice Amount.

Pro Tip: Set payment terms (e.g. Net 30) to establish the baseline Due Date.

Let Formulas Sort Open Balances Automatically

The template dynamically compares today's date with the due date and populates the appropriate aging bucket column.

Pro Tip: Record customer phone call promises and expected wire dates in Column K.

Review Total Overdue Portfolio Risk

Check the Summary Dashboard to see what percentage of your total receivables is past 60 and 90 days.

Pro Tip: Aim to keep 90+ day balances below 5% of total receivables.

4. Formula & Automation Architecture

The aging categorization uses dynamic logical IFS and AND formulas that automatically update every morning based on the system clock TODAY() without requiring manual recalculation.

Core Formulas Used in This Sheet:

=TODAY() - DueDate

Calculates exact days past due for open invoices.

=IF(DaysPastDue<=0, InvoiceAmount, 0)

Sorts open balances into the 'Current (Not Overdue)' bucket.

=IF(AND(DaysPastDue>30, DaysPastDue<=60), InvoiceAmount, 0)

Routes invoice into the 31-60 days aging bucket.

=IF(DaysPastDue>90, InvoiceAmount, 0)

Flags critical high-risk accounts in the 90+ days severe aging bucket.

5. Frequently Asked Questions & Troubleshooting

What should I do when an invoice reaches the 90+ day bucket?

Place future work for that client on credit hold immediately, send a formal demand letter, and consider offering a small settlement discount for immediate card or wire payment.