Fair Value, Governance and Judgment in Private Markets
Julian R. Sterling
Five Excel workbooks. The book describes its worked illustrations without numbers, on purpose —
the reasoning is the lesson. These files close the chain, with one full set of figures and every
input in a cell you can overwrite. They are free. Nothing is gated behind a sign-up, and no email
address is asked for.
Everything described below is inside it, with the read-me.
Chapters 5, 7 and 14
The market approach, calibration and the roll-forward
The chain of Chapter 5, link by link: five comparable peers and why each is in the set,
reported EBITDA normalised to the figure a market participant would use, and a multiple built
from four named, signed judgments — size, customer concentration, growth, margin —
that net to seven-tenths of a turn. Under one turn, as the chapter says, but now four things
that can be challenged separately rather than one number asserted.
Then the confrontation that makes it calibration rather than arithmetic. The entry price two
years ago implied its own relationship to the peer set; the file computes that relationship
independently and puts it beside the bottom-up build. When they disagree it says by how much,
and converts the disagreement into dollars of enterprise value. The day-one test is on the same
sheet: re-run at the acquisition date, does the model reproduce the price paid?
The last sheet splits the movement in enterprise value into the part caused by comparable
multiples moving, the part caused by the company’s own metric moving, and the part caused
by a deliberate change in the calibrated relationship. The three sum to the total with a
residual of exactly zero.
The income approach and the tyranny of the terminal value
Chapter 6 says the ten years of forecast may account for only a minority of present value
while the terminal value carries the majority, and calls the split dependent on the discount
rate and the growth assumption. Here it is: 171.5 of present value in ten years of forecasting,
196.2 in a single estimate of everything after.
The terminal value is computed both ways the chapter prescribes — a perpetuity growth
capitalisation and an exit multiple — and the two are put side by side, because a wide
gap means one of the assumptions is unreasonable and the model should not proceed until you
know which. A grid of five growth rates against five discount rates shows the same split
moving, with nothing in the ten forecast years changing at all.
The bridge Chapter 9 calls deceptively simple, walked twice. Once at a single enterprise
value: seven components of net debt, six of which contain a judgment, and then a waterfall
through a participating preferred, a non-participating preferred, common and an option pool.
The non-participating preferred chooses. The file computes both branches on every row —
convert, or take the liquidation preference — and takes the one the holder would actually
choose, because a model that assumes otherwise is valuing a security nobody owns. At the base
case it takes the preference, and the common is worth 29 per cent of the headline post-money
valuation of the last round. That gap is the preference stack, and Chapter 8 calls
treating the two as the same a well-known error.
One senior secured term loan of fifty million, floating over a base rate with a floor, four
years to run — walked from performing, through moves in rates and spreads, into trouble.
Move the base rate two points either way and the price does not change, to four decimal places.
Move the spread and it moves nine and a half points. Nothing about the borrower’s cash
changed in either case.
Then the borrower gets into trouble and the question stops being a yield. The file asks what the
enterprise is worth, subtracts what ranks ahead, caps the answer at the face amount, discounts
it for the eighteen months a restructuring takes, and probability-weights three states. The mark
comes out at 74.8 per cent of face. A final sheet draws the payment-in-kind accrual trap: a
balance compounding at twelve per cent against an enterprise growing at three.
Credit_Marks_Across_the_Cycle.xlsx · XLSX · 15 KB
Chapter 11, and Appendix F
The hardest judgment calls, computed
Chapter 11 says it is on real assets, secondaries and the hardest judgments that valuations
most often succeed or fail under scrutiny — and it is the one chapter in the book that
contains no figure at all. This file computes all five of its instructions on one consistent set
of inputs, and Appendix F walks through what each one turns out to be worth.
A secondary interest reported at 50 becomes an honest 45 once the lag and the distribution come
out, and the bid lands at 34.41. The 31.2-point headline discount then splits: 10.0 points of
stale reporting, 21.2 points of the secondary market pricing the interest, residual zero. Two
thirds of the discount is the part a roll-forward never recovers.
Then the double-count, in money: an entry at 7.3 times against peers at 8.9 already embeds 18.0
per cent, and a conventional 20 per cent on top compounds to 34.4 — 16.4 points too much,
or 19.7 of equity value removed twice on an enterprise value of 120.
The marketability sheet is the one that argues with the convention. A protective put at 45 per
cent volatility over two and a half years indicates 22.0 per cent. Invert it and the required
volatility for a conventional 30 per cent is 60 per cent — and no holding period whatever
reaches 30 at 45 per cent volatility. The method peaks at 28.6 per cent, at 9.6 years, and
declines after that. Volatility is an input cell; change it and the ceiling moves with it.
Two more. Three distressed trades — 71 cents after three days, 78 after forty-two, 84
after sixty-three — fit to an orderly sixty-day price of 81.7, against which the
prior-quarter mark of 96 overstates by 17.5 per cent and the three-day trade understates by
13.1. And an appraisal delivered as a single 121.9 sits inside a range of 13.7 per cent of
itself, resting on seventy-five basis points of capitalisation-rate judgment nobody had to
defend in writing.
The_Hardest_Judgment_Calls.xlsx · XLSX · 18 KB
What to break first
Set the exit multiple in the income file to twelve times. One check turns from PASS to EXPLAIN, and
the terminal value sheet tells you why: at a discount rate of 8.5 per cent, a twelve-times exit
implies long-term growth of about a sixth of one per cent. If that is not a growth rate you would
have typed, it is not an exit multiple you should have typed either. Run that inversion in both
directions and the terminal value stops being a number you assume and becomes one you defend.
Then open the exit ladder in the waterfall file and read down the price-per-common-share column.
It is flat at zero while the participating preferred absorbs everything, then rises, then kinks
where the preferred converts and the pool it shares with grows, then kinks again where the options
come into the money. Three kinks, none of them visible from a single mark, all of them
load-bearing for anyone holding the common.
And in the calibration file, type a non-zero number into the cell marked deliberate change in
the relationship this quarter. A third line appears in the roll-forward attribution with your
name on it, and the residual stays at zero. That is the whole discipline of Chapter 14: a
change you can decompose is a change you can defend.
Conventions used throughout
Amber fill
an input — you may edit these
Grey fill
a formula — do not overtype these
Checks sheet
forty-five controls across the five files, each stating its own verdict
Two of the checks are deliberately not PASS. One reads as warned when the terminal value
carries more than half the answer, because that is what Chapter 6 warns about. Another reads
as Chapter 8 warns when the common is worth less than the headline post-money implies.
Both are findings, not faults.
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.
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.
These files accompany The Private Markets Valuation Specialist. The book is on Amazon.
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.