How do you calculate the allowance for doubtful accounts from an aging report?
Short answer
Age the open invoices into buckets by days past due, multiply each bucket's open balance by an estimated uncollectible rate taken from your own collection history, and add the results. That sum is the required allowance. The adjustment to book is the required allowance minus the balance already in the allowance account.
An aging report groups unpaid invoices by how long they are past due. The longer an invoice has been outstanding, the less likely it is to be paid, so each bucket gets its own estimated uncollectible rate. The allowance sits on the balance sheet as a deduction from receivables.
What is the formula for the required allowance?
- Required allowance = the sum of (open balance in each bucket × that bucket's rate).
- Adjustment needed = required allowance − the balance already in the allowance account.
A positive adjustment raises the allowance and a negative one lowers it. The invoice's open balance is its amount minus what has been paid, and days past due run from the due date, which is the invoice date plus its terms.
What does a worked example look like?
| Bucket | Open balance | Rate | Allowance |
|---|---|---|---|
| Current | 60,000 | 1% | 600 |
| 1-30 days | 25,000 | 3% | 750 |
| 31-60 days | 12,000 | 10% | 1,200 |
| 61-90 days | 6,000 | 25% | 1,500 |
| Over 90 days | 4,000 | 50% | 2,000 |
| Total | 107,000 | 6,050 |
The required allowance is 6,050. If the allowance account already holds 4,500, the adjustment is 1,550, usually booked as a debit to bad debt expense and a credit to the allowance. The rates in the table are illustrative, not a recommended reserve.
Two companion figures come from the same report. Receivables over 60 days are 10,000 of 107,000, or 9.3%. Days sales outstanding (DSO) is total open receivables ÷ credit sales × days in the period: with credit sales of 360,000 over 90 days, DSO = 107,000 ÷ 360,000 × 90 = 26.8 days.
Where do the rates come from?
From your own history: the share of invoices in each bucket that were eventually written off. Start with a year of write-offs, group them by the bucket the invoice was in when you last measured, and use those shares. The rates are estimates, so review them when collection patterns change.
What does an aging-based allowance leave out?
It uses the age of each invoice, nothing more. It does not model a specific reserve for one customer you expect to default, write-offs and recoveries, or credit memos and unapplied cash. Treat those separately, and confirm the accounting treatment with a qualified accountant. This page is arithmetic, not accounting or tax advice.
The accounts receivable aging template builds the buckets with SUMIF, takes the rates as inputs and shows the adjustment against the existing balance.