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.
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).
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
Measure
Value
Pool balance
$1.0bn, 20 loans
Weighted average coupon
6.34%
Pool loan-to-value
59.9%
Weighted average coverage ratio
1.59x
Top five loans
37.6%
Office share
22.6%
Implied capitalization rate
6.41%
Credit support, senior block
30%
Severity on the largest loan that wipes class H
40.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
Enter up to twenty loans on Loan tape.
Set the class sizes on Capital structure and read the credit support.
Mark the loans that fail and their timing on Liquidations.
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.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
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.