Cookbook
The awkward parts of real models, one recipe each: the problem in plain words, the Rufineer that solves it, and the model where it came up. Written in the planned language: many recipes don't compile yet.
Operations start on 15 May but the columns are quarterly, so the first quarter should only earn about half a quarter of revenue.
revenue: USD = energy * tariff * operations.shareoperations.share is the fraction of the period's days inside the phase: 1 in full periods, about 0.52 in that first quarter, 0 outside. A plain | during operations treats the quarter as fully operating.
Lenders want to see the ramp-up month by month, then quarterly for the rest of the loan.
timeline {
start 2027-01-01
construction 18m step month
operations 25y step month for 12m, then quarter
}The ruler changes width once. Every line converts its annual rates with the right year fraction in each column.
The model runs monthly through development and construction and switches to half-years at commercial operation.
timeline {
start 2018-01-01
development 12m
construction 3m
operations 25y
step month until operations.start, then half
}The switch point can be any date or phase start.
Three solar phases reach financial close a year apart, each with its own construction period and 25-year life.
timeline {
start 2019-01-01
for s in stages {
construction[s] from inputs.stages[s].financial_close for inputs.stages[s].build
operations[s] 25y
}
}
module phase[s] "Phase {s}" for s in stages { … }Each stage gets its own flags and counters, and each module declared for s in stages becomes one sheet per stage.
A USD 250k licence fee falls due on 15 June 2031, inside a quarter.
licence_fee: USD = 250_000 USD | at 2031-06-15The amount lands in the column that contains the date.
The PPA tariff steps up every August from COD, not in January.
tariff: USD/MWh = inputs.tariff | grow inputs.escalation yearly in Aug from operations.startyearly makes a step once a year; without it, the index grows every period.
An operating model needs recorded revenue for the first two years and forecasts after that.
revenue: USD = energy * tariff | actuals "Revenue" until 2026-06-30Past periods read the Actuals sheet of the data workbook by row label. The formula row is still there, so the switch-over is visible.
Traffic is modelled monthly, debt is paid in May and November, and tax is yearly.
ruler monthly month
ruler finance half ends May, Nov
ruler annual year ends Dec
module traffic "Traffic" on monthly { … }
module debt "Debt" on finance { … }Revenue read by the debt module is summed into each half-year for you.
A development loan accrues interest daily with draws on specific dates.
ruler daily day from inputs.advance_date
module bridge "Bridge loan" on daily {
balance loan: GBP { + draws + capitalised - redemption }
interest: GBP = loan.opening * 12 %/y | day_count act/365
}Charter rates follow the market with a three-month lag.
charter_rate: USD/d = inputs.market_rate | lag 3mA 25-day dry-dock starts on the 20th and runs into the next month.
period drydock[k] = inputs.drydocks[k].start for inputs.drydocks[k].days
off_hire: d = sum(overlap(drydock[*]))
charter_days: d = period.days - off_hireThe loan can be in pesos or dollars. Everything that touches it must follow the choice.
debt_currency = option MXN | USD
balance loan: inputs.debt_currency {
+ funding.debt in inputs.debt_currency
- principal
}
debt_service_usd: USD = (interest + principal) in USDA unit can be an option. Conversions follow the switch through the fx row.
The peso is forecast from purchasing-power parity, with a separate real change on top.
fx MXN/USD = ppp(17.53, local: inputs.macro.mx_cpi, foreign: inputs.macro.us_cpi)
* grow(inputs.macro.real_fx_change)The summary shows project IRR in dollars, in pesos and in real dollars.
irr_usd = xirr(project_cash)
irr_mxn = xirr(project_cash in MXN)
irr_real = xirr(real(project_cash, by: inputs.macro.us_cpi, base: timeline.start))The yield report gives a P50 and an uncertainty of 6%. The model needs P90 and P99.
p90: kWh/kWp/y = pvalue(inputs.p50, sd: 6 %, level: 90) // 1,985.5 → 1,832.8
p99: kWh/kWp/y = pvalue(inputs.p50, sd: 6 %, level: 99) // 1,985.5 → 1,708.4Written as p50 × (1 − NORM.S.INV(level) × sd) in the workbook.
Inverters are replaced every 10 years. The model books a flat yearly provision with the same present value.
replacements: USD/kW = inputs.inverter_cost | grow 1.5 %/y | every 10y from operations.start
provision: USD/kW/y = level(replacements, rate: 10 %/y) // 4.17 USD/kW/ylevel also adds a check that the two NPVs match, like the workbook's Proof rows.
Prices and solar output come as 8,760-hour profiles; the model needs a yearly price.
capture_price: USD/MWh = weighted_avg(inputs.prices_8760[year], by: inputs.solar_8760)Hour-of-year tables are reduced to yearly factors; the timeline stays monthly or longer.
The contractor's share of profit oil falls as cumulative revenue grows relative to cumulative cost.
r_factor = cumulative(contractor.revenue) / cumulative(contractor.cost)
profit_share = bands(prev(r_factor), { ..1.0: 50, 1.0..1.5: 45, 1.5..2.0: 40, 2.0..: 30 } %)prev uses last period's R-factor, as most contracts do, which also avoids a loop.
Debt is the lowest of 80% of cost, P50 CFADS at 1.35x and P99 CFADS at 1.00x.
by_p50: USD = npv(cash.cfads / 1.35 x | during tenor, rate: all_in)
by_p99: USD = npv((cash.cfads under p99) / 1.00 x | during tenor, rate: all_in)
solve size: USD = min(0.8 * capex.total, by_p50, by_p99)under p99 builds the P99 cash flows on a parallel sheet. No second copy of the model to keep in step.
Senior 1, 2 and 3 and a mezzanine loan each have their own share, tenor, rate, fees and repayment profile.
for t in inputs.tranches {
loan[t] = term_loan(
amount: inputs.tranches[t].share * senior_size,
rate: inputs.tranches[t].rate,
window: operations.start + inputs.tranches[t].grace for inputs.tranches[t].maturity,
profile: inputs.tranches[t].repayment
)
}
debt_service: USD = sum(loan[*].debt_service)The terms come from a sheet of the data workbook. Adding a tranche is adding a row.
The model is Actual/365, but one loan accrues on 360 days.
interest: USD = loan.opening * rate | day_count act/360The development fee is 5% of total cost, and total cost includes the fee.
function gross_up(base, rate) = base * rate / (1 - rate)
development: USD = gross_up(epc.total, 5 %) // 708.58 → 37.29 USD/kWSolve it with algebra, not a loop.
A switch decides whether the reserve is cash on the balance sheet or an LC with a fee.
when inputs.dsra_mode == funded {
dsra = reserve(target: debt_service | next 6m)
}
when inputs.dsra_mode == letter_of_credit {
lc_fee: USD = (debt_service | next 6m) * inputs.lc_fee_rate
}The reserve-based loan may not exceed 45% of the present value of remaining reserves.
borrowing_base: USD = 45 % * pv_ahead(net_revenue, rate: 8 %/y) | sampled every 6m
mandatory: USD = max(0, rbl.opening - borrowing_base)The concession is bid at the toll that gives equity 15%.
seek inputs.toll so that returns.equity_irr = 15 %A Seek macro with a check, or algebra when the return is linear in the toll.
A seven-year mini-perm with a 60% balloon is refinanced by a sculpted term loan.
mini = term_loan(amount: debt.size, rate: 6 %/y, window: 7y, profile: balloon(60 %))
refi = refinance(mini, at: mini.window.end,
with: sculpted_loan(rate: 6.5 %/y, window: 15y, cfads: cash.cfads, dscr: 1.30 x))Sponsors put in 40% of their equity before any debt is drawn, then fund pro rata with the lenders.
funding uses {
first equity up to 40 % * equity.commitment
then pro_rata {
senior share 75 %
equity rest
}
}Planned as a drawdown order (equity_first 40 % of equity, then pro_rata); written by hand for now.
The state utility holds 5% of the project without putting in capital.
split to_equity {
main_investor 95 %
edm 5 % carried
funded by equity_draws
}
main_irr = xirr(to_equity.main_investor)The main investor funds all draws and receives 95% of distributions, which is why its IRR sits below the project's equity IRR.
Investors take everything until they earn 12%, then the developer gets 20%.
split to_equity {
first investors 100 % until investors earn 12 %/y
then investors 80 %, developer 20 %
}The investor takes 95% of cash and income until it earns its target yield, then 15%.
split partnership among tax_investor, sponsor {
cash = cash.distributable tax_investor 95 % until flip, then 15 %
income = tax.taxable_income tax_investor 95 % until flip, then 15 %
}
event flip = first(earned(partnership.tax_investor.after_tax, 5 %/y))Inverter replacement is modelled but switched off in the base case.
when inputs.include_inverters {
inverter_provision: USD = capacity * provision
}Rows stay in place when the switch changes; switched-off lines read as zero.
Spend follows a beta curve with alpha 9 and beta 2 rather than a typed table.
spend: USD = spread(epc.total, over: construction, profile: beta(9, 2))Also even, normal(mid, sd) or a table from the data workbook.
A small model is easier to read on a single sheet, like Coral Bay's Financial Model.
model "Coral Bay Solar" {
sheets one
}Modules become titled sections of one sheet, and the Imports blocks disappear because nothing crosses a sheet.
A lender insists on a specific Excel construct.
special: USD = excel "=MAX(0,{rev}-{cost})*{flag}"
with rev: ops.revenue, cost: ops.opex, flag: operationsReferences still go through the sheet's Imports block. rfn check lists every excel line.
Several costs are quoted per kW and converted the same way.
function per_kw(amount, capacity) = amount / capacity
om: USD = per_kw(inputs.om_budget, 1 kW) * plant.capacityFunctions are written out in full where they are used, so there is nothing hidden in VBA.
Sixteen cell towers share one template; some are excluded from the consolidation.
module tower[t] "Tower {t}" for t in inputs.towers where inputs.towers[t].include == yes { … }
ebitda: USD = sum(tower[*].ebitda)A gas turbine is overhauled every 25,000 operating hours, and output falls during the outage.
running_hours: h = period.days * 24 h/d * capacity_factor
overhaul: USD = inputs.overhaul_cost | every 25_000 h of running_hoursThe senior loan pays its share of every quarter's uses from financial close and is fully drawn at COD; preference shares, a shareholder loan and the ordinary shares pay the rest in order of seniority, each up to its commitment.
drawdown {
before financial_close: ordinary
from financial_close: pro_rata on uses
equity in_order [pref_a, shl, ordinary]
}
senior = loan(size: 70 % of costs, interest_during_construction: paid, …)
shl = loan(by: shareholders, size: inputs.shl_size, …)
shares pref_a up to inputs.pref_a
shares ordinary = restIn the model block. The loan's share is its size over the uses from financial close, solved when the uses include interest and fees on what it draws.
The partners pay their 40% before the bank lends anything, so the bank's money goes in last.
drawdown equity_first
loan = loan(size: 60 % of uses, signed: base_case, upfront_fee: 1.25 %, …)
shares partners = restThe rest's amount is the uses less every other source; it pays that first, then the loans side by side, then anything still left. in_order [partners, loan, overrun] puts a capped class first and an overrun last.
The lenders fund construction to their commitment first; the sponsors' equity comes in at the end.
drawdown debt_firstback_ended says the same. The loans draw side by side, by their sizes, then the equity in order.
The mezzanine funds first; the commercial bank and the DFI then lend pari passu; the sponsors pay the rest.
drawdown {
from financial_close: in_order [mezz, [senior, dfi]]
}Sources in [ ] share what is left by their sizes. The rest comes last, named or not. Drawing order is separate from repayment rank (rank:).
The lenders size the loan at 75% of total project cost, which includes the loan's own arrangement fee and interest during construction.
senior = loan(size: 75 % of uses, upfront_fee: 2 %, commitment_fee: 0.5 %/y,
interest_during_construction: paid, …)of costs is the capex before financing costs, with no loop. of uses is circular, and Rufineer writes the total as a solve line.