Free Excel templates

Contingencies and Provisions Model

Claims, ranges, sales and claims history, retirement cash flows in; accruals, roll-forwards and proofs out, with a Checks sheet.

Type each claim with counsel's assessment and range, the pump sales and the claims history, and the retirement cash flows once, and the workbook keeps a manufacturer's provisions for three years: a probability matrix that decides accrue, disclose or neither for every matter, a lawsuit accrued at the low end of its range, a 24-month warranty reserve rolled forward and proved a second way, and an asset retirement obligation kept in layers at their own rates. In the worked case the lawsuit is accrued at $1,200,000 at the end of 2025, with $1,800,000 of reasonably possible loss disclosed above it.

Get the template

One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 212 free models.

Contingencies_Provisions_Model.xlsx (32 KB)

You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.

The book behind this model

This model goes with The Contingencies and Provisions Handbook, which explains the method behind it, chapter by chapter.

Get the PDF edition → See the book

Want every model at once?

The Financial Model Library: all 213 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).

Get the Library, $79 →

This free file stays free. Secure checkout by Stripe.

Not on Amazon: 24 paperbacks by Julian R. Sterling on insurance and retirement, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

5 sheets. Inputs can be changed; everything else is a formula, and the last sheet checks the figures against the book.

Inputs
Claims, counsel's ranges and assessments, pump sales, the warranty rate and claims history, the retirement scenarios, inflation, risk premium and rates, in yellow cells.
Litigation
The claims inventory as a probability matrix, and the lawsuit accrued at the low end of its range, the reasonably possible excess and the 2026 settlement after year-end.
Warranty
The accrual rate tested by expected value, the five-line roll-forward, the change in estimate for a defect campaign, and the liability proved a second way.
ARO
Each layer measured at its own credit-adjusted risk-free rate, accretion year by year, a downward revision traced to its layer, and each layer proved by present value.
Checks
Ties that can fail: the accrual against the ledger, the opening warranty balance against the prior balance sheet, the rate against history, the layers against the liability.

What the worked case returns

MeasureValue
Litigation accrual at 31 December 2025, the low end of the range$1,200,000
Reasonably possible loss above the accrual, disclosed, 2025$1,800,000
2026 charge when the settlement after year-end is recognized$550,000
Warranty accrual on 2026 sales$1,080,000
Change in estimate for preexisting warranties, 2026 (seal campaign)$420,000
Warranty liability, 31 December 2026$602,000
The same liability proved a second way$602,000
ARO layer 1 at inception, 1 January 2024$1,542,430
Accretion, 2026 (both layers)$156,965
Downward revision, 31 December 2026 (US dollars)271,967
Asset retirement obligation, 31 December 2026$2,067,800

The warranty liability is never trusted on the roll-forward alone: at the end of 2026 it must equal the second-year share of the 2026 accrual plus the unpaid part of the seal campaign, $602,000. The asset retirement obligation falls by $271,967 in 2026 because a cheaper removal method cut the expected cash flows; the cut is discounted at the rate of the layer it relates to, not at a current rate, and the liability closes at $2,067,800.

How to use it on your own case

  1. Enter each claim, counsel's assessment and range, and any settlement offer, in the yellow cells of Inputs.
  2. Enter pump sales, the warranty rate, the claims pattern and the actual claims, and any defect campaign.
  3. Enter the retirement scenarios in today's prices, the inflation rate, the risk premium and each layer's credit-adjusted risk-free rate.
  4. Read Litigation, Warranty and ARO: the matrix gives the decision per matter, the roll-forward and its proof must agree, and each layer keeps its own rate.
  5. Check that the Checks sheet reads ALL PASS before you take any figure into a note or a memo.

Questions people ask about it

When a range has no best estimate, what is accrued?

Under US GAAP, the minimum of the range, with the reasonably possible excess disclosed. A substantive settlement offer can set that low end. In the case the accrual at the end of 2025 is $1,200,000 and the excess disclosed is $1,800,000.

How do I know the warranty reserve is right?

Roll it forward, then rebuild it a second way: the share of each year's accrual still inside its cover period plus any campaign still unpaid. The two must agree. In the case both give $602,000 at the end of 2026.

Does the model decide whether a loss is probable?

No. It keeps the figures once the judgment is made. The assessment rests on the facts, counsel's view and a documented memo, which the book covers in its own chapters.

Go further

The free workbooks of The Contingencies and Provisions Handbook go further: the full companion set, blank models and the worked cases of the book.

See the book and its free files →

Other free templates

This template goes with The Contingencies and Provisions Handbook. See the book.