A Practitioner’s Guide to What Actually Produced the Return, and Who Was Paid for It
Julian R. Sterling
These are the four Excel workbooks that go with the book. Every figure the book prints is
reproduced in them by a live formula rather than a typed constant — move an expiry year,
change the void, drag the exit yield, switch the waterfall convention, and every dependent number
moves. Each one ends with a Checks sheet setting the printed figure beside the
computed one: 158 controls in all, every one green. If a control ever reads FAIL,
the workbook is wrong, not the book.
Free to download. No sign-up, no email address, nothing to fill in.
Everything described below is inside it, with the read-me.
The four workbooks
Chapters 1 to 4
The income account, with the void applied
Ten units with their areas, passing rents, rental values and expiry years, and eleven years of
income built on top of them by formula. The re-letting rule is applied automatically: a lease
expiring at the end of year e pays its old rent through year e, three twelfths
of the new rent in year e+1, and the whole of it thereafter.
That single convention is what most models leave out, and it is visible here as soon as the
sheet opens: the rent line falls in five of the ten years, because each of
those is the year after an expiry. Passing rent starts at 1,575,900 against a
rental value of 2,284,000, two units of the ten are empty on day one, and year one produces a
negative net income of 265,863.
The_Income_Account.xlsx · XLSX · 20 KB
Chapters 5 to 7
Six entry yields, and the one that counts
An exit yield is a stabilised income over a gross value, so only one of the numbers people
quote as the entry yield can be set beside it. The sheet computes all six — 5.9693,
5.5892, 5.4551, 4.8681, 8.6515 and the like-for-like 7.0555 per cent —
and says why five of them compare two different things.
Against an exit at 6.65, the yield moved 0.4055 points. Not the 0.6807 you get
by subtracting the headline yield, and not the 2.0015 you get from the reversionary one. The
sale is priced on year eleven's 2,453,319 at 6.65 per cent, and the sheet also prices it on
year ten instead, which is worth 4,198,723 less — because year eleven is
the only year in four that carries no re-letting.
The_Sale_and_the_Yield.xlsx · XLSX · 32 KB
Chapters 8 to 12
All twenty-four orders
Switch the four drivers on one at a time and record the step in the equity return at each
switch. Do it in a different order and you get a different answer: leverage is worth
plus 1.8441 points when it goes on last and minus 1.1977 when
it goes on first, a swing of 3.0419 points on the same deal.
All twenty-four orders are here as live rows, with each driver's minimum, maximum and average.
Only the average is order-independent: business plan 2.5923, rental growth
1.4816, exit yield 0.6711, leverage 0.4535 — and the four of them sum to exactly the
5.1986 points there are to explain, which the sheet checks. That is the only
attribution worth putting in a report.
Attribution.xlsx · XLSX · 26 KB
Chapters 13 to 15
What each party actually keeps
The preferred return accruing year by year on unreturned capital and on unpaid accrued
preferred, capital returned, the full catch-up, then the eighty-twenty. The investor put in
14,943,867 and took out 29,341,958, of which 14,398,091 is
the preferred return itself: nothing above the hurdle, and a return of exactly
8.0000 per cent against a gross equity return of 8.6647. The manager took 2,114,640 of fees and
1,748,716 of promote.
The sheet also carries a switch that turns off the crediting of capital called after
completion. Turn it off and the promote goes from 1,748,716 to 3,451,151 on
exactly the same building and the same distributions — the whole difference being nine
years of preferred return on 1,108,947 the investor put in and did not get back.
The_Waterfall.xlsx · XLSX · 34 KB
Conventions used throughout
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
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, from a formula, a number
that was printed before the model existed.
On this book the discipline worked in the other direction twice, and the chapters carry the result
rather than the original claim. The first draft declared a nine-month void between leases and never
applied it; applying it turned the rent line from a rising staircase into something that falls in
five years out of ten, and made the hold-period table jagged rather than smooth. The second draft
computed the preferred return on the money subscribed at closing and ignored the
1,108,947 called a year later; correcting that cut the promote from 3,451,151 to
1,748,716 and moved the investor's return from 8.2150 per cent to exactly 8.0000.
Both errors flattered the story the book was telling. Both were caught by refusing to print a
number that would not reproduce.
Where two correct computations disagree, both are shown. The four attribution averages printed to
four decimals sum to 5.1985 against the model's 5.1986. The exit value is 36,892,015 computed from
the printed income and 36,892,021 from the unrounded. Neither gap is smoothed away.
Opening the files
The workbooks open in Microsoft Excel, LibreOffice Calc, Google Sheets and Numbers. 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.
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 FinanceFour people look at one building and reach four numbers; the lender is whole only above 105,109,489, twelve per cent below today’s value rather than forty.
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 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.
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.