Bad Debt Provision Calculator

The Bad Debt Provision Calculator calculates expected credit losses on receivables from ageing analysis, default rates, write-off history, and management overlays.

What Is a Bad Debt Provision Calculator?

A bad debt provision is an estimate of receivables you do not expect to collect. The provision sits in the allowance for doubtful accounts, which reduces reported accounts receivable (AR). A calculator is a tool that structures this estimate using inputs, formulas, and consistent steps.

It supports different approaches, including percentage‑of‑sales, aging of receivables, and expected credit loss. The goal is to produce a reasonable and supportable number at close. It also helps you document the method, assumptions, and year‑over‑year changes.

Good calculators allow scenario testing. You can adjust collection patterns, credit risk, or economic overlays. You can also compare results across business units to keep policy application consistent.

Formulas for Bad Debt Provision

Several accepted formulas exist. Your policy and reporting framework determine which one you use. Below are the most common formulations you will see in audits and management reviews.

• Aging method: Provision = Σ (Balance in bucket i × Historical loss rate for bucket i). Buckets often include 0–30, 31–60, 61–90, 91–120, and 120+ days past due.
• Percentage of sales: Provision = Credit sales × Bad debt rate. This method ties losses to sales volume and works well for high‑turnover portfolios.
• Historical default rate on AR: Provision = Total AR × Long‑run default rate. Use when aging is not reliable or the portfolio is very homogeneous.
• Expected credit loss (ECL): Provision = Σ (Exposure at default × Probability of default × Loss given default), discounted if material. ECL aligns with IFRS 9 and CECL concepts.
• Roll‑forward check: Ending allowance = Beginning allowance + Current‑period provision − Write‑offs + Recoveries. This ensures your ledger ties out.

All methods should converge over time if assumptions are stable. The aging and ECL methods usually give the most granular breakdown and best audit trail. Keep your approach consistent unless the business model or data quality changes.

How to Use Bad Debt Provision (Step by Step)

Successful provisioning follows a simple rhythm each close. You gather data, apply the formula, and reconcile to the ledger. Document every key assumption and any management overlay.

• Assemble accurate AR data, including aging buckets and any credit memos not yet posted.
• Choose the method stated in your policy, or justify a change with evidence.
• Determine loss rates from history, peers, or model outputs, and adjust for current conditions.
• Apply rates to balances or sales as the formula requires, then compute the proposed provision.
• Reconcile the ending allowance with write‑offs and recoveries to ensure the roll‑forward ties.
• Review the results against prior periods and key ratios to spot outliers.

These steps scale from a small ledger to an enterprise portfolio. The calculator standardizes the process so teams can reproduce results and explain movements clearly.

Inputs and Assumptions for Bad Debt Provision

Provisioning quality depends on the inputs. Identify the source system, extract time stamps, and confirm that aging reflects invoice dates rather than due dates unless your policy says otherwise. Then set assumptions that reflect both history and current conditions.

• Accounts receivable by aging bucket: The dollar balances in each time band (for example, 0–30, 31–60, 61–90, 91–120, 120+ days).
• Historical loss rates: The percentage of balances historically written off in each bucket, net of recoveries when material.
• Credit sales and bad debt rate: For percentage‑of‑sales methods, use net credit sales and a validated loss percentage.
• Write‑offs and recoveries: Actual charge‑offs and collections on previously written‑off accounts for the roll‑forward.
• Risk and macro overlays: Adjustments for customer concentration, recession risk, or sector stress where supported by data.
• Materiality thresholds: Minimum levels for adjustments, rounding, and disclosure to avoid immaterial churn.

Set reasonable ranges for each assumption. For example, loss rates in the current bucket usually range from 0.1% to 2% in stable portfolios. Be careful with small samples, newly launched products, or rapid growth periods; history may not be predictive. If an input seems extreme, run sensitivity tests and document why the edge case is still credible.

Using the Bad Debt Provision Calculator: A Walkthrough

Here’s a concise overview before we dive into the key points:

1. Upload or paste your AR aging table, including customer, invoice date, due date, and balance.
2. Select the provisioning method: aging, percentage of sales, historical default, or ECL.
3. Enter loss rates for each bucket or provide model outputs for PD, LGD, and exposure.
4. Add write‑offs and recoveries for the period to enable a roll‑forward check.
5. Optionally apply a macro overlay, such as a 10% uplift for sector stress, with justification notes.
6. Review the computed provision, allowance roll‑forward, and bucket‑level breakdown.

These points provide quick orientation—use them alongside the full explanations in this page.

Case Studies

