A free sale and leaseback model template in Excel, with a complete worked portfolio
Nine buildings, two rent lines, a five-way decomposition of the price, rent cover on the entity that actually signs the lease, and a ladder of risk premia solved by formula: fifteen linked sheets, one fictional portfolio, no macros.
The file models one sale and leaseback from the tape to the price. A tenant's nine buildings, 169,000 square metres in all, pass a rent of €12,113,500 a year against a market rent of €9,606,000: an over-rent of 26.1%. At the 6.75% deal yield that rent prices the portfolio at €179,459,259. Of that price, 62.3% is the buildings and the costs a buyer avoids; the rest, €65,783,020, is exposure to one company rather than to property. Rent cover on the group that presents the numbers is 4.79 times; on the entity that actually signs the lease, 1.61 times. Every one of those figures is a formula you can move.
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.
Sale_and_Leaseback_Model.xlsx (189 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.
Need the full project or fund? The Pro Editions go further: Development (60 months, S-curves, senior and mezzanine loans, JV waterfall), $99 → · Fund (up to eight assets, European or American waterfall), $129 →
The book behind this model
This file is one worked case from Sale and Leaseback. 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).
Real Estate Fund Model, Pro Edition: a closed-end fund with up to eight assets, fees, European or American waterfall, 30 built-in checks, a blank copy and a 19-page guide. $129 → · See what is inside
REPE Interview Prep Pack: 18 real estate PE interview cases solved step by step, with three Excel models. $129 →
Real Estate Acquisition and Development Model, Pro Edition: monthly ground-up model over 60 months, S-curves, senior and mezzanine loans, bulk or unit sale, JV waterfall, 33 checks, a 14-page guide. $99 → · See what is inside
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 10 paperbacks by Julian R. Sterling on tax and incentives, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Fifteen sheets, in the order the calculation runs. Inputs are blue type on a pale blue fill and sit on two sheets; a yellow fill marks the carrying assumption to argue about first; everything else is a formula.
Read me
What the file computes, the inputs to argue about first, and the conventions it follows.
2. The tape
Nine buildings: area, market rent, passing rent, the valuer's vacant possession value, criticality, void months, capex and depreciation, with the market rent, passing rent and over-rent totalled.
3. Assumptions
Every scalar input in one place: the deal yield, the re-letting yield, the risk-free rate, the two risk premia, the lease term, indexation, group and guarantor profit, group debt, the bond spread and recovery, and the discount rates.
4. Rent cover
The same rent divided by two different profit figures: the group as presented, and the entity that actually signs the lease, plus the price each cover level on the guarantor would support.
5. Decomposition
The price split five ways per building (bricks, costs avoided, signature premium, capitalised irrecoverable cost, over-rent), the property and exposure-to-one-company subtotals, and the recovery rate of each asset ranked against its criticality.
6. The ladder
Four rungs of risk premium, from the bond's own risk-free measure up to the full property premium, each with its value and its break-even default rate, plus the risk premium the price actually leaves against the premium the base case requires.
7. Cash flow
The hazard-rate cash flow behind every value on the ladder.
8. Indexation
The cap and the floor on the rent increase, and what the cap costs at different inflation assumptions.
9. Solvers
Every rate the book backs out, found by bisection in the open: the break-even default at each rung, the risk premium the price leaves, and the single rate that reproduces the base-case value.
10. Sensitivities and 11. Inversions
How the price moves with the deal yield and the risk premia, and the four values (a rent, a yield, an inflation rate, a recovery rate) solved backward from a target price.
12. Master lease
What a tenant could keep and cherry-pick if the lease were signed building by building instead of as one master lease.
13. Credit, lender, seller
The same cash flows read by a credit desk, a lender sizing a loan and the seller's own IRR, plus the IFRS 16 entries the transaction creates.
14. Scenario engine
The whole model re-run under named scenarios rather than one base case.
15. Checks
Every figure the book prints set against the cell that computes it, with a verdict on each line: 191 checks, all OK.
What the worked portfolio returns
Measure
Value
Total area, nine buildings
169,000 m²
Market rent, total
€9,606,000
Passing rent, total
€12,113,500
Over-rent, per cent above market
26.1%
Asking price at the 6.75% deal yield
€179,459,259
Property share of the price (bricks and costs avoided)
62.3%
Exposure to one company
€65,783,020 (36.7%)
Rent cover, group as presented
4.79x
Rent cover, lease guarantor
1.61x
Value at the bond-implied default rate
€152,990,677
Read the last two rows against the price. Discounted both legs at the risk premium the tenant's own bond actually trades at, the portfolio is worth €152,990,677, some €26.5 million below the €179.5 million asking price.
The gap is a risk premium of 36 basis points the price leaves once credit is paid for at the bond's own rate, against the 241 basis points the arithmetic needs for illiquidity, management cost and recovery uncertainty.
That is 14.7% of the price, and it is the gap an investment committee should ask to see closed before agreeing the rent cover on sheet 4 is the only test that matters; the ladder on sheet 6 shows exactly which assumption closes it.
How to use it on your own deal
Open sheet 2, The tape, and overwrite the blue cells for your own buildings: area, market rent and the valuer's vacant possession value first.
Read sheet 4, Rent cover, before anything else. If the entity that signs the lease is not the group that reports the profit, cover at the group level tells you nothing about the risk you actually carry.
Look at sheet 5, Decomposition. The share of the price that is exposure to one company, not property, is the number a yield alone never discloses.
Start the ladder on sheet 6 at both risk premia set to zero: that is the only rung strictly comparable with the tenant's own bond. Add illiquidity, then the property premium, one at a time.
Keep sheet 15, Checks: once you change the tape it stops matching this worked portfolio, which is expected. Use its structure for your own tie-outs.
Questions people ask about it
Is this sale and leaseback model 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: iterative calculation is switched off in the file, and every rate the model backs out (the break-even default, the risk premium the price leaves) is found by bisection over a fixed number of steps, not by a cell that refers back to itself. It recalculates in Excel, LibreOffice or Google Sheets.
Why does rent cover fall from 4.79x to 1.61x?
The rent is covered 4.79 times by the profit of the group as presented, but the lease is only ever signed, and only ever enforceable against, one entity: the guarantor. Its own profit before rent covers the same rent just 1.61 times. Rent cover read at the group level, when the group is not who signs the lease, overstates the safety of the rent by three times on this portfolio.
Can I use it for a real transaction?
You can reuse the structure. The portfolio, the tenant and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for Sale and Leaseback: the full set adds
the covenant monitor, the lease abstract, the bid sheet, a blank set for your own transaction, thirteen working documents,
forty self-marking questions and three named cases the book never takes to a number. 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.