Free Excel templates

A free yield maintenance and defeasance calculator in Excel

The two ways out of a fixed-rate commercial mortgage before maturity, priced side by side: yield maintenance with its minimum and the two common shortcuts, a grid by treasury yield and years left, and the cost of a defeasance portfolio with fees.

Yield maintenance is the present value, at the treasury yield, of the monthly gap between the note rate and that yield over the remaining term, subject to a minimum. On an illustrative $40.0M interest-only loan at 6.25 per cent with six years left and a 4.00 per cent treasury, the file returns a premium of $4,793,808, 11.98 per cent of the balance; the undiscounted shortcut says $5,400,000 and discounting at the note rate $4,493,398. On the defeasance sheet, an illustrative $50.0M amortising loan defeased with 72 months left and treasuries at 3.90 per cent needs a portfolio of 50,213,873 against a balance of 46,944,121: 3,444,752 with fees.

Free online calculator, no sign-up: yield maintenance calculator.

Free online calculator, no sign-up: defeasance calculator.

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.

Yield_Maintenance_and_Defeasance_Calculator.xlsx (14 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 file is one worked case from How to Read a Real Estate Loan Agreement. The book explains every assumption, every sheet and the decision the numbers support, 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: 36 paperbacks by Julian R. Sterling on how to read the documents, printed on demand and shipped worldwide. Browse the bookshop →

The formulas

Yield maintenance = MAX(balance × (note − treasury) / 12 × [1 − (1 + treasury/12)−n] / (treasury/12), minimum)

Defeasance cost = PV at the treasury yield of every remaining payment and the balloon + fees − balance released

What is in the file

4 sheets. Blue type on a pale blue fill is an input; black type is a formula.

Read Me
What the file does, the two worked cases, what to read in your loan agreement first, and its limits.
Yield Maintenance
Balance, note rate, treasury yield, an optional spread on the discount rate, months left and the minimum in blue; the monthly differential, the annuity factor, the premium and the payoff; the two shortcuts for comparison; and a grid of the premium by treasury yield and years remaining.
Defeasance
Original loan, coupon, amortisation, term, months elapsed, treasury yield and fees in blue; the payment, the balance and the balloon, the monthly rate equivalent to the treasury yield, the portfolio cost, the premium and the cheque that frees the property.
Checks
Four identities that hold for any inputs (yield maintenance equals the PV of the remaining payments at the treasury yield less the balance, the loan’s remaining payments at its own rate equal its balance), then the worked cases. The file ends ALL OK.

What the worked case returns

MeasureValue
Yield maintenance premium$4,793,808
Premium, % of balance11.98%
Undiscounted shortcut$5,400,000
Discounted at the note rate$4,493,398
Premium with 8 years left at a 3.00% treasury23.09%
Defeasance: monthly payment276,102
Defeasance: portfolio cost50,213,873
Defeasance: cost with fees3,444,752 (7.34%)

Both premiums are a rate trade. Yield maintenance is most expensive when rates have fallen: with eight years left and a 3.00 per cent treasury it reaches 23.09% of the balance, and at a 6.00 per cent treasury it sinks to the 1 per cent minimum. Defeasance moves the same way, because it replaces a loan paying 5.25 per cent with treasuries paying less. A borrower who can pick the date of a sale or refinancing, within its timetable, is picking a treasury yield. Read the definition clause first: which treasury, whether a spread is added to the discount rate, and what the minimum is.

How to use it on your own figures

  1. Read the prepayment clause of your loan agreement: the treasury it names, any spread on the discount rate, the minimum premium and any open period near maturity.
  2. On Yield Maintenance, type the balance, note rate, months left and treasury yield over the blue cells. Use the yield of the treasury the clause names, on the date it names.
  3. On Defeasance, type the original loan, coupon, amortisation, term, months elapsed, the treasury yield and the fees quoted by your consultant.
  4. Compare the two only if the loan lets you choose; most conduit loans allow one or the other.
  5. Keep the Checks sheet. It ends ALL OK on any inputs except the worked-case rows.

Questions people ask about it

How is yield maintenance calculated?

As the present value, at the treasury yield, of the monthly interest differential between the note rate and the treasury over the remaining term, with a minimum, often 1 per cent of the balance. On $40.0M at 6.25 per cent against 4.00 per cent with 72 months left: $75,000 a month for 72 months, discounted, is $4,793,808.

What is the difference between yield maintenance and defeasance?

With yield maintenance the loan is repaid and the borrower pays a premium. With defeasance the loan stays in place: the property is released and replaced as collateral by treasuries that pay every remaining payment, and a successor borrower takes over the loan. At a flat treasury curve the two costs converge.

Why does yield maintenance go up when rates fall?

Because the lender loses the gap between the note rate and what it can earn by reinvesting in treasuries. A lower treasury yield widens the gap and lowers the discount rate, so the premium rises on both counts.

Is this calculator really free?

Yes. It is a companion file to a book. You enter an email address, the download opens at once, and you can leave the reader list in one click. It has no macros.

Where do the figures come from?

From two illustrative loans in the book. The treasury yields are assumptions, not market quotes, the loans are fictional, and nothing here is legal or investment advice.

The rest of the files

This template sits alongside the companion files for How to Read a Real Estate Loan Agreement: the workbooks behind every chapter, the worked cases and the checklists. Free, like this one.

Open the companion files →

Worked questions from the same book

Short articles that each answer one question with the arithmetic shown, on the same ground as this model.

Other free templates

This template comes from How to Read a Real Estate Loan Agreement. 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.