Receivables Aging Collection Forecast

Method and assumptions

For each aging bucket, scenario collections = bucket balance × its entered recovery percentage. Total expected collections sum across buckets; exposure = total receivables − expected collections. Weighted recovery percentage = expected collections ÷ total receivables when the total is positive; an empty balance shows “No balance” rather than a numeric percentage.

Worked example

Balances of $5,000, $1,800, $800, and $500 with recovery assumptions of 95%, 75%, 45%, and 15% yield $6,535 expected collections from $8,100 receivables. The weighted recovery is about 80.68%, leaving $1,565 exposure.

How to use the result

Use editable rates for low, base, and high scenarios based on your own history. The buckets in this form are current, 1–30, 31–60, and over 60 days; this is not a credit-loss standard or a collection promise.

Input meanings and limits

• Current / not yet due balance: Amount in the current bucket.
• Current-bucket recovery assumption: Your estimated recovery percentage for this bucket.
• 1–30 days past due balance: Balance in your selected 1–30 day bucket.
• 1–30 day recovery assumption: Enter your own scenario rate.
• 31–60 days past due balance: Balance in your selected 31–60 day bucket.
• 31–60 day recovery assumption: Enter your own scenario rate.
• More than 60 days past due balance: Balance beyond 60 days past due.
• More-than-60-day recovery assumption: Enter your own scenario rate.

Inputs accept finite, nonnegative values in the displayed ranges; percentages are bounded to 0–100% (with 99.99% maximum where division by the remaining share is used), and whole-unit or month fields require integers. Currency figures are shown in USD for convenience; enter values in one consistent currency. This is a planning worksheet, not financial, legal, tax, or accounting advice.

Related calculators

Cash conversion cycle calculator and Invoice early-payment discount rate calculator.