B2B wholesaler using the aging method: AR totals $1,000,000 with buckets as follows: 0–30 $700,000 at 0.5%; 31–60 $180,000 at 2%; 61–90 $80,000 at 6%; 91–120 $30,000 at 15%; 120+ $10,000 at 50%. Provision = (700,000×0.005) + (180,000×0.02) + (80,000×0.06) + (30,000×0.15) + (10,000×0.5) = 3,500 + 3,600 + 4,800 + 4,500 + 5,000 = $21,400. Management overlays 10% for a key customer under review, adding $2,140. Final provision: $23,540; write‑offs this month were $20,000 and recoveries $2,000, which tie out in the roll‑forward. What this means: Aging shows most risk in older buckets, and the overlay is small but supportable given the customer review.

SaaS provider using an ECL approach: Monthly AR is $2,500,000. The team segments customers into Small (EAD $1,200,000, PD 2.5%, LGD 60%), Mid‑Market (EAD $1,000,000, PD 1.2%, LGD 45%), and Enterprise (EAD $300,000, PD 0.4%, LGD 35%). ECL = (1,200,000×0.025×0.60) + (1,000,000×0.012×0.45) + (300,000×0.004×0.35) = 18,000 + 5,400 + 420 = $23,820. A recession scenario increases PDs by 20%, making ECL $28,584; management selects a midpoint overlay for $26,200. What this means: Segmentation exposes higher losses in small accounts, and a scenario‑weighted overlay captures macro uncertainty.

Accuracy & Limitations

Provisioning is an estimate. Accuracy improves with clean data, strong history, and thoughtful overlays. Even then, sudden customer failures and fast economic shifts can break patterns. The calculator structures the work but cannot remove judgment.

• Data quality limits the result; mis‑aged invoices or missing credits skew loss rates.
• Short histories produce unstable rates; consider peer or industry data with care.
• Overlays can reduce bias but may add subjectivity if not evidence‑based.
• Rapid growth or product changes weaken comparability with prior periods.
• Discounting benefits are often immaterial for short‑dated AR; confirm materiality first.

Use the tool to test ranges and document rationale. Peer comparisons, back‑testing, and roll‑forward checks help anchor judgment. Keep your policy and disclosures aligned with your accounting framework.

Disclaimer: This tool is for educational estimates. Consider professional advice for decisions.

Units Reference

Clear units make inputs comparable and prevent mistakes. Receivable balances are in currency, rates in percentages or bps, and time in days. The table below lists the most common units used in provisioning and how to interpret them.

When you see a rate expressed in bps, divide by 10,000 to get a decimal. For example, 250 bps is 2.5%. Keep day counts consistent with your system’s aging logic.

Tips If Results Look Off

If the provision seems too high or too low, start with the data. Most surprises come from aging errors, duplicate invoices, or old credits not applied. Then check that assumptions match your policy and the current period’s risk.

• Rebuild the aging from raw transactions and compare to the summary report.
• Scan for one‑off large balances driving older buckets.
• Run sensitivity tests on loss rates and overlays to see which inputs matter most.
• Compare the roll‑forward to actual write‑offs and recoveries.

Document every correction and keep a change log. This creates a clear audit trail and speeds future closes.

FAQ about Bad Debt Provision Calculator

Is the bad debt provision the same as the allowance for doubtful accounts?

The provision is the expense recorded this period; the allowance is the cumulative balance on the balance sheet that offsets AR.

Which method is best: aging, percentage of sales, or ECL?

Use the method in your policy. Aging suits most AR portfolios; ECL offers deeper risk modeling; percentage of sales works for simple, high‑turnover businesses.

How often should I update loss rates?

Update at every reporting date, with full refresh at least quarterly. Recalibrate if customer mix or economic conditions change materially.

Do I need discounting for short‑term receivables?

Usually not. For short‑dated AR, the time value of money is immaterial. Apply discounting only if the timing of losses is long or material.

Glossary for Bad Debt Provision

Accounts Receivable (AR)

Money owed by customers for goods or services delivered on credit, recorded as a current asset before allowance.

Allowance for Doubtful Accounts

A contra‑asset balance that reduces AR to its net realizable value, reflecting expected uncollectible amounts.

Bad Debt Provision

The current‑period expense recognized to increase the allowance, based on estimated uncollectible receivables.

Write‑off

The removal of a specific receivable deemed uncollectible from AR and the allowance, with no income statement effect at write‑off.

Recovery

Cash collected on previously written‑off receivables, recorded as a credit to bad debt expense or the allowance per policy.

Probability of Default (PD)

The likelihood that a customer will fail to pay over a specified horizon, used in ECL models.

Loss Given Default (LGD)

The share of exposure not recovered if default occurs, after collateral, offsets, or collection efforts.

Exposure at Default (EAD)

The outstanding balance at the time of default, including principal and any accrued amounts expected to be owed.

Sources & Further Reading

Here’s a concise overview before we dive into the key points:

• IFRS 9 Financial Instruments overview (Deloitte iGAAP)
• FASB ASC 326 Current Expected Credit Losses (CECL)
• PwC Guide: Expected credit losses
• Deloitte CECL implementation guide
• Investopedia: Allowance for Doubtful Accounts

These points provide quick orientation—use them alongside the full explanations in this page.