Modelling, Valuation, Diligence and Risk in Infrastructure Investment
Julian R. Sterling
Three Excel workbooks. Every number in them is a live formula, and every figure the book states
reproduces exactly — except the three that do not, which the third workbook corrects. 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.
Chapter 4
Debt sculpting and the DSCR
The Calder Toll Road, live. The chapter gives the parameters — twenty-eight miles, a
forty-year concession with fourteen years elapsed, a lender’s floor of 1.25 times coverage
in the tightest years, debt of roughly 65 per cent of enterprise value, a resurfacing in year
four, an exit around year eight, a levered return in the low teens — then describes the
mechanic in prose and never prints the arithmetic. This file is the arithmetic.
The sculpt asks, for every year of the tenor, how large a first-year debt service that year
could carry while still clearing 1.25 times, then takes the smallest answer and names the year
that gave it. Leverage is not an input anywhere in the file: it comes out at 63.8 per cent, and
the equity return comes out at 12.2 per cent. Five controls test those claims and say out loud
whether they hold.
Debt_Sculpting_and_DSCR.xlsx · XLSX · 17 KB
Appendices B and C
Diligence, risk and the committee memo
The fifteen diligence workstreams, one row each, with an owner, a target date and the column
most trackers leave out: whether the finding was material to the investment thesis. A finding
that changes nothing and a finding that changes the price look identical in a tracker that only
records completion.
The risk register uses the four families of Chapter 8 — regulatory, political,
construction, demand — and forces every risk to be labelled mitigated, priced or accepted.
The progress sheet counts the ones still unlabelled, and that count should be zero before the
memo goes out.
Diligence_Risk_and_IC_Memo.xlsx · XLSX · 12 KB
Chapter 6, and the valuation appendix
One asset, valued properly
Chapter 6 tells you how to value an infrastructure asset and never values one. It leaves
seven quantitative claims standing without arithmetic behind them. This workbook builds the
asset — a regulated utility, twenty-five years of concession left, eleven inputs —
and computes all seven. Three of them need correcting.
It values the asset twice, by adjusted present value and by an equity discounted cash flow at a
cost of equity relevered every year as the debt amortises, and the two routes agree
to the last decimal. They agree only because the relevering uses net debt
rather than the formula most textbooks print. Reconciling them once teaches more about the
discount rate than any amount of reading about it.
The chapter warns that mismatching the cash flow and the rate costs “double digits”.
It costs 33 per cent one way and 72 per cent the other —
the error is larger than the answer. And there is a third mismatch the chapter does not mention,
which is the common one because it does not look like a mistake: holding the weighted average
cost of capital constant while the debt amortises overstates equity by
8.6 per cent. Every model with an amortising schedule and a fixed WACC contains it.
The chapter also says leverage lifts the equity return “up to a point”. There is no
such point. The return rises all the way to eighty-five per cent gearing, because the cost of
equity rises with it one for one. The point is a covenant, not an optimum, and
it is set by the lender: coverage falls below one at eighty per cent, where the equity has to
inject cash to keep the debt current.
Calder_Water_Valuation.xlsx · XLSX · 53 KB
What to break first
The resurfacing is twelve million dollars in a year that generates nineteen. Fund it from operating
cash and year four becomes the binding year, dragging the whole debt quantum down with it. Fund it
from a reserve built up over the preceding years — which is what the chapter means when it
describes equity cash flow as the residual after debt service and reserve funding —
and the lumpy year stops setting the price of the debt. Set the reserve contribution to zero on the
assumptions sheet and watch the binding year move.
The second thing to try is the hold. At exit the concession has eighteen years left, not twenty-six,
and the file values the exit on the years that actually remain. Push the hold from eight years to
twelve and the exit value falls even though the asset has done nothing wrong. The chapter calls that
assumption frequently overlooked; here it is priced.
Conventions used throughout
Amber fill
an input — you may edit these
No fill
a formula — do not overtype these
Checks sheet
five controls that state their own verdict
The traffic volume and the toll rate are placeholders, and the file says so. The book does not
publish them, and presenting invented figures as the book’s would be dishonest. Everything the
book does state is reproduced, and the checks sheet tests each one. Change the placeholders to your
own asset and the whole file re-solves.
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.
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.