Lab: project finance calculations in Python

Video lecture

Lab: project finance calculations in Python

8 chapters · about 8 min · full transcript

Coming soon

Chapter 1 of 8

A shadow model you can trust

Sculpted debt, schedule, DSCR
LLCR, PLCR, project and equity IRR
Monte Carlo on CFADS and lock-up risk

The narrated lecture is in production

Every chapter is scripted and ready. Browse the chapters and read the full transcript now — the video will appear here when it’s published.

Chapters

Transcript of the narration, chapter by chapter.

0:00 A shadow model you can trust

Here's a habit that separates careful analysts from everyone else. When a model says the project can borrow fifty-five million at a one point three times DSCR, they don't just accept it. They reproduce it independently, in a different tool, from the same inputs. If the answers match, confidence goes up. If they don't, they've found something worth knowing before the lenders do. In this lab lecture I'll walk you through a short Python notebook that sizes and sculpts debt, builds the repayment schedule, calculates DSCR, LLCR and PLCR, computes project and equity IRR, and then runs a Monte Carlo simulation on CFADS to show how often minimum DSCR would breach a lock-up level. By the end, you'll have a shadow model you can adapt to check any project finance spreadsheet.

0:57 Why a shadow model?

Why build a shadow model? Three reasons. First, independent verification. The audited spreadsheet remains the model of record, but reproducing its key outputs in a separate tool catches errors that consistency checks miss, such as a wrong discounting convention or a mis-specified sizing period. Second, speed. Questions like 'what if yield is more volatile than we assumed?' can be explored in seconds, without touching the model everyone relies on. And third, clarity. Writing the calculation in a few lines of code forces you to be explicit about every assumption, which is a brilliant way to understand the mechanics deeply. None of this replaces the spreadsheet. It makes you better at checking it.

1:46 Setup and inputs

The setup is quick: a virtual environment, then install NumPy, numpy-financial, pandas and Jupyter. The commands are in the lesson text. One warning before we start. numpy-financial's NPV function treats the first value as time zero, while Excel's NPV discounts the first value by one period. We'll use that deliberately later. Our inputs are illustrative: ten operating years of CFADS rising from ten to fourteen million, debt repaid over the first eight years, a target DSCR of one point three, an interest rate of seven per cent, capital cost of seventy-five million and a gearing cap of seventy-five per cent. A repayment flag marks years one to eight. In real use, you'd paste in the CFADS row from the audited model, so both tools start from identical cash flows.

2:42 Cells two and three: sizing and schedule

Cell two sizes the debt. In each repayment year, maximum debt service is CFADS divided by one point three. Discount those amounts at seven per cent and add them up: about fifty-five point four six million. The gearing cap is seventy-five per cent of seventy-five million, fifty-six point two five. The lower is fifty-five point four six, so the DSCR test binds. Cell three builds the schedule. Interest is the opening balance times seven per cent. Principal is debt service minus interest. The closing balance rolls forward. Look at the output: DSCR is exactly one point three in every repayment year, and the closing balance after year eight is zero. That's what correct sculpting looks like. If your spreadsheet's schedule doesn't match this for the same inputs, one of the two has an error, and now you know where to look.

3:43 Worked example: LLCR, PLCR and IRRs

Cell four calculates the forward ratios. LLCR at the start of operations is the NPV of CFADS over the loan life, at the debt rate, divided by the debt. Here's where the numpy-financial convention helps: I put a zero in front of the cash flows so the first real year is discounted by one period. LLCR comes out at one point three, equal to the sizing DSCR, exactly as theory predicts. PLCR, including the two tail years, is about one point five seven. Cell five calculates IRRs, with the simplifying assumption that all capex is spent in year zero. Project IRR, on total cash flows before financing, is about ten point one per cent. Equity IRR, on equity invested and cash after debt service, is about fifteen point two. Leverage lifts the equity return, and, as we saw in the capital structure lesson, the risk that comes with it.

4:48 Worked example: Monte Carlo on CFADS

Now the most interesting cell. We simulate twenty thousand futures. Each has two sources of uncertainty in energy yield: a single draw for the long-term average, applied to every year, and separate year-to-year variability. Opex is fixed at four million, and debt service is fixed by the schedule, so any revenue shortfall falls straight through to CFADS. For each future, we calculate DSCR in every repayment year and take the minimum. The results are sobering. The median minimum DSCR is about one point one five, well below the one point three sizing target. There's roughly a thirty-one per cent chance at least one year falls below a one point one lock-up level, and about an eight per cent chance of a year below one times. Here's the key idea: that's why lenders size on P90 cash flows, not P50. A loan sculpted on P50 will very often lock up.

