Wind farm training model

model "Wind Farm PF Training Model" {
  currency USD
  display  thousands
  data     "wind_farm_inputs.xlsx"  // capex, price curves, MACRS: one sheet each
}

timeline {
  start        2020-01-01
  step         quarter
  construction 18m
  operations   15y
}

inputs "Construction" {
  contingency = 5 %
}

inputs "Generation" {
  turbines    = 30
  yield       = { p50: 2_300, p75: 2_185, p90: 2_070 } MWh/y     // per turbine
  yield_case  = option p50 | p75 | p90
}

inputs "Price" {
  price_case  = option central | high | low
  price_flex  = 0 %
  escalation  = 1 %/y
}

inputs "Operating costs" {
  om_fixed        = 40_000 USD/y   | grow 2 %/y from timeline.start
  lease_fixed     = 45_000 USD/y   | grow cpi from timeline.start
  om_variable     = 4.0 USD/MWh
  lease_variable  = 2.5 USD/MWh
  opex_flex       = 0 %
  servicing       = { 4: 100_000, 8: 200_000, 12: 100_000 } USD   // by operating year
  cpi             = 2.5 %/y
  cpi_flex        = 0 %/y
  debtor_days     = 30d
  creditor_days   = 30d
}

inputs "Senior debt" {
  gearing             = 75 %
  target_dscr         = 1.30 x
  lockup_dscr         = 1.10 x
  tenor               = 10y
  grace               = 6m
  repayment           = option sculpted | annuity
  size_mode           = option calculate | schedule
  rate_flex           = 0 %/y
  margin_construction = 2.50 %/y
  margin              = series by operations.year { 1..3: 2.00, 4..6: 1.75, 7..: 1.50 } %/y
  commitment_fee      = 40 % * margin_construction       // on the undrawn amount
  arrangement_fee     = 1 %
  dsra_cover          = 6m                                // two quarters of debt service
}

inputs "Equity" {
  shl_split      = 50 %
  shl_rate       = 7 %/y
  cash_sweep     = 30 %
  payout_ratio   = 90 %
  cost_of_equity = 10 %/y
  discount_rate  = 7 %/y
}

inputs "Tax" {
  tax_rate = 21 %
}

/// Each table is one sheet of wind_farm_inputs.xlsx (named in model.rfn)

table capex: USD                         // Item | Cost

table capex_profile: % {
  rows     quarter "Construction quarter"
  columns  turbines, contractor, grid_energisation "Grid energisation"
}

/// Merchant price in real 2020 terms: one row per case, one column per year
table price: USD/MWh sheet "Merchant price" {
  rows     case
  columns  by year                                    // 2020, 2021 … across the top
}

table base_rate: %/y                     // Year | Rate

table asset_class sheet "Asset classes" {
  rows     class
  columns  book_life: y "Book life", book_method: text "Book method",
           tax_method: column of macrs "Tax method"
}

table macrs: % {                         // Depr inputs sheet
  rows     year "Tax year"
  columns  macrs_5 "5 year", macrs_7 "7 year", macrs_15 "15 year"
}

module ops "Operations" {
  generation: MWh     = inputs.turbines * inputs.yield[inputs.yield_case]  | during operations
  price_real: USD/MWh = inputs.price[inputs.price_case, year] * (1 + inputs.price_flex)
  price:      USD/MWh = price_real | grow inputs.escalation from timeline.start
  revenue:    USD     = generation * price

  fixed_opex:    USD = (inputs.om_fixed + inputs.lease_fixed) * (1 + inputs.opex_flex)
                     | during operations
  variable_opex: USD = generation * (inputs.om_variable + inputs.lease_variable)
  opex:          USD = fixed_opex + variable_opex

  wc = working_capital(
    revenue: revenue, costs: opex,
    debtor_days: inputs.debtor_days, creditor_days: inputs.creditor_days
  )

  maintenance_capex: USD = inputs.servicing[operations.year] * year_start
}

module cons "Construction" {
  for i in inputs.capex {
    cost[i]: USD = inputs.capex[i] * (1 + inputs.contingency)
                 * inputs.capex_profile[construction.period, i]
  }
  capex: USD = sum(cost[*])

  solve idc: USD = debt.interest | during construction
  fees:      USD = debt.commitment_fee + debt.arrangement_fee
  uses:      USD = capex + idc + fees + debt.dsra.initial

  /// Equity first, then the shareholder loan, then senior debt.
  /// Additional equity covers anything left.
  funding uses {
    first  equity             up to equity.commitment
    then   shareholder_loan   up to equity.shl_commitment
    then   senior             up to debt.size
    then   additional_equity  rest
  }
}

module debt "Senior debt" {
  period tenor     = operations.start for inputs.tenor
  period grace     = operations.start for inputs.grace
  period repayment = grace.end to tenor.end

  all_in: %/y = inputs.base_rate + inputs.rate_flex
              + (if construction then inputs.margin_construction else inputs.margin)

  /// The Macro sheet: gearing vs sculpting, lower of the two, copied to "Applied"
  by_gearing: USD = inputs.gearing * sum(cons.uses)
  by_dscr:    USD = npv(cash.cfads / inputs.target_dscr | during repayment, rate: all_in)
  solve size: USD = if inputs.size_mode == calculate then min(by_gearing, by_dscr)
                    else scenario(debt).size          // equity cases keep the debt case size

