A free debt sculpting model in Excel, on a toll road concession
CFADS year by year, debt service sculpted to the lender's minimum DSCR in the binding year, the debt quantum as the present value of the service, a maintenance reserve, and the equity cheque, exit and IRR: six sheets, no macros, no circularity.
The file finances the Calder Toll Road, a fictional 28-mile road with 26 years left on a 40-year concession. Cash flow available for debt service starts at $16.75 million and grows faster than the debt service, which escalates at 2.1% a year, so coverage is tightest in year one. Sculpted to a 1.25x minimum DSCR there, the service supports $171.3 million of debt at 5.8% over 18 years, 63.8% of a $268.6 million enterprise value. The equity cheque of $97.3 million is the residual, and over an eight-year hold it earns a 12.16% IRR and 2.31 times its money.
Get the template
One Excel file, no macros, nothing locked. Free: enter your email address and the download opens at once, along with the other 129 free models.
Debt_Sculpting_Model.xlsx (24 KB)
You join the reader list: one email when a new free model or a new book comes out, and a few notes on using the models. Leave in one click. Already on the list? Enter the same address. How your address is used.
The book behind this model
This file is one worked case from The Infrastructure Investment Analyst. The book explains every assumption, every sheet and the decision the numbers support, chapter by chapter.
Take sculpting further on a solar project with a merchant tail: two sizing cases, period targets, the tax shield solved without a circular reference, reserves and stress. Exercises that check your answers, a 160-question quiz and the Project Finance Model, Pro Edition included ($149 at launch). The Pro model alone is $99.
The Financial Model Library: all 130 models from this site in one download, organised by theme, plus two modelling books in DRM-free PDF (Financial Modeling from First Principles and Real Estate Financial Modeling).
This free file stays free. Secure checkout by Stripe.
Not on Amazon: 11 paperbacks by Julian R. Sterling on banking, credit and payments, printed on demand and shipped worldwide. Browse the bookshop →
What is in the file
Six sheets. Amber cells on the Assumptions sheet are inputs; nothing else is typed.
0. Read Me
What the file adds to the chapter, what it deliberately leaves out (construction, sweep, hedging, tax), and the sheets.
1. Assumptions
The concession, traffic and toll, inflation and the toll escalation floor, operating cost and the efficiency programme, routine maintenance, the resurfacing and the reserve, and the financing and exit terms.
2. Cash flow
Traffic, toll per mile, revenue, operating cost, maintenance, reserve contribution, CFADS and the reserve balance, year by year.
3. Sculpting
The largest year-one service each year could carry, the smallest of them (the binding year), the escalating service, the debt raised as its present value, the amortisation schedule and the DSCR achieved.
4. Equity
Enterprise value at entry, debt, the equity cheque and leverage; the exit on the years of concession that remain; the equity cash flow and the return read three ways.
5. Checks
Five controls that test themselves: minimum DSCR equals the floor, coverage rises elsewhere, leverage, IRR, and debt fully repaid.
What the worked case returns
Measure
Value
CFADS, year 1
$16.75m
Minimum DSCR (binding year 1)
1.25x
Debt service, year 1
$13.40m
Debt raised at close
$171.3m
Enterprise value at acquisition
$268.6m
Leverage, debt over EV
63.8%
Equity cheque
$97.3m
Enterprise value at exit, year 8
$309.5m
Levered equity IRR
12.16%
Equity multiple
2.31x
Year-1 cash yield on equity
3.44%
Set the reserve contribution to zero and watch the binding year move. Funded out of operating cash at $1.5 million a year, the $12 million resurfacing in year four never touches coverage. Left to fall in the year it is spent, it becomes the tightest year, and the lender sizes the whole debt on it.
How to use it on your own project
Replace the amber cells on 1. Assumptions with your own asset: concession length, traffic or volume, tariff and its escalation, costs and maintenance.
Set the lender's minimum DSCR, the cost of debt, the tenor and the escalation of the service.
Read the binding year on 3. Sculpting before the debt quantum: it tells you which year is setting the debt.
Change the reserve and the lumpy capex to see how much debt the reserve buys.
Read 4. Equity last: the equity is the residual, so its IRR follows from everything above.
Questions people ask about it
Is this debt sculpting model really free?
Yes. It is a companion file to a book. The file costs nothing. You enter an email address, the download opens at once, and you can leave the reader list in one click.
Does it use macros or circular references?
No macros and no circular reference. The service is sculpted to the binding year and the debt is its present value at the cost of debt, so nothing refers back to itself and no copy-paste macro is needed.
What is debt sculpting?
Sizing the debt so that debt service follows the project's cash flow at a target coverage, rather than a level annuity. Here the service escalates gently while cash flow grows faster, so the DSCR is exactly 1.25x in the tightest year and rises to 1.76x later.
Can I use it for a real project?
You can reuse the structure. The road and every figure in it are fictional, some traffic and toll inputs are placeholders the chapter does not publish, and nothing here is investment advice.
The rest of the files
This template is one of the companion workbooks for The Infrastructure Investment Analyst: the others value a regulated water utility twice and take a deal through diligence, risk and the investment committee memo. Free, like this one.
LCR and NSFR calculatorLiquidity coverage and net stable funding built line by line, with encumbrance and six defensible readings.
Risk-weighted assets (RWA) calculatorStandardised against internal-model RWA, the mortgage loan split, CET1 ratios and the year the output floor binds.
Credit scorecard modelA points scorecard built from counts: bins, weight of evidence, information value and the leaking variable.
Price waterfall analysisList price to pocket price for one account and a whole company: nine leakages, ranked and added up.
This template comes from The Infrastructure Investment Analyst. 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 template stays free either way.