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.

Julian R. Sterling · from Sale and Leaseback

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.

Download the template

One Excel file, no macros, nothing locked. No account, no email address.

Sale_and_Leaseback_Model.xlsx189 KB

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. There is no sign-up, no email address and no paid version.

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.

Open the companion files →

Other free templates

LBO modelSources and uses to sponsor IRR, twelve sheets, every figure checked.
Real estate property modelRent roll, cash flow, debt and returns for one asset, empty and ready to fill.
Residual land valuationDevelopment appraisal with the four downside tests and peak debt.
DCF with terminal valuePerpetuity growth and exit multiple side by side, with the TV share of value.
Distribution waterfall modelReturn of capital, pref, catch-up and carry, three catch-up readings, European vs deal-by-deal.
Loan sizing: LTV, LTC, DSCR, debt yieldThe four lender tests at once, the binding one, and a rate shock.
Direct lending credit modelUnitranche underwriting: coverage, leverage, sweep, base, downside and stress.
Data center financial modelTurnkey, powered shell and powered land on one campus, levered returns and delay.
13-week cash flow modelLiquidity on paper against usable liquidity, a springing covenant and six levers.
Small business acquisition model (SBA loan)SBA 7(a) loan sized in closed form, lender coverage against cash coverage, exit returns.
3-statement financial modelFive-year forecast feeding a DCF and an LBO on one case, with a blank build.
Project finance modelSolar and storage project, debt sculpted to a 1.35x DSCR, no circularity.
REIT valuation modelFFO, AFFO, NAV and the implied cap rate for one REIT, plus an empty template.
Self-storage financial modelCohort rent roll, break-even occupancy, ten-year hold and the bid for one store.
Tax lien certificate yield calculatorBid, minimum charge and redemption month turned into a real annual yield.