  balance loan: USD {
    + cons.senior
    - principal
  }
  interest:        USD = loan.opening * all_in
  commitment_fee:  USD = (size - loan.opening) * inputs.commitment_fee  | during construction
  arrangement_fee: USD = size * inputs.arrangement_fee                   | at construction.first

  principal: USD = match inputs.repayment {
    sculpted => clamp(cash.cfads / inputs.target_dscr - interest, 0, loan.opening)
    annuity  => pmt(all_in, periods_left(repayment), loan.opening) - interest
  } | during repayment

  debt_service: USD = interest + principal
  dscr:     x = cash.cfads / debt_service                              | during repayment
  dscr_ltm: x = (cash.cfads | last 12m) / (debt_service | last 12m)
  llcr:     x = npv(cash.cfads | during tenor, rate: all_in) / loan.opening
  lockup      = dscr_ltm < inputs.lockup_dscr or dsra.underfunded

  dsra = reserve(
    target:  debt_service | next inputs.dsra_cover,
    initial: from_funding
  )
}

module tax "Depreciation and tax" {
  for a in inputs.asset_class {
    book[a] = depreciation(
      additions: cons.additions[a],
      method:    inputs.asset_class[a].book_method,
      life:      inputs.asset_class[a].book_life
    )
    allowance[a] = depreciation(
      additions: cons.additions[a],
      method:    inputs.asset_class[a].tax_method,
      schedule:  inputs.macrs
    )
  }

  taxable: USD = ops.revenue - ops.opex - sum(allowance[*].charge)
               - debt.interest - equity.shl_interest
  losses = tax_losses(profit: taxable)
  payable: USD = losses.taxable * inputs.tax_rate
}

module equity "Equity and shareholder loan" {
  commitment:     USD = (1 - inputs.shl_split) * (sum(cons.uses) - debt.size)
  shl_commitment: USD = inputs.shl_split * (sum(cons.uses) - debt.size)

  balance shares: USD { + cons.equity + cons.additional_equity }

  balance shl: USD {
    + cons.shareholder_loan
    + capitalised
    - cash.shl_principal
  }
  shl_interest: USD = shl.opening * inputs.shl_rate
  capitalised:  USD = shl_interest - cash.shl_interest

  flows: USD = - cons.equity - cons.shareholder_loan - cons.additional_equity
             + cash.shl_interest + cash.shl_principal + cash.distributions
  irr        = xirr(flows)
  npv        = xnpv(flows, rate: inputs.cost_of_equity)
}

waterfall cash "Cash flow waterfall" {
  start  ops.revenue - ops.opex - ops.wc.change
  pay    construction      = cons.uses - cons.funding.total
  pay    maintenance       = ops.maintenance_capex
  pay    tax               = tax.payable
  = cfads                  "Cash flow available for debt service"
  pay    senior_interest   = debt.interest
  pay    senior_principal  = debt.principal
  = for_dsra
  fund   debt.dsra
  = for_equity
  pay    shl_interest      = equity.shl_interest              unless debt.lockup
  pay    distributions     = available * inputs.payout_ratio  unless debt.lockup
  pay    shl_principal     = available * inputs.cash_sweep    unless debt.lockup
  keep   rest              as balance
}

statement pl "P&L" | sum by year {
  + ops.revenue
  - ops.opex
  = ebitda
  - sum(tax.book[*].charge)
  - debt.interest
  - equity.shl_interest
  = ebt
  - tax.payable
  = npat
}

statement bs "Balance sheet" | at year_end {
  + cash.balance.closing
  + ops.wc.debtors
  + debt.dsra.closing
  + sum(tax.book[*].nbv)
  = total_assets
  - ops.wc.creditors
  - debt.loan.closing
  - equity.shl.closing
  = net_assets
  - equity.shares.closing
  - cumulative(pl.npat - cash.distributions)
  = difference
}

// Each case matches a column of the Scenarios sheet.
// Anything not set falls back to the equity default: central price, P50, scheduled debt.

scenario debt "Debt" {
  inputs.size_mode = calculate
}
scenario equity "Equity" {}
scenario low "Low" {
  inputs.yield_case = p90
}
scenario half_irr "Price down to 1/2 IRR" {
  inputs.price_flex = -10 %
}
scenario dscr_one "Price down to 1.0 DSCR" {
  inputs.price_flex = -15 %
}
scenario disaster "Disaster" {
  inputs.price_flex = -10 %
  inputs.opex_flex  = -10 %
  inputs.rate_flex  = +1 %/y
}
scenario opex_up "Opex up" {
  inputs.opex_flex  = +20 %
}
scenario interest_up "Interest up" {
  inputs.rate_flex  = +2 %/y
}
scenario cpi_down "CPI down" {
  inputs.cpi_flex   = -1 %/y
}

/// Replaces the Scenarios sheet data table. A macro runs each case and pastes the row.
results "Scenario results" {
  across  all scenarios
  show    equity.npv, equity.irr, xnpv(cash.cfads - cons.capex, rate: inputs.discount_rate),
          min(debt.dscr), argmin(debt.dscr), sum(cash.cfads)
}

// The Checks sheet separates alerts (a business condition) from warnings (a model flaw)

alert "DSCR below covenant"     debt.dscr >= inputs.lockup_dscr | during debt.repayment
alert "Cash balance below zero" cash.balance.closing >= 0

check "Balance sheet 1"          fs.bs.difference == 0 tolerance 1 USD
check "Capital structure solved" debt.size converged
check "IDC solved"               cons.idc converged