5:53 Watch me do it: reconcile to the spreadsheet

Let me show you how I use this on a real deal. I paste the audited model's CFADS row and debt terms into the inputs cell, run everything, and fill a small comparison table: debt size, minimum DSCR, LLCR at commercial operation, project IRR and equity IRR, with columns for the model, the notebook and the difference. Most rows match to rounding. Say LLCR differs by nought point nought three. I don't assume either is wrong. I check the loan agreement definition, and find that it includes the DSRA balance in the numerator, which the model does and my notebook doesn't. I add it, and the difference disappears. That's the value: every difference is either an error or a definition you now understand properly.

6:47 Common mistakes, recap and try this now

A few mistakes to avoid. Mixing Excel's NPV convention with numpy-financial's. Reporting only the base-case DSCR when the distribution tells a very different story. Using Monte Carlo ranges you made up, rather than the uncertainty figures in the yield or traffic adviser's report. And treating the shadow model as the model of record. So, to recap. You've built a notebook that sizes and sculpts debt, checks the schedule, calculates LLCR, PLCR and IRRs, simulates CFADS uncertainty, and reconciles against the spreadsheet. Your try-this-now: add a twelve-month grace period by switching off repayment in year one, so debt is repaid over years two to nine. Re-run the notebook. How does the debt size change, what happens to LLCR, and does the lock-up probability go up or down?

What you will build

A short Python notebook that reproduces the core project finance calculations you have learned, so you can check a spreadsheet model independently: a CFADS profile, sculpted debt sizing against a target DSCR and a gearing cap, a full repayment schedule with interest and principal, DSCR by year, LLCR and PLCR, project and equity IRR, and a Monte Carlo simulation of CFADS that shows how often minimum DSCR would breach a lock-up level. Numbers are illustrative; the method is what you reuse.

A notebook does not replace the lender-audited spreadsheet model. It is an independent check (a "shadow model") and a fast way to explore questions such as "what happens to minimum DSCR if yield is more volatile than we assumed?"

Setup

python -m venv .venv && source .venv/bin/activate      # Windows: .venv\Scripts\activate
pip install numpy numpy-financial pandas jupyter
jupyter notebook

numpy-financial provides npv, irr, pmt and related functions. Note one convention that differs from Excel: npf.npv(rate, values) treats the first value as time 0, while Excel's NPV() discounts the first value by one period.

Cell 0 and 1: inputs

import numpy as np
import numpy_financial as npf
import pandas as pd

# Cell 1: annual CFADS profile (USD M, illustrative) and debt terms
years = np.arange(1, 11)                        # 10 operating years; debt repaid over years 1-8
cfads = np.array([10.0, 11.0, 12.0, 12.0, 13.0, 13.0, 13.5, 13.5, 14.0, 14.0])
tenor, rate, target_dscr = 8, 0.07, 1.30
capex, gearing_cap = 75.0, 0.75
repay = years <= tenor

Cell 2: sculpted debt sizing

Maximum debt service in each repayment year is CFADS ÷ target DSCR. Discounting those amounts at the debt rate gives the DSCR-based debt capacity; the debt is the lower of that and the gearing cap.

# Cell 2: sculpted debt sizing
ds_max = np.where(repay, cfads / target_dscr, 0.0)          # debt service each year
df = 1 / (1 + rate) ** years
debt_dscr = float((ds_max * df).sum())                      # PV of max debt service at the debt rate
debt = min(debt_dscr, gearing_cap * capex)
print(f"DSCR-sized debt {debt_dscr:.2f}M | gearing cap {gearing_cap * capex:.2f}M | debt {debt:.2f}M")

Output: DSCR-sized debt of about 55.46M against a gearing cap of 56.25M, so the DSCR test binds.

Cell 3: repayment schedule and DSCR

# Cell 3: repayment schedule (interest on opening balance, principal = debt service - interest)
scale = debt / debt_dscr                                    # < 1 if the gearing cap binds
bal, rows = debt, []
for y, c, ds in zip(years, cfads, ds_max * scale):
    interest = bal * rate
    principal = ds - interest if ds else 0.0
    rows.append((y, c, bal, interest, principal, ds))
    bal -= principal
sched = pd.DataFrame(rows, columns=["year", "CFADS", "opening", "interest", "principal", "debt_service"])
sched["DSCR"] = np.where(sched.debt_service > 0, sched.CFADS / sched.debt_service, np.nan)
print(sched.round(2).to_string(index=False))
print(f"Closing balance after year {tenor}: {bal:.4f}")      # should be ~0

Every repayment year shows DSCR 1.30x and the balance reaches zero at the end of year 8, which is exactly what sculpting should produce. If the gearing cap had bound instead, scale would be below 1 and DSCRs would sit above the target.

