Modelling, Valuation, Diligence and Risk in Infrastructure Investment
Julian R. Sterling
These are the companion files of The Infrastructure Investment Analyst by Julian R. Sterling.The book on Amazon →
These are the workbooks and documents that go with the book. The three original workbooks reproduce
every figure the book states — except the three that do not, which the valuation workbook
corrects. Around them: the two models with the inputs emptied, four printable working documents, a
question book that marks itself, and three cases the book names and never takes to a number. Every
number is a live formula. Nothing is locked, protected or watermarked.
Free to download. No sign-up, no email address, nothing to fill in.
Nine workbooks, the printable documents and the read-me. Each group below can also be
downloaded on its own. Last revised 22 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.
These carry the book’s own figures, so that every number it states can be traced to a live
formula.
Download these three130 KB
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 11.6 per cent. Six controls test those claims and say out loud
whether they hold.
Debt_Sculpting_and_DSCR.xlsx · XLSX · 18 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, due in year four of the hold and every eight years after.
The reserve holds 6 at acquisition and receives 1.5 a year, so it holds exactly 12 each time the road
is resurfaced — which is what the chapter means when it describes equity cash flow as the
residual after debt service and reserve funding. Set the reserve contribution to zero on the
assumptions sheet and the shortfalls come out of operating cash: the binding year moves from year 1
to year 12, the debt falls from 171.3 to 131.1, and leverage from 63.8 to 48.0 per cent.
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.
The same models, on your own asset
The debt sculpting model and the Calder Water valuation, with the same formulas and every input
emptied. Type your own asset into the amber cells and the model runs.
Blank set · two workbooks
Your road, your utility
Twenty-three inputs on the sculpting model: the concession, traffic and toll, costs and
maintenance, the resurfacing and its reserve, the lender’s floor, the tenor and the exit.
Sixteen on the valuation: the asset, the tax, the two rates, the gearing, the handback and the
hold. Each workbook closes on a checks sheet that counts the inputs still empty and runs the tests
that hold on any asset: the binding year clears the floor exactly, the debt is repaid within the
tenor, the tenor and the hold end before the concession does, every resurfacing is paid from the
reserve, and the two valuation routes agree.
Put the book’s inputs back and the book’s figures come back with them: 63.8 per
cent leverage on the Calder Toll Road, 242.18 of equity value by both routes on Calder Water.
Four pages to print and take into a meeting. A4, with margins wide enough for US Letter.
Structuring · due diligence · Appendix B · monitoring
Four working documents
A concession and handback read-through: each clause that decides the deal, where it sits in the
agreement, and the cell of the model that prices it, because a clause with no cell is a clause the
model ignores. An adviser report review, one column per report, with the seller’s capex set
against the engineer’s. The investment committee memo frame, with the headline metrics the
first paragraph must carry in the base case and the downside. And the quarterly asset review:
metrics against the underwriting case, covenant headroom four quarters forward, the value bridge
and the asset’s own early-warning indicators.
A workbook that marks itself. Answer in the yellow cells; the marking fills in as you go.
All chapters
Twenty-seven numerical, thirteen multiple choice
The numerical questions work the book’s own assets: the Calder Toll Road sculpt from
revenue to the debt quantum, and Calder Water from the unlevered value to the cost of equity, the
three mismatches, the dividend discount gap, the terminal value and the leverage table. They are
marked against a tolerance, so a reasonable rounding passes. The score sheet counts the wrong
answers by chapter, which gives the only revision list worth having.
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 in passing 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.
Case one · extends the Calder Toll Road
The reserve that sets the price
Four ways to pay for the same resurfacing, on the same road, at the same price. With the
reserve bought at closing the road carries 171.3 of debt; with none it carries 85.0, because the
lumpy year becomes the binding year. The 6 of reserve bought with the asset adds 43.1 of debt
capacity and cuts the equity cheque by 37.1: a pound in the reserve is worth about seven pounds of
debt. Pre-funding the resurfacing from year 1 does not escape the constraint either; it moves it
to year 1.
Case two · extends the stress-testing section
Traffic below the consultant
The debt sculpted on the consultant’s traffic meets traffic 8 per cent lower. No
default, but two years of lock-up and 3.79 of trapped cash. The equity return falls from 11.6 to
7.1 per cent, and two thirds of the damage is the exit, not the lock-up. A permanent shortfall of
4.5 per cent reaches the lock-up and 9.1 per cent the default, because the binding year has no
cushion by construction. Sized on the downside, the same road carries 154.1 of debt instead of
171.3.
Case three · extends the valuation appendix
Selling with fifteen years left
Calder Water bought at 646.68, 10.43 times EBITDA, and sold after ten years. The next buyer
pays 552.28 for the fifteen years that remain, 7.54 times, although EBITDA has grown 18 per cent,
and the return is exactly the 7 per cent cost of capital. At the entry multiple the model reports
9.22 per cent, as a perpetuity 12.20: returns the asset cannot earn. The handback doubles in
present value while the fund owns it, and neither shortcut sees it.
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.
Go beyond the companion files
The Infrastructure Investment Analyst 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. 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
22 Sep 2026
Debt sculpting model corrected: a resurfacing the reserve cannot cover
is now charged to cash flow, and the equity pays for the reserve it inherits. Leverage and the binding
year are unchanged; the equity return is 11.6 per cent rather than 12.2. Blank model set, four
printable working documents, the question book and three new cases added. Read-me rewritten.
How do you calculate CFADS in project finance?CFADS = EBITDA less cash tax, working capital, capex and reserve funding. On an illustrative toll road 33.6 of EBITDA gives 28.0 of CFADS: a 1.30x DSCR, not 1.56x.
How is allowed revenue calculated under a RAB model?Allowed revenue = return on RAB + depreciation + fast money + tax. On an illustrative 1,000 RAB at a 4.0% real WACC it is 172.98: inflation is paid through the RAB.
How do you calculate toll road revenue from traffic?Revenue = AADT x toll x 365 by vehicle class, less leakage. 39,000 vehicles a day on an illustrative road earn $54.896m gross and $53.52m after 2.5% leakage.
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.
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.