An accounts receivable aging analysis sorts overdue invoices by age. This calculator takes four overdue buckets and an expected loss rate for each. It returns the total overdue, the share older than 60 days, the allowance you may need and the amount you expect to collect.
The formula
Expected loss (bucket) = bucket amount x loss rate (%)
Allowance needed = sum of the expected losses
Allowance % = allowance / total overdue x 100
Share over 60 days = (61-90 + 90+ buckets) / total x 100
Expected to collect = total - allowance
Worked example
| Bucket | Amount | Loss rate | Expected loss |
|---|---|---|---|
| 1-30 days | $20,000 | 1% | $200 |
| 31-60 days | $10,000 | 5% | $500 |
| 61-90 days | $5,000 | 15% | $750 |
| 90+ days | $2,000 | 40% | $800 |
Total overdue: $37,000
Share over 60 days: $7,000 / $37,000 = 18.92%
Allowance needed: $2,250, which is 6.08% of the total
Expected to collect: $37,000 - $2,250 = $34,750
How to read the result
Look at two numbers first. The share over 60 days shows how much of your risk is old. The allowance percentage shows how large the expected loss is against the total.
Keep these limits in mind:
- Rates are estimates. Replace the defaults with rates from your own past write-offs.
- One customer can dominate. A single large invoice in the 90+ bucket can change the picture. Review big accounts one by one.
- Accounting rules differ. Standards and tax rules vary by country. An accountant should confirm how to book the allowance.
Run this analysis every month. Compare the buckets over time, not only the totals. The guide on the invoice aging report shows how to build and use one.
Related tools
- Bad debt percentage calculator
- Collection effectiveness index calculator
- Account statement generator to send overdue customers a statement
- Guide: Bad debt
- Guide: Overdue invoices
Frequently asked questions
- What is an accounts receivable aging report?
- It groups unpaid invoices by how long they are overdue, for example 1 to 30 days and 31 to 60 days. It shows which debts are at risk. It also helps you plan collection work.
- How do I estimate the allowance for doubtful accounts?
- Multiply each bucket by an expected loss rate and add the results. This is the aging method. Base the rates on your own history of unpaid invoices. The defaults in the calculator are only a starting point.
- What loss rates should I use?
- Use your own data if you have it. Older debts normally carry higher rates, because they are less likely to be paid. Rates differ by industry, so adjust the defaults and ask your accountant.
- Why is the share over 60 days important?
- Debts older than 60 days are harder to collect. A rising share is an early warning of future write-offs. Track it each month along with the total.
- Does this calculator include current invoices?
- No. The buckets start at 1 day overdue. Enter only overdue amounts. Invoices that are not yet due are outside the four buckets.