First page of the CECL Allowance Calculator (WARM Method)Excel workbook preview

Bank supervision (OCC, FFIEC, FDIC)

CECL Allowance Calculator (WARM Method)

CECL WARM method calculator in Excel: six loan pools, loss history, runoff with prepayments, forecast and reversion, qualitative factors and a live ACL summary.

Excel workbook · 23 KB Built on free official texts

3 free downloads per account. Just your email and a 6-digit code.

Need more than 3? Every template for $249, once. Lifetime access, including the templates we add later. See pricing

About this template

The weighted-average remaining maturity (WARM) method is a loss-rate approach the bank regulators discussed for smaller banks moving to the current expected credit loss model. It applies an average annual net charge-off rate to the remaining life of a loan pool, adjusted for amortization and prepayments, and then adds qualitative adjustments. It suits pools of similar, less complex loans where a simple and well-documented method is easier to defend than a model.

This calculator follows the description of the WARM method in the OCC Comptroller's Handbook on allowances for credit losses and the qualitative factors in the interagency policy statement on allowances. Six worked pools run through loss history, a 30-year runoff, a forecast period with reversion to the historical rate, and qualitative add-ons. Every result is a live formula, so you can trace each number back to an input.

Who it is for

The controller or CFO of a smaller community bank who must show the auditor and the examiner how the allowance for credit losses was estimated for less complex loan pools.

What is inside

  • Six worked loan pools for a fictional USD 1.2 billion community bank (USD 900 million amortized cost)
  • Loss History sheet with 48 pool-year rows of net charge-offs and average balances, and live annual and average loss rates
  • Runoff sheet projecting 30 years of balances per pool with straight-line amortization and an annual prepayment rate
  • Forecast period and immediate reversion to the historical loss rate, built into the loss formulas
  • Qualitative factors sheet with the 9 factors from the interagency allowance policy statement, in basis points per pool
  • Summary with amortization-adjusted remaining life, quantitative allowance, qualitative add-on, total allowance and coverage per pool
  • Quarter roll-forward that derives the provision needed from the prior allowance and net charge-offs
  • Every output is a formula off yellow input cells, with a how-this-is-calculated note

How to use it

Replace the pool balances, terms and prepayment rates on the Inputs sheet, then enter your own charge-off history.

Files

  • XLSXcecl-allowance-calculator.xlsx23 KB

Opens in Microsoft Word or Excel, Google Docs or Sheets, and LibreOffice. Built on free, official texts that anyone can read: laws, regulations, standards and guidance published by public bodies. A template to adapt, not legal, tax or accounting advice.

Questions about this template

How does the WARM method work?

It multiplies an average annual net charge-off rate by the amount of loan balance expected to be outstanding over the remaining life of the pool. The remaining life is adjusted for scheduled amortization and prepayments, so it is shorter than the contractual term. Adjustments for current conditions, reasonable and supportable forecasts and other qualitative factors are added on top of that historical figure.

Is the WARM method acceptable for CECL?

A January 2019 staff Q&A from the accounting standard setter said WARM is one of the acceptable methods for less complex pools, and the bank regulators held an interagency webinar on applying it in April 2019. Whether it fits a given pool is a judgement for management and its auditors. It works best for pools with stable loss history and simple terms, and less well for pools whose risk is changing fast.

What qualitative factors should a bank consider?

The interagency policy statement on allowances lists factors such as the nature and volume of assets, concentrations of credit, past due and classified volumes, collateral values, lending policies, the quality of credit review, staff experience, external factors, and changes in economic and business conditions. Each adjustment needs a written reason and must not repeat something already in the loss rate or forecast.