Accounts Receivable Aging
Categorizes outstanding customer invoices into aging buckets (current, 30, 60, 90+ days) and estimates potential bad debt. Produces a collection priority list so you can focus efforts on the most overdue or highest-value receivables.
Accounting - Accounts Receivable Aging.xlsx
Excel (.xlsx) — No macros — Works in Excel, Google Sheets, LibreOffice
What This Spreadsheet Solves
- Identifying which invoices are past due and by how long
- Estimating bad debt exposure based on aging patterns
- Prioritizing collection calls by amount and overdue duration
- Tracking days sales outstanding (DSO) over time
- Preparing aging reports for auditors or lenders
Who This Is For
- Accounts receivable clerks managing invoice follow-ups
- Controllers monitoring overall receivable health
- CFOs assessing collection risk and cash flow impact
- Credit managers setting customer payment terms
Inputs
- textCustomer name
- textInvoice number
- $Invoice amount
- dateInvoice date
- #Payment terms (days)
Outputs
- Receivables by aging bucket (current, 1-30, 31-60, 61-90, 90+)
- Total outstanding receivables
- Estimated bad debt provision
- Days sales outstanding (DSO)
- Collection priority ranking
How Calculations Work
Each invoice's age is calculated as the number of days between the invoice date and today. Invoices are sorted into aging buckets based on this age relative to the payment terms. Bad debt is estimated by applying a configurable percentage to each bucket, with higher percentages for older buckets. The collection priority score combines the invoice amount with the days overdue to rank which receivables need attention first.
Example Use Case
Scenario: A company has $200,000 in total receivables: $120,000 current, $40,000 at 31-60 days, $25,000 at 61-90 days, and $15,000 over 90 days. Bad debt rates are 1%, 5%, 15%, and 40% respectively.
Result: Estimated bad debt provision is $11,950 ($1,200 + $2,000 + $3,750 + $6,000). DSO is 38 days. The $15,000 in the 90+ bucket is flagged as highest collection priority.
What You Get — 5 Sheets
Technical Details
Frequently Asked Questions
How is DSO calculated?
DSO equals total receivables divided by average daily revenue. If you enter monthly revenue on the CONFIG sheet, the template divides by 30 to get the daily figure.
Can I customize the aging buckets?
Yes. The CONFIG sheet lets you redefine the day ranges for each bucket (e.g., change 90+ to 120+).
What bad debt percentages should I use?
Defaults are 1% for current, 5% for 31-60, 15% for 61-90, and 40% for 90+. Adjust them based on your historical write-off rates.
How do I mark an invoice as partially paid?
Enter the remaining unpaid balance as the invoice amount. The template ages based on the original invoice date regardless of partial payments.
Does this integrate with accounting software?
Not directly. Export your open invoices as CSV from your accounting system and paste them into the INPUT sheet.
Download Accounts Receivable Aging
Ready to use immediately. Enter your data in the INPUT sheet, see results in OUTPUT.