Blog · Finance metrics and formulas · Finance and FP&A teams
The aging of accounts receivable method applies documented loss rates to aged customer balances. Work through a required allowance, its reconciliation to the existing balance and a sensitivity using management-supplied assumptions.
The aging of accounts receivable method estimates a credit-loss allowance by applying supported loss rates to aged receivable balances. Add the bucket estimates to obtain the required ending allowance, then compare it with the allowance already recorded to identify the adjustment for accounting review.
An accounts receivable aging report sorts the unpaid invoices. The allowance calculation adds a different layer: management's estimate of how much of those balances will not be collected. An old invoice is evidence to investigate, rather than proof that its entire balance is lost.
Required allowance = Sum of (Bucket balance × Approved expected loss rate)
Allowance adjustment = Required ending allowance − Existing credit allowance balance
Net receivables = Gross receivables − Required allowance
The adjustment formula assumes the existing allowance is expressed as a positive credit balance. A debit balance needs the opposite sign in that input. Keep the convention explicit.
FASB's trade-receivables aging illustration, reproduced in its June 2024 PCC materials, shows an aging schedule as one way to estimate expected credit losses. Its example rates illustrate that method; they are not rates to copy into your company's workbook.
Use the reconciled gross receivables export at the reporting date. Keep customer, entity, currency, invoice, due date, open balance and bucket. Confirm that cash and credits posted through the cutoff have been applied correctly and that the export ties to the ledger.
In a separate assumption table, record the portfolio segment, bucket, approved loss rate, source, rationale, approver and effective date. Segment balances that have different risk characteristics before applying rates. Have accounting review customer-specific circumstances that may require separate assessment.
For each rate, preserve the relevant history and management's adjustments. A historical loss percentage needs a defined population and observation period; dividing this month's write-offs by this month's sales does not automatically produce the loss experience of today's aged balances. Distinguish supplied facts from estimated assumptions so a reviewer can see what the workbook establishes and what management has judged.
An illustrative US wholesaler has the following gross receivables at September 30, 2026. Management supplies the example rates for a single risk pool; they are assumptions for this calculation, not benchmark rates. Put buckets in A2:A6, balances in B2:B6 and rates in C2:C6.
| Aging bucket | Gross balance (USD) | Supplied loss rate | Calculation | Expected loss (USD) |
|---|---|---|---|---|
| Current | 600,000 | 0.5% | 600,000 × 0.005 | 3,000 |
| 1–30 days past due | 200,000 | 2.0% | 200,000 × 0.02 | 4,000 |
| 31–60 days past due | 100,000 | 6.0% | 100,000 × 0.06 | 6,000 |
| 61–90 days past due | 60,000 | 20.0% | 60,000 × 0.20 | 12,000 |
| Over 90 days past due | 40,000 | 60.0% | 40,000 × 0.60 | 24,000 |
| Total | 1,000,000 | 49,000 |
In Excel, calculate expected loss in D2 and fill down:
=B2*C2
Sum D2:D6. Independently check the multiplication and total using:
=SUMPRODUCT(B2:B6,C2:C6)
Microsoft's SUMPRODUCT documentation describes this multiplication of corresponding ranges followed by summation. Covirage is not affiliated with Microsoft.
Both routes return $49,000. The weighted loss rate is $49,000 / $1,000,000 = 4.9%; do not average the bucket percentages. Net receivables are $1,000,000 − $49,000 = $951,000. Dollar results are exact; displayed percentages use one decimal place.
The required allowance is an ending balance. The existing allowance must first include movements already recorded through the reporting date:
| Existing allowance movement | Credit-balance effect (USD) | Running credit balance (USD) |
|---|---|---|
| Opening allowance | 42,000 | 42,000 |
| Write-offs charged against allowance | -8,000 | 34,000 |
| Recoveries credited to allowance | 3,000 | 37,000 |
| Adjustment to reach required allowance | 12,000 | 49,000 |
The existing credit allowance before adjustment is $37,000. Therefore $49,000 − $37,000 = $12,000 is the additional provision in this simplified example. Charging $49,000 again would ignore the balance already recorded.
If the required allowance is in D7 and the existing credit balance is in F2:
=D7-F2
The controller reviews the resulting adjustment and any entry. The schedule supports that review; it does not approve policy or post an entry. Use an allowance flux analysis to explain changes between reporting dates.
Keep every balance and other rate unchanged, but raise the over-90 loss assumption from 60.0% to 70.0%. Its estimate becomes $40,000 × 0.70 = $28,000, an increase of $4,000. The total allowance becomes $53,000, net receivables become $947,000 and the adjustment against the same $37,000 existing allowance becomes $16,000.
This sensitivity isolates rate risk. Moving balances into older buckets is a separate driver. Neither calculation forecasts future sales; forecasting receivables using DSO addresses that different planning job.
Bring your reconciled aging, approved rate table and allowance rollforward to Covirage; its tools compute bucket estimates, compare supplied scenarios and check the totals. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data.
See Covirage for finance teams for the supporting reporting context.
It first estimates the required ending allowance. To determine the adjustment, compare that estimate with the allowance balance after existing write-offs, recoveries and other movements. The required allowance and the period's credit-loss expense therefore need not be equal.
Yes. A balance does not need to be overdue to carry expected credit-loss exposure. Use a documented estimate for the current bucket as well as overdue balances, unless a supported assessment under the applicable policy concludes that no allowance is needed.
Management supplies supported rates based on relevant loss experience, portfolio characteristics and the accounting policy applied. Document the history, adjustments, approval and reporting date. Percentages used in a worked example are assumptions, rather than industry benchmarks or prescribed accounting rates.
No. Aging-based allowance estimation assesses expected credit losses on existing receivables. A DSO forecast estimates future receivables from sales and collection assumptions. Keep the schedules separate, then reconcile their definitions when preparing an integrated financial forecast.