A free project finance model template in Excel, with a complete worked deal
The asset, the funding loop solved without a circular reference, a debt schedule sculpted to a constant cover ratio, and equity returns with and without the investment tax credit: ten linked sheets, one fictional deal, no macros.
The file models Aurora, 220 MW of solar with 80 MW of co-located storage, from financial close to the end of a 25-year life. Debt is sculpted, not level: the installment is solved every year so cover holds at a constant 1.35 times while the power is contracted, then 1.90 times once the plant turns merchant. The project costs $468.5 million to build, funded with $242.9 million of senior debt and $249.5 million of equity. With a 30% investment tax credit the equity earns 5.59%. Set the credit to zero and it earns 0.13%: that gap is why the deal is in the book at all.
Download the template
One Excel file, no macros, nothing locked. No account, no email address.
Nine working sheets behind a one-page read-me, in the order the calculation runs. Inputs are blue type; everything else is a formula.
Assumptions
The asset, the capital cost, revenue from the power purchase agreement and the merchant tail, operating cost, the debt terms and its two cover ratios, tax and policy including the investment tax credit, and a stress switch.
Cash flow
Generation and revenue year by year, whether the year is contracted or merchant, operating cost, CFADS, the target cover ratio for that year, and the sculpted debt service that holds it.
Solve
The funding loop, unrolled one pass per column: construction cost, the loan the cover test allows, interest during construction, fees and the debt service reserve, repeated until the loan stops moving. A cell reads CONVERGED.
Returns
Sources and uses, the equity cash flow with the construction period inside it, the equity IRR and the pre-tax project IRR, and a second series that reruns the same return with the tax credit set to zero.
Debt
Interest, principal and the closing balance of the sculpted loan, year by year to full repayment.
Signed schedule
The debt service actually written into the credit agreement, held fixed when the stress switch reads YES instead of letting the loan re-size itself to a weaker year.
Simplifications
Two modeling choices priced on their own: the reserve returning to equity at the tenor rather than at year 25, and tax losses carried forward instead of forfeited each year.
Project
The whole project's pre-tax cash flow, construction and operation, in one row.
Checks
Every figure the book prints, recomputed and compared, plus the identities that must hold whatever the inputs are.
What the worked deal returns
Measure
Value
Construction cost, all in
$468.5m
Senior debt, cover-test binding
$242.9m
Sponsor equity at close
$249.5m
Gearing
49.3%
CFADS, year 1
$34.3m
Minimum cover ratio, contracted years
1.35x
Minimum cover ratio, merchant years
1.90x
Equity IRR, 30% investment tax credit
5.59%
Equity IRR, no credit
0.13%
Project IRR, pre-tax
2.68%
Read the last four lines together. The debt is sized so the cover ratio never moves: 1.35 times every year the contract runs and 1.90 times every year after, which is what a sculpted installment is for.
The pre-tax project earns 2.68%, and levering it with debt already lifts the equity return past that, but only to 0.13% once the tax credit is removed.
The 30% credit alone is worth 5.46 points of equity IRR on this deal, which is more than the debt, the sculpting and the merchant tail combined.
That is the number an investment committee should ask for before anything else, and the Returns sheet prints it beside the base case rather than in a footnote.
How to use it on your own deal
Open Assumptions and overwrite the blue cells: capacity, capital cost, the power price and merchant tail, operating cost, the two cover ratios, and the tax and policy block.
Read Solve before anything else. Change an input and watch the loan converge again on its own; if the check cell stops reading CONVERGED, an input broke the loop.
Watch the DSCR row on Cash flow: it is flat by construction in the years you sculpt, then steps to the next target when the contract ends.
Try the investment tax credit on Assumptions first. On this deal it decides more of the return than the debt does.
Set the stress switch on Assumptions to YES to hold the signed schedule against a weaker resource year instead of letting the loan re-size itself.
Questions people ask about it
Is this project finance model really free?
Yes. It is a companion file to a book. There is no sign-up, no email address and no paid version.
Does it use macros or circular references?
No macros and no circular reference: the funding loop is unrolled one pass per column on the Solve sheet, with a cell that reads CONVERGED once the passes stop moving. The file recalculates in Excel, LibreOffice or Google Sheets without iterative calculation.
Why is the debt service not a level payment?
The loan is sculpted: the installment is solved every period so that cash flow available for debt service, CFADS, covers it at a constant target, 1.35 times in the contracted years and 1.90 times once the contract ends and the merchant tail begins. A sculpted schedule uses the cash flow the way a level loan cannot, which is why it sizes the largest loan a lender will support against a resource that varies year to year.
Can I use it for a real transaction?
You can reuse the structure. The project, the offtaker and every figure in it are fictional, and nothing here is investment advice.
The rest of the files
This template is one of the companion files for The Project Finance Handbook: the full set adds
a second deal (Northgate, a 96 MW data center), a blank version of both models, thirteen working documents and forty marked questions. Free, like this one.