Project Finance & Excel: Build Financial Models from Scratch

Learn to build a project finance financial model from scratch for infrastructure, using cash flows, capex, maintenance, inflation, and financing assumptions to evaluate IRR and tariffs.

Build a from-scratch financial model in Excel by creating input assumptions, organizing time and costs, and using multi-scenario inputs to avoid hard coding while exploring IRR and return on investment.

Build an input assumptions sheet in excel, distinguish hardcoded versus formula values, and enable scenario switching using data validation and index-based retrieval for what-if analysis.

Learn to build a time-based assumptions sheet for project finance models, using 0/1 flags to track construction and operations and schedule debt drawdown and repayment.

Create a construction sheet with capex, uses and sources schedules, and a debt drawdown model, including arrangement fees, engagement fees, and capitalized interest tied to timing based assumptions.

Learn how to break circularity in project finance models by iteratively sizing debt and equity from uses of funds, building a debt drawdown schedule, and checking balance with fees.

Build the operations sheet from traffic and revenue assumptions, compute real and nominal revenue, and integrate maintenance and SPV costs with 4% inflation, using 2% annual growth across years.

Create the profit and loss template with gross revenue, opex, ebitda, ebit, interest, income tax, and net profit, setting the stage to fill in unknowns in the next lecture.

Learn to build a linear amortization table for assets, calculating opening and closing values, applying amortization from commissioning through the concession's operational years, with no amortization during construction.

compute ebit in the profit and loss statement by incorporating amortization (wear and tear) deducted from ebitda, using values from the amortization sheet and asset opening and closing figures.

Build the debt repayment schedule to quantify yearly interest payments and debt service using opening balances, drawdowns, and PMT-based principal repayments.

Plug computed interest from the debt schedule into the profit and loss statement to derive EBIT, cap taxes with an if statement, and finalize net profit after amortization.

Learn to construct a cash flow statement with a waterfall cfads computation, then cap dividends by cash available for equity and track net cash flow and cash in hand.

Build a complete balance sheet in Excel by linking assets, liabilities, equity, debt, retained earnings, and p&l inputs, applying formulas and checks to ensure balance.

Compute and compare project IRR and equity IRR from cash flows, capex, SPV costs, and taxes, and adjust heavy-vehicle tariffs via scenario inputs to hit 23% equity IRR.