Free Excel templates

A free loan workout model in Excel: enforce, extend, payoff or sell

One broken commercial real estate loan and four ways out of it, each priced as a present value on the same assumptions: enforcement, extension, discounted payoff and consensual sale, with the indifference price and the provision for each.

An office loan of EUR 82.5 million now sits at 107% loan to value. Enforcement recovers 56.3% in present value, a consensual sale 80.5%, an extension 97.4%. A discounted payoff beats enforcement at any price above 58.7% of the balance.

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 129 free models.

Loan_Workout_Options_Model.xlsx (23 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 Real Estate Workout, which explains the method behind it, chapter by chapter.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Want every model at once?

The Financial Model Library: all 130 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.

Assumptions
Asset and loan
The four options
The extension
Checks

What the worked case returns

MeasureValue
Value today (EUR m)77.4
Loan to value today106.5%
Interest cover today1.02x
Enforcement, present value (EUR m)46.42
Extension, present value (EUR m)80.33
Discounted payoff, present value (EUR m)56.89
Consensual sale, present value (EUR m)66.42
Indifference point, cents on the euro0.587
Break-even probability, extension against payoff47.3%
Provision under enforcement (EUR m)36.08

The extension ranks first only if it works. If it fails and the loan is enforced three years later, it is worth EUR 35.84 million, which is why it needs a success probability above 47% to beat the payoff on the table.

How to use it on your own case

  1. Enter the income, yields, loan terms and covenants on Assumptions.
  2. Set the enforcement timing, forced-sale discount and costs, then the extension terms and the payoff offer.
  3. Read the four options side by side on The four options, with their recovery and rank.
  4. Move the forced-sale discount and watch the indifference price: that sensitivity is the negotiation.

Questions people ask about it

What is a discounted payoff (DPO)?

The lender accepts less than the loan balance, paid soon, to close the loan. It is worth doing when that amount beats what enforcement would recover in present value.

How do you compare enforcement and an extension?

Discount the cash each one returns to the same date at the same rate, then compare present values and recovery on the loan.

What is the indifference point?

The payoff price, in cents on the euro, at which a discounted payoff returns exactly what enforcement would.

Is this template really free?

Yes. You enter an email address, the download opens at once, and you can leave the reader list in one click.

Go further

The free workbooks of The Real Estate Workout 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 Real Estate Workout. The book is on Amazon.

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 template stays free either way.