Template · Accounting
Accounts receivable aging report with doubtful account allowance
Age open invoices into buckets by days past due, then estimate the allowance for doubtful accounts and DSO.
Downloads are included in the $19.85 yearly membership. Sign in
XLSXCSVODSXMLNumbers| Accounts receivable aging | |||||
| Example data — replace the blue input cells with your own. | |||||
| TOTAL RECEIVABLES | SHARE PAST DUE | REQUIRED ALLOWANCE | ADJUSTMENT NEEDED | ||
| 104,635.00 | 74.5% | 15,295.60 | 11,495.60 | ||
| Inputs | |||||
| Aging date | Sep 30, 2026 | Invoices are aged as of this date | |||
| Existing allowance balance | 3,800.00 | Balance in the allowance account before this adjustment | |||
| Credit sales in the sales period | 186,500.00 | Sales on account for the DSO calculation | |||
| Days in the sales period | 90 | 90 for a quarter, 365 for a year | |||
| Aging by bucket | |||||
| Bucket | Open balance | Share of total | Allowance rate (example) | Allowance required | Invoices |
| Current | 26,680.00 | 25.5% | 1% | 266.80 | 9 |
| 1-30 days | 31,310.00 | 29.9% | 3% | 939.30 | 4 |
Showing the first 16 of 37 rows and 6 of 6 columns. Cells with formulas show the formula on hover.
| Open balance by customer | |||||||||
| Open balance per customer and bucket, from the Invoices tab. Customer names must match the Invoices tab exactly. | |||||||||
| Customer | Current | 1-30 days | 31-60 days | 61-90 days | Over 90 days | Total open | Share of total | Share over 60 days | Oldest days past due |
| Northwind Example Ltd. | 980.00 | 4,250.00 | 6,400.00 | 0.00 | 0.00 | 11,630.00 | 11.1% | 0.0% | 37 |
| Bluepine Example Supply | 3,870.00 | 0.00 | 0.00 | 3,550.00 | 0.00 | 7,420.00 | 7.1% | 47.8% | 88 |
| Harbor Point Example Inc. | 8,600.00 | 7,250.00 | 0.00 | 0.00 | 9,800.00 | 25,650.00 | 24.5% | 38.2% | 152 |
| Summit Example Grocers | 4,655.00 | 1,310.00 | 3,020.00 | 2,920.00 | 765.00 | 12,670.00 | 12.1% | 29.1% | 152 |
| Cedar Example Clinic | 3,375.00 | 0.00 | 0.00 | 1,140.00 | 0.00 | 4,515.00 | 4.3% | 25.2% | 74 |
| Lakeside Example Builders | 5,200.00 | 18,500.00 | 0.00 | 14,250.00 | 4,800.00 | 42,750.00 | 40.9% | 44.6% | 172 |
| Total | 26,680.00 | 31,310.00 | 9,420.00 | 21,860.00 | 15,365.00 | 104,635.00 | 100.0% | 35.6% | 172 |
| Add a customer by extending the list and the totals; the Aging check flags a mismatch. |
Showing the first 13 of 13 rows and 10 of 10 columns. Cells with formulas show the formula on hover.
| Open invoices | ||||||||||
| One row per invoice. Blue columns are inputs; the others are formulas and stay blank on empty rows. | ||||||||||
| Column K is a helper for the oldest-days figure on the Customers tab. It is 0 for paid and empty rows. | ||||||||||
| Customer | Invoice no. | Invoice date | Terms (days) | Due date | Invoice amount | Amount paid | Open balance | Days past due | Aging bucket | Past-due days (helper) |
| Northwind Example Ltd. | INV-1001 | Aug 28, 2026 | 30 | Sep 27, 2026 | 4,250.00 | 0.00 | 4,250.00 | 3 | 1-30 days | 3 |
| Northwind Example Ltd. | INV-1014 | Sep 15, 2026 | 30 | Oct 15, 2026 | 1,980.00 | 1,000.00 | 980.00 | 0 | Current | 0 |
| Northwind Example Ltd. | INV-1029 | Jul 10, 2026 | 45 | Aug 24, 2026 | 6,400.00 | 0.00 | 6,400.00 | 37 | 31-60 days | 37 |
| Bluepine Example Supply | INV-2201 | Sep 1, 2026 | 30 | Oct 1, 2026 | 2,750.00 | 0.00 | 2,750.00 | 0 | Current | 0 |
| Bluepine Example Supply | INV-2208 | Jun 12, 2026 | 30 | Jul 12, 2026 | 3,900.00 | 1,500.00 | 2,400.00 | 80 | 61-90 days | 80 |
| Bluepine Example Supply | INV-2215 | May 5, 2026 | 60 | Jul 4, 2026 | 1,150.00 | 0.00 | 1,150.00 | 88 | 61-90 days | 88 |
| Bluepine Example Supply | INV-2230 | Sep 22, 2026 | 30 | Oct 22, 2026 | 1,120.00 | 0.00 | 1,120.00 | 0 | Current | 0 |
| Harbor Point Example Inc. | HP-5531 | Sep 20, 2026 | 15 | Oct 5, 2026 | 8,600.00 | 0.00 | 8,600.00 | 0 | Current | 0 |
| Harbor Point Example Inc. | HP-5540 | Aug 14, 2026 | 30 | Sep 13, 2026 | 12,250.00 | 5,000.00 | 7,250.00 | 17 | 1-30 days | 17 |
| Harbor Point Example Inc. | HP-5562 | Mar 2, 2026 | 60 | May 1, 2026 | 9,800.00 | 0.00 | 9,800.00 | 152 | Over 90 days | 152 |
| Summit Example Grocers | SG-3307 | Sep 28, 2026 | 30 | Oct 28, 2026 | 920.00 | 0.00 | 920.00 | 0 | Current | 0 |
| Summit Example Grocers | SG-3290 | Aug 3, 2026 | 30 | Sep 2, 2026 | 1,310.00 | 0.00 | 1,310.00 | 28 | 1-30 days | 28 |
Showing the first 16 of 205 rows and 11 of 11 columns. Cells with formulas show the formula on hover.
| Accounts receivable aging |
| Notes: what this workbook does, how to use it and the method behind it. |
| What it does |
| Ages open customer invoices into buckets by days past due as of an aging date, totals the open balances by customer and bucket, and estimates the allowance for doubtful accounts from example bucket rates. It also shows the share of receiva… |
| How to use it |
| 1. On the Aging tab, enter the aging date, the existing allowance balance, the credit sales for the period and the days in that period. |
| 2. On the Invoices tab, enter each open invoice: customer (must match the Customers tab), invoice number, invoice date, terms in days, invoice amount and amount paid. The rest of the row is calculated. |
| 3. On the Customers tab, enter the customer names. Read the open balance in each bucket for each customer. |
| 4. On the Aging tab, set the allowance rate for each bucket to your own collection history. Read the required allowance and the adjustment needed. |
| 5. Read the checks on the Aging tab. Fix any invoice with no customer or an overpayment before you post the adjustment. |
| Formulas and method |
| Due date = invoice date + terms. Days past due = aging date - due date, or 0 when the invoice is not yet due. |
| Open balance = invoice amount - amount paid. An invoice with an open balance of zero or less is labeled Paid. |
Showing the first 16 of 33 rows and 1 of 1 columns. Cells with formulas show the formula on hover.
What does this template do?
This workbook ages open customer invoices by days past due and estimates the allowance for doubtful accounts. Each invoice on the Invoices tab has terms in days, a due date equal to the invoice date plus the terms, and an open balance equal to the invoice amount less the amount paid. Days past due are measured from the aging date on the Aging tab, and each invoice falls into the Current, 1-30, 31-60, 61-90 or Over 90 bucket.
The Aging tab totals each bucket, applies an allowance rate to each one and compares the required allowance with the existing balance to give the adjustment. It also shows the share of receivables past due, the share over 60 days, and days sales outstanding, which is receivables divided by credit sales times the days in the period. The Customers tab splits the open balance by customer and bucket and shows each customer's oldest days past due.
The example data is fictional: 25 invoices for six invented customers, aged at September 30, 2026. The allowance rates are example values to replace with your own collection history.
What’s inside
- Due dates from invoice date plus terms, and days past due from the aging date
- Five aging buckets with SUMIF totals, shares and counts
- Allowance required from bucket rates, compared with the existing balance
- Days sales outstanding from credit sales and the period length
- Open balance by customer and bucket, with the oldest days past due
Which tabs does the workbook have?
| Tab | What it holds |
|---|---|
| Aging | Inputs, the bucket totals, the allowance calculation, receivable measures and checks. |
| Customers | Open balance per customer and bucket, with the share over 60 days and the oldest days past due. |
| Invoices | One row per invoice: dates, terms, amounts, open balance, days past due and bucket. |
| Notes | Purpose, steps, formulas used, assumptions and limits. |
What formulas does this template use?
This template holds 1,105 formulas in 1,371 cells across 4 tabs, so 81% of its cells calculate. They use 9 distinct functions; the longest formula is 246 characters and 249 of them read from another tab.
| Function | Uses | What it does |
|---|---|---|
IF | 2,224 | one result when a test is true, another when false |
MAX | 207 | largest value |
SUMIFS | 30 | adds values meeting several conditions |
SUM | 21 | adds numbers |
SUMPRODUCT | 10 | multiplies matching entries, then adds them |
COUNTIF | 5 | counts cells meeting one condition |
SUMIF | 5 | adds values meeting one condition |
ABS | 1 | absolute value |
ROUND | 1 | rounds to a number of digits |
Counted from the workbook itself. Only functions that Excel, LibreOffice Calc, Google Sheets and Apple Numbers evaluate the same way are used, so the formulas survive every download format.
How do you use it?
- On the Aging tab, enter the aging date, the existing allowance balance and the credit sales for the period.
- On the Invoices tab, enter each open invoice with its customer, dates, terms, amount and amount paid.
- On the Customers tab, enter the customer names so they match the Invoices tab.
- Set the allowance rate for each bucket on the Aging tab.
- Read the adjustment needed and confirm the checks read OK.
What is it good for?
- Monthly receivables review for a small business
- Estimating the allowance for doubtful accounts at a quarter end
- Following up overdue customers by bucket
- Tracking days sales outstanding over time
Questions about this sheet
Is an invoice due today Current or past due?
It is Current. Days past due are zero until the day after the due date.
Why is an invoice labeled Paid?
An invoice whose open balance is zero or less is labeled Paid, so it does not add to any bucket.
Can the allowance rates be changed?
Yes. The rates are inputs on the Aging tab. The 1, 3, 10, 25 and 50 percent values are examples, not a recommended reserve.
How is days sales outstanding calculated?
DSO is total open receivables divided by credit sales for the period, times the number of days in that period.