A Practitioner’s Guide to Debt, Credit Structures and What a Property Is Worth to a Lender
Julian R. Sterling
These are the companion files of Real Estate Finance by Julian R. Sterling.The book on Amazon →
The four Excel workbooks that go with the book, corrected where they were wrong, and two new sheets
for the chapters no workbook computed: the stack of Chapter 5 and the sale and leaseback of Chapters 15
and 16. Every figure is a live formula, and each workbook ends with a Checks sheet:
206 controls in all, every one green; where the book was wrong, the check gives the
corrected figure and says what the first edition printed. Around them: the same models with the inputs
emptied, twelve working documents to print, forty questions that mark themselves, and three cases the
book names and never takes to a number.
Free to download. No sign-up, no email address, nothing to fill in.
Eleven workbooks, the printable documents and the read-me. Each group below can also be
downloaded on its own. Last revised 23 September 2026.
Get an email if these files are corrected
Optional. When a workbook on this page is corrected or a new worked case goes up, readers on the list get one email, plus a short note when a new book comes out. The downloads do not need it.
These carry the book’s own figures, so that every number it prints can be traced to a live
formula.
Download these four108 KB
Chapters 1 to 3, 5, 11, 15 and 16
Alder Point — the four values
The bridge of Chapter 2, with each of the three adjustments on its own switch. Turn the
over-rent strip off and watch 82,204,444 climb; turn the yield widening off and watch it climb
further. The sheet also prices each adjustment on its own, so you can see which part of the
37,800,000 gap you are actually arguing about — and, in practice, most disagreements
collapse onto one line.
The second half turns the bridge into the option of Chapter 3. Recovery is market value
times a compound factor; the loan is whole above a strike of 105,109,489,
twelve per cent below today's value rather than forty. The payoff table runs from 140,000,000
down to 80,000,000, and a separate block works the bridge backwards to the income fall at which
the lender begins to lose: 12.4 per cent from market rent, 19.6 from passing.
That distance is 69 per cent shorter than the 40 per cent a 60 per cent loan-to-value
suggests, not the 30 to 40 the chapter’s rule of thumb gives.
Two sheets are new. The Stack puts the bridge under Chapter 5’s junior loan: 85 per cent
recovery at 82,200,000, 17 at 74,000,000. Sale and Leaseback builds Chapters 15 and 16, which no
workbook computed. The first edition set a price struck on net income against a market-rent value
struck on gross rent; on one basis the over-price is the over-rent capitalised, 9,904,762
rather than 7,619,048, and the fair price is 114,225,435 (not 116,511,149). The over-rent
repays 63 per cent of the over-price undiscounted, and the illusion costs 27 basis points of
yield, not 16.
Loan-to-value, debt service cover and debt yield on one sheet, each solved for the movement in
income or value that breaks it, and ranked. The binding test is named by formula, not by
opinion, and the sheet writes the one line a credit paper should carry: binding test:
loan-to-value, breaching on a 7.7 per cent fall; debt yield second at 8.6 per cent; debt
service cover is not a constraint.
Then the shocks: 200 basis points on the rate takes cover from 1.43x to 1.08x
on an asset whose income has not moved by a euro; twenty-five basis points of yield is worth
4.5 per cent of value outward and 5.0 inward (the book prints the first-order 4.8), which is most of the headroom in a loan-to-value covenant. The cure
block prices the paydown, and the last sheet runs the same engine on three loans side by side
so you can watch the ranking flip when the asset yields more and the debt costs more.
Thirty-eight loans, one row each, and every summary statistic computed from the rows rather
than typed above them — which is the point of the chapter. Edit a balance and the
weighted averages, the concentration table and the maturity profile all move.
The structure sheet carries the seven tranches with their attachment and detachment points and
allocates a loss you drive with a default rate and a severity. The three cases of
Chapter 9 are computed alongside: Alder Point defaulting alone costs the pool
nothing, because recovery exceeds the loan; a recovery of 60,000,000 costs
0.97 per cent of the pool and takes 16 per cent of class G and nothing else; five simultaneous
defaults at 25 per cent severity wipe class G out and take 18.7 per cent of
class F, and still do not reach the BB tranche.
The last sheet is the mechanism that does the most damage and is least understood: the
appraisal reduction. A valuation of 88,000,000 produces none. At 70,000,000 it produces
11,200,000, the servicer stops advancing interest, and control moves up the
stack — all before a single euro of loss is realised. The sheet names the controlling
class for whatever loss you have set, and now once the reduction is applied as well.
Revised: loan R04 was also called Alder Point; the loans needed to reach each tranche read 39 for
every class (class D needs three total losses, class F one); and a total loss on the largest loan
stops in class F, taking 19 per cent of it, rather than consuming most of class E. On the
tape the book prints, Alder Point’s lease runs three years past the loan, not one.
Two reported gross values and one coefficient, and the smoothing comes out: 486,000,000
reported becomes 468,000,000 true, an overstatement of 3.7 per cent that the
leverage turns into 9.2 per cent of net asset value, because the debt does not
move.
Which means the ten per cent discount on offer is 0.85 per cent. The
sensitivity table runs the coefficient from 0.40 to 0.80 — the honest presentation, since
the coefficient is the one judgement in the calculation — and the workbook also solves
for the coefficient at which the discount vanishes entirely. Add the bottom-up correction for
Alder Point's over-rent, which is a different kind of adjustment and additive to this one, and
the buyer offered a ten per cent discount is paying a 2.5 per cent premium (the
first edition says 1.1: the over-rent correction was a typed constant built on the wrong over-price;
it is now computed, 5,774,565). The discount turns into a premium at a coefficient of 0.62,
barely above the central 0.60, not across most of the range.
The four workbooks with every input emptied. The checks sheets count what is still missing and test what holds for any inputs.
Chapters 1 to 18
Your bridge, your covenants, your tape, your fund
The recovery bridge and the option, the three sizing tests, the loan tape and its tranches, and the unsmoothing of a net asset value, with the same formulas and every blue cell emptied. A sheet fills in as soon as the inputs it uses are typed. The switches of the bridge, the market values of the option table and the coefficient steps stay.
The book’s inputs are listed in a text file inside the archive. Type them back in and the book’s figures come back with them: 82,204,444 of recovery, a binding loan-to-value on a 7.7 per cent fall, 0.85 per cent of real discount.
Twelve pages to print and use. A4, with margins wide enough for US Letter.
Chapters 1 to 18
Twelve working documents
Which question a number answers. The recovery bridge with the cost of each step. The loan as a written put, with the conditions an extension should carry. Sizing and the one line for the credit paper. The stack and the five questions of the intercreditor. A loan agreement in ninety minutes. A loan tape in half a day. Your tranche: attachment, thickness, control and the four monthly reports. Pools and the investor’s side of a risk transfer. Buying a sale and leaseback in seven lines. What a net asset value is made of, and the data room. Unsmoothing, and the bid.
A workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of the book
Forty questions on the book
Seven on the four values, the bridge and the option, seven on sizing, the stack and the document, seven on the tape, the tranches, the waterfall and the rating, six on pools and risk transfer, seven on the sale and leaseback, the net asset value and unsmoothing, and six on the three cases below. Numbers are marked to a tolerance, multiple choice to the letter, and the score sheet counts your errors part by part: that is your revision list.
Nothing is hidden. The marking sheet holds every answer, every tolerance, and the reason for each answer.
New material, not a restatement. Three situations the chapters name and never take to a number. Each case is one workbook: the note is the first sheet, the model is the rest, and a checks sheet closes it. Type your own figures in and every finding recomputes.
Chapter 3 · Case one
The extension is a new put
The chapter says a lender who extends on unchanged terms has made a gift and should know its size. Priced with Black-Scholes on the recovery value, at a 12 per cent volatility (ADDED): at an unchanged 120,000,000 the twelve-month put is worth 733,671, 102 basis points, and the 210 of spread pays for it. It stops paying below 114,009,572 of market value, or above a 15.9 per cent volatility. At 100,000,000 the gift is 4,210,735, and the paydown that pays for it is 8,847,408.
Chapter 5 · Case two
What “senior debt” includes
Default interest, protective advances and hedging breakage (ADDED, 6.4 per cent of the senior, inside the chapter’s 5 to 8) take the junior’s attachment to 63.8 per cent: the book’s 64. What the chapter does not say is the recovery: 5,600,000 at 82,200,000 instead of 10,200,000, nothing at 74,000,000. Additions of 5.83 per cent of the senior already halve it, and the junior is wiped out on a 6.8 per cent fall in market value.
Chapter 14 · Case three
The hundred million the bank releases
The chapter’s risk transfer releases 100,000,000 of capital and never says how. On the book’s own figures that assumes a 46.1 per cent risk weight on what the bank keeps; the trade stops paying above 59.9. On the other side, the investor’s coupons over four years (ADDED) are consumed only at a portfolio loss of 3.52 per cent, seven times the expected loss, and the chapter’s 0 to 0.6 per cent tranche is 83 per cent expected loss.
an input cell; everything else on the sheet is a formula
Black text
a formula — do not overtype these
Checks sheet
the printed figure beside the computed one, with a PASS or a FAIL; where the book was wrong, the corrected figure and what the first edition printed
ADDED
a figure the book does not give, used in the cases and marked wherever it appears
Why the checks matter more than the models
A workbook that agrees with a book proves nothing on its own — the author wrote both. What
the Checks sheets do is different: they force the model to reproduce a number that was printed
before the model existed, from a formula rather than from the number itself. Building them for
this book found three figures in the manuscript that did not survive the test, and all three were
corrected before publication rather than quietly adopted. Extending them to the chapters no
workbook computed found more, listed under What changed below: the largest is the
sale-and-leaseback over-price of Chapter 15, which compared a net figure with a gross one.
Where the book states a convention that the arithmetic can be read two ways — the rounded
0.685 recovery factor of Chapter 3 against the exact 0.68504 of the bridge — both are
shown, and the workbook says which one the printed table used.
Go beyond the companion files
Real Estate Finance develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers; the PDF
prints on A4 or US Letter. They use no macros and no add-ins, so nothing needs to be enabled or trusted. If your spreadsheet asks
to update links on opening, decline — there are none.
What changed, and when
23 Sep 2026
The four workbooks corrected; 206 checks. Chapters 15 and 16 built for the first time: the over-price is 9,904,762, not 7,619,048 (a net price against a gross market-rent value), the fair price 114,225,435, the overpayment 5,774,565; the fully adjusted premium on the fund stake is 2.5 per cent, not 1.1. The loan tape’s tranche reach, a duplicate loan name and the lease-to-maturity gap (three years, not one) corrected; the widest gap between the property values is 37,800,000. Added: a blank set, twelve working documents, forty questions that mark themselves, and three cases.
How much is an assumable commercial mortgage worth?PV of the interest saved against the buyer's new financing, less fees: a $30.0M loan at 4.00% is worth 1,445,222 on a $60.0M deal, not the 3,000,000 rate gap.
Closing the DealTwo defensible bridges 20.70 million apart, a peg worth 7.00 million, and the six choices that remove 6.60 of a 12.00 earn-out.
CMBS and CRE CLOsWhere the loss actually lands, from appraisal reduction to realised severity, and what the B-piece is really being paid for.
How to Read a Commercial LeaseThe three refinements chapter 19 names and never performs, and the renewal rate below which the mark-to-market is worth nothing.
How to Read a Credit AgreementWhere the default actually comes from, the cure that costs 5.5 times the other, and the capacity nobody adds up.
How to Read a Real Estate Loan AgreementThe cure ratio in closed form, the four-point window in which the cheap cure works, and the cure sized to the wrong threshold.
Office Real EstateA six per cent yield that returns 3.2 per cent once the re-letting cycle is paid for, and the headline-to-net-effective rent arithmetic.
Private Equity Real EstateBoth worked waterfalls to the dollar, the two capital stacks, and the arithmetic of the promote made changeable.
Private Markets PerformanceThirty-one of the thirty-three figures chapter 19 publishes reproduce exactly — and the two that do not are named rather than quietly adopted.
Raising a Real Estate FundThe chapter 17 funnel run on a calendar — when the first close actually lands, and why more travel does not help.
Real Estate Financial ModelingProperty, development and fund models built line by line, and the modelling test worked end to end.
Real Estate Fund ManagementThe waterfall of 6.11, the build-to-core of 8.7 and the proceeds gap, reproduced as live formulas rather than asserted.
REIT Analysis and ValuationFFO of 532.0, AFFO of 381.0, net asset value and dividend safety — every figure a formula you can change.
Retail Real EstateThe occupancy cost of every unit in a centre, the sixteen per cent of the rent roll no tenant can sustain, and the right-size-convert-or-hold decision priced.
Sale and LeasebackA €179.5 million transaction end to end, with rent cover measured on the entity that actually signs the lease.
Self-Storage Real EstateThe cohort engine behind a 590-unit store, and the rate increase on existing customers priced against the move-outs it causes.
The Fund Finance ProfessionalChapter 8 builds the reported-to-eligible NAV bridge; chapter 9 computes every ratio without it. Two points at every state — and what a subscription line does to the IRR.
The Growth Equity InvestorWhat a pro rata cheque really costs, and the band where defending your ownership loses money.
The Private Equity Secondaries InvestorThe floor and the ceiling are the same formula at two rates — so at equal rates the zone of agreement is exactly zero.
The Private Credit InvestorThe two coverage ratios are not measured on the same thing: the erosion is 47.7 per cent, not the 28.7 the headline implies.
The Private Equity Fund Controller PlaybookThe book defines IRR, DPI, RVPI and TVPI, tells you to update them at the exit, and prints not one value. Computed: a 1.833× deal inside a fund at 0.892 TVPI.
The Venture Capital AssociateWhat defending a position costs, and how many companies a reserve pool actually defends.
Financial Risk ManagementA fund inside every limit that cannot meet a redemption — and the number that decides it is the one with no currency attached.
Business ValuationThree advisers land 26.8 per cent apart on one company, and the whole gap turns out to be 1.96 points of perpetual growth.
Quantitative FinanceThree models agree to a quarter of one per cent about a number that one unobservable input moves a hundred and three times as much.
Asset ManagementFour people quote four returns for one mandate, all correct and 2.7017 points apart — forty-eight times the manager’s net skill.
Alternative InvestmentsA manager reports 13.29 per cent and the endowment earns 6.26 — both correct, and only a third of the advertised advantage arrives.
Credit AnalysisFour defensible EBITDAs on one borrower give leverage from 3.19x to 6.47x — and the add-back argument is fifty times the covenant headroom.
Venture CapitalOne company out of twenty-eight returns 56.7 per cent of the fund, and half the capital goes in after the decision — at half the return.
Machine Learning for FinanceFive people quote the accuracy of one credit model, all five are right, and the number that decides how much money it makes is none of them.
Commercial Real Estate InvestingThe equity earned 8.6647 per cent and the investor received exactly 8.0000 — the preferred return, and nothing above it.
Mergers and AcquisitionsThe board paper says the deal creates 13,436,667 of value. The arithmetic says it destroys 17,530,855. Nobody is lying.
DerivativesThe treasury report says the hedge cost 1,233,698. That is the interest differential, not a cost.
Treasury ManagementFive cash balances for one company, all correct and 145,600,000 apart — and the revolver that is two-thirds of the liquidity leaves at a revenue fall of 8.4127 per cent.
Financial Planning and AnalysisRevenue 3.0190 per cent above budget and operating profit 16.3209 per cent below it, in the same quarter, with every figure correctly stated.
Energy TradingA position report that is 91.7031 per cent hedged and correctly computed, on a book that is short 2,542,000 MWh — and a margin call of 198,400,000 the next morning.
Construction Cost ControlA contract sum of 26,301,102 became a final account of 29,153,363 on the building that was drawn — and 85.8 per cent of what was lost was knowable on the day it was signed.
Trade FinanceSix routes to payment on one 4,200,000 export order cost between 178,040 and 223,268, a spread worth 14.8 per cent of the margin, and a day of buyer credit costs 1,031.76.
Cost AccountingOne factory costed twice on the same 13,440,000 of overhead, and 4,053,091 moves between four product families.
Pricing StrategyA list price of 148.00, a pocket price of 112.51, and the nine deductions in between — with what one point of price is actually worth.
Contract ManagementA bid 5.50 a unit cheaper, signed on paper worth 9.81 a unit more — and 94.68 per cent of it was knowable on the day of signature.
Capital MarketsFour defensible readings of one bond, the cost of an issue under each convention, and the placement discount computed at a constant discount.
Financial RegulationThree ratios pass and the fourth is in breach by 1,956.0 million: the output floor, the liquidity buffer after encumbrance, and the loan split.
Bank ManagementFour defensible readings of one balance sheet, the deposit beta measured two ways, and the liquidity that lasts 3.9 days.
The Real Estate WorkoutFour ways out of one broken loan, discounted to today, and the 58.7 cents at which a payoff beats enforcement.
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 workbook stays free either way.