Companion files
A Practitioner’s Guide to Debt, Credit Structures and What a Property Is Worth to a Lender
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.
Reading the paperback or the Kindle edition? The same book is also sold as a DRM-free PDF: searchable, printable, yours on any device. $19.99, instant download.Buy the PDF edition → Free sample
Finished the book? Two or more PDF editions bought together are 15% off. Browse the PDF editions →
The working models behind the books, built out for live deals: the Pro models →
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.
Join the reader listRun by MailerLite, off this site. How your address is used.
These carry the book’s own figures, so that every number it prints can be traced to a live formula. Download these four104 KB
Chapters 1 to 3, 5, 11, 15 and 16
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.
Download the workbook28 KBChapter 4
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.
Download the workbook21 KBChapters 8, 9 and 10
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.
Download the workbook35 KBChapters 16, 17 and 18
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.
Download the workbook23 KBThe 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
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.
Download the blank set91 KBTwelve pages to print and use. A4, with margins wide enough for US Letter.
Chapters 1 to 18
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.
Download the documents32 KBA workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All parts of 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.
Download the questions15 KBNew 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 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
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 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.
Download the three cases51 KB| Blue text | a hardcoded input — you may edit these |
| Yellow fill | 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 |
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.
Real Estate Finance develops the complete framework behind these models, with the assumptions, trade-offs and worked examples tied together.
View the book on Amazon Read the related analysisAlso as an ebook on Apple Books · Google Play.
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.
| 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. |
| 8 Nov 2026 | Real Estate Finance realigned: the loan has three years to run, so the quarterly tests make twelve three-month options, not twenty; the margin is the reference rate plus 210 basis points, not SOFR. |
More in real estate, with free companion files. The full list of titles is on the author page.
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.
Also available as a standalone free template: loan amortization schedule.