Blog · Finance metrics and formulas · Finance and FP&A teams
Apply company-approved loss rates to receivable aging buckets to calculate a required allowance. This worked example shows invoice-level aging, spreadsheet formulas and the adjustment from the recorded allowance balance.
The accounting aging method applies company-approved loss rates to unpaid receivable balances grouped by age. The bucket estimates add to the required ending allowance under those assumptions. Compare that requirement with the allowance already recorded to determine the adjustment.
Required allowance = Sum of each aging bucket's receivables × Its approved loss rate
Allowance adjustment = Required allowance − Existing allowance credit balance
The OpenStax explanation of aging-based bad debt estimation distinguishes the estimated ending allowance from the adjustment after considering a carryover balance. Applying a percentage to receivables does not mean booking the entire result as current-period expense.
Rates require judgment. US banking regulators' credit-loss FAQs describe historical experience, current conditions and reasonable and supportable forecasts as relevant inputs to credit-loss estimates. That banking guidance is context, rather than a compliance determination for your company. The worksheet below applies supplied assumptions; management approves the method, rates and any account-specific treatment.
Use unpaid balances as of October 31, 2026, after posted receipts, credits and write-offs. Include only receivables within the method's approved scope. Reconcile the total to gross trade receivables before estimating an allowance; the accounts receivable formula explains that balance bridge.
On a worksheet named Invoices, use columns A:D below. Put the as-of date in Setup!B1 as an actual date. These are illustrative US customer invoices, with amounts in USD.
| Invoice (A) | Due date (B) | Open balance (USD) (C) | Days past due (D) |
|---|---|---|---|
| INV-1001 | November 15, 2026 | 80,000 | 0 |
| INV-1002 | October 31, 2026 | 40,000 | 0 |
| INV-1003 | October 15, 2026 | 60,000 | 16 |
| INV-1004 | September 15, 2026 | 30,000 | 46 |
| INV-1005 | August 15, 2026 | 15,000 | 77 |
| INV-1006 | July 15, 2026 | 10,000 | 108 |
| Total | 235,000 |
Calculate D2 and fill down through D7:
=IF(OR(B2="",C2=""),NA(),MAX(0,Setup!$B$1-B2))
The formula keeps future due dates at zero and exposes missing dates or balances as errors. Resolve those exceptions before summing the buckets. A balance with a missing due date should not quietly enter the current bucket.
This schedule groups by days past due. It does not measure collection speed like days sales outstanding, and it does not use sales revenue as the allowance base.
On an Aging worksheet, place the following table in A:F, with data on rows 2:6. The rates are illustrative assumptions supplied by the company's accounting owner, rather than recommended rates. The last bucket has no upper boundary.
| Bucket (A) | Minimum days (B) | Maximum days (C) | Receivables (USD) (D) | Approved loss rate (E) | Required allowance (USD) (F) |
|---|---|---|---|---|---|
| Current or due today | 0 | 0 | 120,000 | 1% | 1,200 |
| 1–30 days past due | 1 | 30 | 60,000 | 3% | 1,800 |
| 31–60 days past due | 31 | 60 | 30,000 | 8% | 2,400 |
| 61–90 days past due | 61 | 90 | 15,000 | 20% | 3,000 |
| More than 90 days past due | 91 | No upper limit | 10,000 | 50% | 5,000 |
| Total | 235,000 | 13,400 |
For the bounded buckets, enter D2 and fill through D5:
=SUMIFS(Invoices!$C$2:$C$7,Invoices!$D$2:$D$7,">="&B2,Invoices!$D$2:$D$7,"<="&C2)
Use this in D6 for the oldest bucket:
=SUMIFS(Invoices!$C$2:$C$7,Invoices!$D$2:$D$7,">="&B6)
In F2, enter =D2*E2 and fill through F6. The total allowance can be calculated as:
=SUMPRODUCT(D2:D6,E2:E6)
Microsoft documents SUMPRODUCT as summing products of corresponding ranges. Enter rates as numeric percentages; text entries can silently contribute zero in that function. Covirage is not affiliated with Microsoft.
The required allowance is $1,200 + $1,800 + $2,400 + $3,000 + $5,000 = $13,400. These calculations produce whole dollars, so no rounding is needed.
Assume the ledger shows an existing allowance credit balance of $9,000 after all other postings. The adjustment is $13,400 − $9,000 = $4,400. Subject to management review and company posting procedures, that means a $4,400 debit to bad debt expense and a $4,400 credit to the allowance.
| Check | Calculation | Result (USD) |
|---|---|---|
| Invoice total less bucket total | 235,000 − 235,000 | 0 |
| Adjusted allowance | 9,000 + 4,400 | 13,400 |
| Net receivables after allowance | 235,000 − 13,400 | 221,600 |
The bucket check proves completeness, while the allowance check proves the adjustment reaches the required balance. Keep the approved rate version with the schedule. Stated and estimated figures should remain distinguishable: invoice balances come from records, while loss rates represent approved estimates.
Upload a file or Google Sheet containing your open receivables, due dates, approved rates and recorded allowance. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains the result with every figure traced to its rows. Create a free Covirage account and bring a sample of your own receivables file.
See financial planning and reporting for the supporting finance workflow.
It calculates the required ending allowance using the supplied loss rates. The expense adjustment is the difference between that result and the allowance already recorded after other postings. Ignoring the existing balance can overstate the adjustment.
Use the basis defined in the company's approved method. Days past due and days since invoice are different measures. This example uses days past due, groups invoices not yet due with those due today, and labels the boundaries explicitly.
Management and the accounting policy owner approve rates and adjustments using the evidence relevant to their business. The percentages in this example are illustrative assumptions. A spreadsheet or analytics tool can apply supplied rates, but it does not establish their accounting appropriateness.
Treat it as a negative credit balance in the adjustment calculation. Subtracting that negative amount increases the required adjustment. First confirm write-offs, recoveries and other postings are complete so the calculation uses the actual pre-adjustment ledger balance.