Free Excel templates

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.

Get the book on Amazon → Or the DRM-free PDF edition, $19.99

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada.

Go further: Project Finance Modeling, the course

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.

Read module 4 and try ten quiz questions, free →

Want every model at once?

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).

Get the Library, $79 →

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

MeasureValue
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 EV63.8%
Equity cheque$97.3m
Enterprise value at exit, year 8$309.5m
Levered equity IRR12.16%
Equity multiple2.31x
Year-1 cash yield on equity3.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

  1. Replace the amber cells on 1. Assumptions with your own asset: concession length, traffic or volume, tariff and its escalation, costs and maintenance.
  2. Set the lender's minimum DSCR, the cost of debt, the tenor and the escalation of the service.
  3. Read the binding year on 3. Sculpting before the debt quantum: it tells you which year is setting the debt.
  4. Change the reserve and the lumpy capex to see how much debt the reserve buys.
  5. 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.

Open the companion files →

Other free templates

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.