Cell 4: LLCR and PLCR

# Cell 4: LLCR and PLCR at the start of operations
llcr = npf.npv(rate, np.r_[0, cfads[repay]]) / debt         # npf.npv treats the first value as t=0
plcr = npf.npv(rate, np.r_[0, cfads]) / debt
print(f"LLCR {llcr:.2f}x | PLCR {plcr:.2f}x")

LLCR equals the target DSCR (1.30x) because debt was sculpted at that DSCR and the LLCR is discounted at the same rate. PLCR is higher (about 1.57x) because it includes the two tail years after maturity.

Cell 5: project and equity IRR

# Cell 5: project and equity IRR (construction in one year 0 for simplicity)
project_cf = np.r_[-capex, cfads]
equity_cf = np.r_[-(capex - debt), cfads - np.r_[sched.debt_service.values]]
print(f"Project IRR {npf.irr(project_cf):.2%} | Equity IRR {npf.irr(equity_cf):.2%}")

With the simplifying assumption that all capex is spent in year 0, project IRR is about 10.1% and equity IRR about 15.2%: leverage lifts the equity return, with the risk you saw in the capital structure lesson. A real model spreads capex over construction, includes fees, IDC, reserves and tax.

Cell 6: Monte Carlo on CFADS

Two sources of uncertainty: the long-term average yield (a single draw per simulation, applied to every year) and year-to-year variability. Opex and debt service are fixed, so lower revenue falls straight through to CFADS.

# Cell 6: Monte Carlo on CFADS: yield uncertainty with fixed opex and debt service
rng = np.random.default_rng(11)
N = 20_000
revenue_base = cfads + 4.0                                  # assume 4.0M fixed opex each year
long_term = rng.normal(1.0, 0.05, (N, 1))                   # uncertainty in the long-term average yield
annual = rng.normal(0.0, 0.06, (N, len(years)))             # year-to-year variability
sim_cfads = revenue_base * (long_term + annual) - 4.0
sim_dscr = sim_cfads[:, repay] / sched.debt_service.values[repay]
min_dscr = sim_dscr.min(axis=1)
print(f"P50 min DSCR {np.percentile(min_dscr, 50):.2f}x | P10 {np.percentile(min_dscr, 10):.2f}x")
print(f"P(any year below 1.10x lock-up) {np.mean(min_dscr < 1.10):.0%} | P(any year below 1.00x) {np.mean(min_dscr < 1.0):.0%}")

Typical output: the median of minimum DSCR across the repayment years is about 1.15x, well below the 1.30x sizing target, and roughly a 31% chance that at least one year falls below a 1.10x lock-up level. That is the quantitative reason lenders size debt on P90 (or similar) cash flows rather than P50: a loan sculpted on P50 CFADS will very often breach its lock-up in at least one year.

Reading and presenting the results

Output	Meaning	Present it as
DSCR-sized vs gearing-capped debt	Which constraint binds	"Debt of 55.5M is DSCR-constrained"
LLCR at COD	Forward cover over the loan life	"LLCR 1.30x, equal to the sizing DSCR"
PLCR	Cover including the tail	"Tail provides additional cover (PLCR 1.57x)"
Min DSCR distribution	Resilience to yield uncertainty	"About a 3 in 10 chance of at least one lock-up year if sized on P50"

Common mistakes

Using npf.npv and Excel NPV() interchangeably without adjusting for the time-0 convention.
Sizing on P50 CFADS and reporting only the base-case DSCR.
Forgetting that the Monte Carlo ranges must come from the yield or traffic adviser's uncertainty analysis, not from guesswork.
Treating a shadow model as the model of record; the audited spreadsheet remains the reference.

How to measure success

The notebook reproduces the spreadsheet model's debt size, DSCRs and LLCR within rounding for the same inputs.
The Monte Carlo inputs are traceable to the adviser's reports.
Findings from the notebook (for example lock-up probability) are discussed with the modelling team, not used on their own.

A Python shadow model independently checks the spreadsheet model of record; it does not replace it.
numpy-financial npv treats the first value as time 0, unlike Excel NPV, which discounts the first value by one period.
Sculpted debt produces a flat DSCR at target and an LLCR equal to the sizing DSCR in the base case.
Monte Carlo on CFADS shows that debt sculpted on P50 often breaches lock-up in at least one year, which is why lenders size on P90.

Build the notebook, then add a 12-month grace period (repay over years 2–9). Record how debt size, LLCR and the lock-up probability change and explain why.

Enrol for free to save your progress

Reading is always free. Enrol to keep your place, take the final assessment and earn a verifiable certificate.

Lab: project finance calculations in Python