Free Excel templates

A free CMBS deal model in Excel, from the loan tape to the last dollar

A twenty-loan tape, a fourteen-class capital structure with credit support computed from the stack, loan-by-loan liquidations, a ten-year cash reconciliation, five stress scenarios and surveillance: eight sheets, no macros.

The file builds Meridian 2026-C1, a fictional $1.0 billion conduit CMBS. Twenty loans carry a weighted average coupon of 6.34%, a loan-to-value of 60% and a coverage ratio of 1.59 times; the top five are 37.6% of the pool and office 22.6%. The capital structure has fourteen classes with 30% credit support under the senior block. Loans that fail run through a liquidation bridge with fees, legal costs and appraisal reductions, and five stress scenarios push losses through the classes. A severity of 40.8% on the largest loan alone would wipe out class H.

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.

CMBS_Deal_Model.xlsx (59 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 CMBS and CRE CLOs. 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: 19 paperbacks by Julian R. Sterling on markets, funds and compliance, printed on demand and shipped worldwide. Browse the bookshop →

What is in the file

Eight sheets. Blue cells are inputs; a yellow fill marks an assumption that carries the answer.

Read me
What the file covers and the dating convention for balances.
Loan tape
Twenty loans: property type, balance, coupon, LTV, coverage, term, and pool statistics.
Capital structure
Fourteen classes, ratings, sizes, the senior block and credit support computed from the stack.
Liquidations
The loans that fail, loan by loan: balance at transfer, special servicing and liquidation fees, legal costs, appraisal reduction and loss.
Reconciliation
Ten years in which every dollar in leaves by one door.
Stress, Surveillance and Controls
Five scenarios class by class, surveillance metrics, and the controls against the book.

What the worked case returns

MeasureValue
Pool balance$1.0bn, 20 loans
Weighted average coupon6.34%
Pool loan-to-value59.9%
Weighted average coverage ratio1.59x
Top five loans37.6%
Office share22.6%
Implied capitalization rate6.41%
Credit support, senior block30%
Severity on the largest loan that wipes class H40.8%

Concentration is the risk the averages hide. The largest loan is 9.2% of the pool, so a single bad outcome on it, at a 40.8% severity, is enough to wipe out a whole subordinate class.

How to use it on your own deal

  1. Enter up to twenty loans on Loan tape.
  2. Set the class sizes on Capital structure and read the credit support.
  3. Mark the loans that fail and their timing on Liquidations.
  4. Run the five stresses and read losses class by class.

Questions people ask about it

Is this template really free?

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

Does it use macros or circular references?

No macros and no circular reference.

What is credit support in a CMBS?

The share of the pool that sits below a class and absorbs losses before it does. The senior block here has 30%: the pool can lose 30% before a senior dollar is written down.

Can I use it on a real deal?

You can reuse the structure. The deal, the loans and every figure are fictional, and nothing here is investment advice.

The rest of the files

This template is one of the companion workbooks for CMBS and CRE CLOs: the others take one loss through the severity bridge and work through appraisal reductions and control. Free, like this one.

Open the companion files →

Other free templates

This template comes from CMBS and CRE CLOs. 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.