Scenarios, probabilities and the effective rate: why the borrower's plan alone gives a provision of zero, and enforcement alone 33.18.
The provision on a defaulted loan is the carrying amount less the probability-weighted present value of what each recovery scenario will pay, with each scenario's shortfall floored at zero. On an illustrative €82.5 million office loan with a 55 per cent chance that an extension works and a 45 per cent chance it ends in enforcement, the provision is €14.29 million, 17.3 per cent of the loan, discounted at the loan's 6.1 per cent effective rate. Provide only for the plan the borrower is promising and the figure is zero.
Worked in full in The Real Estate Workout by Julian R. Sterling, with every figure reproduced in a free workbook.See the book on Amazon →
A credit-impaired loan is carried at the present value of the cash it is expected to return. Under IFRS 9 that means several scenarios, each with its own cash flows and timing, weighted by probability, and discounted at the loan's original effective interest rate rather than at the rate a distressed buyer would use. The provision, the loss allowance, is the gap between that figure and the gross carrying amount, accrued interest included. The work is in the scenarios: the formula is short.
In IFRS 9 terms this is a stage 3 asset: lifetime expected credit losses, measured as the present value of the cash shortfalls against the contract, and interest income recognised on the net rather than the gross carrying amount. Under US GAAP CECL a discounted cash flow allowance is also discounted at the effective rate, but once foreclosure is probable, or when the lender elects the practical expedient for a collateral-dependent loan, the allowance is set from the fair value of the collateral, less costs to sell where repayment depends on a sale, and the probability-weighted scenarios below give way to a single valuation.
Provision = Σ probabilitys × max(0, carrying amount − PV of scenario s cash flows at the effective rate)
| Input | Value |
|---|---|
| Loan outstanding, accrued interest included | 82.50 |
| Effective interest rate (margin plus base rate) | 6.1% |
| Net operating income today | 5.15 |
| Market yield today | 6.65% |
| Probability the three-year extension succeeds | 55% |
| Extension: fee at signing, then net cash in years 1 to 3 | 0.825; 3.65; 5.45; 5.95 |
| Extension: balance repaid at exit in year 3 | 86.74 |
| Enforcement: forced-sale discount, costs, holding cost a year | 18%; 5.5%; 2.1% |
| Enforcement: years from the decision to cash | 2.25 |
Enforcement proceeds: value 5.15 ÷ 6.65% = 77.44; less 18% = 63.50; less 5.5% costs and 2.1% a year of holding cost for 2.25 years = 56.35 net.
Scenario A, the extension works (55%): 0.825 today, 3.65, 5.45 and 5.95 + 86.74 in years 1 to 3. PV at 6.1% = 86.71, above the 82.50 owed, so the shortfall is 0.00.
Scenario B, it fails after a year (45%): 0.825 today, 3.65 in year 1, then enforcement completing in year 3.25 for 56.35. PV at 6.1% = 50.75; shortfall 82.50 − 50.75 = 31.75.
Provision: 55% × 0.00 + 45% × 31.75 = 14.29. Net carrying amount 68.21.
In Excel, per scenario: =MAX(0,Loan-NPV(EIR,CF1:CFn)-CF0) with yearly cash flows (use XNPV for the 3.25-year enforcement), then =SUMPRODUCT(Prob,Shortfall).
The allowance is €14.29 million, all of it from the failure scenario. The success scenario contributes nothing, and it cannot contribute a negative number: the extension's higher margin and fee make its present value at 6.1 per cent 4.21 above the loan, but a provision measures shortfalls, and the upside of one scenario does not offset the loss in another. Netting it would cut the provision to 11.97, which is the first error to look for in a committee paper.
Two benchmarks frame the figure. If the lender decided to enforce today, the single scenario would give a provision of 33.18 at the effective rate. If the lender believed the borrower's plan outright, it would give nothing. The probability-weighted 14.29 sits between them, and every 10 points of probability moves it by 3.17.
| Probability of success | At the 6.1% effective rate | Coverage | At a 9% decision rate | Coverage |
|---|---|---|---|---|
| 30% | 22.22 | 26.9% | 25.67 | 31.1% |
| 47% | 16.83 | 20.4% | 19.96 | 24.2% |
| 55% | 14.29 | 17.3% | 17.28 | 20.9% |
| 70% | 9.52 | 11.5% | 12.24 | 14.8% |
| 90% | 3.17 | 3.8% | 5.53 | 6.7% |
The probability is the input that carries the answer: from 30 to 90 per cent the provision falls from 22.22 to 3.17, against a building whose value has not changed. That is why auditors ask for the evidence behind it, signed heads of terms, a letting pipeline, a sponsor's equity actually in escrow, rather than the plan itself. The 47 per cent row is not arbitrary: it is the probability at which, on the book's figures, the extension and the discounted payoff on the table are worth the same to the lender.
The discount rate matters less but is often wrong. A workout committee comparing options rightly discounts at a rate for a distressed asset, 9 per cent here, and the companion workbook prices each option's provision that way: 2.17 for the extension, 36.08 for enforcement. The accounting allowance discounts at the loan's own effective rate, which gives 14.29 rather than 17.28 at the same 55 per cent. Neither is wrong; they answer different questions, and a paper that mixes them, a decision rate in the provision or the accounting rate in the option comparison, is wrong by 2.99 million.
Provisioning the plan. The borrower's business plan becomes the base case, the base case recovers in full, and the provision is nil until the plan visibly fails, by which time the enforcement scenario is a year shorter and the loss arrives in one quarter. The failure scenario belongs in the number from the day the loan defaults, weighted by an honest probability. The same discipline decides whether to extend or enforce in the first place, and the cost of each month of delay in the failure path is set out in what a month of delay costs in an enforcement.
Provision = probability-weighted shortfall of each scenario's present value below the carrying amount, at the effective rate, with no scenario allowed to offset another: €14.29 million on this €82.5 million loan at a 55 per cent chance of success, between 0 for the plan alone and 33.18 for enforcement alone. The four options with their provisions, the extension year by year and the enforcement bridge are in the free workbook for this case.
Under IFRS 9 the loan's original effective interest rate (the current one for a floating-rate loan), not a market or distressed rate. On an 82.5 million loan at 6.1 per cent with a 55 per cent chance of a successful extension, the provision is 14.29 million; at a 9 per cent rate a workout committee might use for decisions it would be 17.28 million, a difference of 2.99 million.
No. Each scenario contributes its shortfall below the carrying amount, floored at zero. An extension whose present value at 6.1 per cent is 86.71 against 82.50 owed contributes nothing, not a 4.21 gain. Netting the gain would cut the provision from 14.29 million to 11.97 million.
Roughly linearly. On this loan each 10 points of probability moves the provision by 3.17 million at the effective rate: 22.22 million at a 30 per cent chance of success, 14.29 million at 55 per cent and 3.17 million at 90 per cent, with no change in the building's value.
The four options and the provision per option are worked in chapters 3 to 8 of The Real Estate Workout. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.
Get the book on Amazon →Free companion files
Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada
Reading guide: real estate investing, finance and fund management → · All 681 articles →
If this book helped, or didn’t, a few lines on Amazon are worth more than they look: they are what the next reader goes on. Write a review. The workbook stays free either way.