Fair Value, Governance and Judgment in Private Markets
Julian R. Sterling
These are the companion files of The Private Markets Valuation Specialist by Julian R. Sterling.The book on Amazon →
Five Excel workbooks, revised. 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. Around them: the same five with the inputs emptied, twelve working
documents to print, forty questions that mark themselves, and three cases the chapters name and never
take to a number. All free. Nothing is gated behind a sign-up, and no email address is asked for.
The five workbooks, the blank set, the printable documents, the question book, the three cases 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.
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 1.24 a share against the round
price of 2.00: 62 per cent of what the headline post-money implies for it. That gap is
the preference stack, and Chapter 8 calls treating the two as the same a well-known error.
The exit ladder shows where each layer bites: the common is worth nothing below an enterprise
value of 190, the options come in at 302.5, the preferred converts at 344.
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 the value left to cover it once the senior claim
is paid, growing at 8.1 per cent and slowing. The two lines cross in year four.
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.
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 until both preferences are covered, then rises, then kinks where the options
come into the money, then kinks again where the non-participating preferred converts and the pool
it shares with grows: at enterprise values of 190, 302.5 and 344. 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.
The same models, for your own assets
The five workbooks with every input emptied. The checks sheets count what is still missing and test what holds for any inputs.
Chapters 5 to 11 · Appendix F
Five blank workbooks
Your comparable set, normalisation and multiple build, with the entry anchor and last quarter for the calibration and the roll-forward. Your forecast, discount rate and terminal assumptions. Your net debt and capital structure. Your loan, its recovery states and a payment-in-kind position. And the five hard calls: a fund interest, an entry discount, a protective put, three distressed trades and an appraisal. The grids and the exit ladder are steps, not data, and are kept.
Each file stays blank until every input is in, then runs the tests that hold for any inputs: the roll-forward closes, the waterfall distributes exactly its equity, recovery never exceeds face, the discount decomposes with no residual. The book’s figures are listed in a text file inside the archive; type them back in and the book’s results come back.
Twelve pages to print and use. A4, with margins wide enough for US Letter.
Chapters 3 to 14 · Appendices B and C
Twelve working documents
The valuation policy on one page, method by asset type. The quarterly calendar worked backward from the reporting deadline. The quarterly checklist of Appendix C. The valuation memorandum of Appendix B, one box per section. The market approach link by link. Calibration, the day-one test and the roll-forward with its residual. The terminal value review with a blank sensitivity grid. The bridge from enterprise value to the security held, with the post-money test. The credit mark: yield, recovery states and payment-in-kind coverage. The five hardest calls. The valuation committee agenda and minutes. And audit readiness with model control.
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 fair value and governance, seven on the market approach and calibration, six on the income approach, seven on equity and the bridge, seven on credit, the hard calls and Appendix F, 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 11 · Case one
The commitment nobody priced
Chapter 11 lists the remaining unfunded commitments among what drives a secondary discount, and Appendix F prices the interest without them. With 12 still to be called and the called capital returning 1.40× (ADDED), the bid falls from 34.41 to 32.41, and the headline discount splits three ways: 10.0 points of stale reporting, 21.2 of market pricing and 4.0 for the commitment. The capital called later must return 1.74× merely to be worth nothing.
Chapter 9 · Case two
The quarter the currency hid
The market workbook’s quarter, in a euro company held by a dollar fund: up 19.1 per cent in euros, up 13.1 in dollars after a five per cent fall in the euro (ADDED). The currency line is -10.63 in the policy order and -8.93 in the other; the 1.70 between them belongs to neither driver, and the policy has to say where it goes. A fall of 16.0 per cent would have hidden the quarter entirely.
Chapter 11 · Case three
The contract lost in January
The 28 per cent customer gives notice after the measurement date. At a 30 per cent contribution margin (ADDED) EBITDA falls 39.6 per cent. Classified as evidence of conditions at the date, the equity mark is 82.18 once the multiple is re-underwritten; as a new development, 193.24: 111.06 rides on the dates. Leaving the multiple alone lands close, but only because a double count of 8.20 offsets a margin premium the company no longer earns. And the concentration discount had implied a 10.9 per cent chance of losing the customer.
fifty-one controls across the five workbooks, each stating its own verdict; the blank set and the cases end with their own
ADDED
a figure the book does not give, used in the cases and marked wherever it appears
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 price implies for its own shares.
Both are findings, not faults.
Go beyond the companion files
The Private Markets Valuation Specialist 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 five workbooks revised. Waterfall: the common is worth 62.2 per cent of what the headline implies for its own shares, not the 29 per cent of the whole headline the first edition compared it with; the options come in at 302.5, before the preferred converts at 344. Market: the subject out-earns all five peers, by 300 basis points over the median, not 212. Income: the terminal value carries 53.4 per cent on the base case, not above sixty. Credit: the cure value now uses its own spread input, the payment-in-kind chart is drawn, and one check that failed on other inputs is rebuilt. Fifty-one checks. Added: a blank set of the five workbooks, twelve working documents, forty questions that mark themselves and three new cases. Each workbook can now also be downloaded on its own.
How do you value warrants in a private company?As a call on the ordinary share's fair value over the expected term, diluted: 1.30 a warrant at a 5.00 strike on a 4.00 share, not 2.13 on the contractual term.
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.
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.
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.