Kestrel Energy 45 MWac solar

model "Kestrel Energy 45 MWac" {
  sponsor   "Kestrel Power"
  country   "Zimbabwe"
  currency  USD
  display   thousands
  data      "kestrel_inputs.xlsx"        // every table below is a sheet in here
  sheets    [dashboard, solve, cash, statements, returns,
             inputs, timing, construction, energy, opex,
             funding, debt, facilities, tax]
}

/// One time ruler with two column widths, as in the workbook's Flags sheet
timeline {
  start          2027-01-01                 // financial close 2026-12-31
  fiscal_year    ends Dec
  construction   15m   step month
  operations     25y   step quarter
}

currency ZAR, RMB, ZiG                      // some EPC lines are priced in these
fx ZAR/USD = inputs.rates.zar_usd
fx RMB/USD = inputs.rates.rmb_usd

inputs "Plant" {
  capacity_ac        = 45 MWac
  capacity_dc        = 50.39 MWp
  yield_case         = option p50 | p75 | p90 | p99      @note "lenders size on their own case"
  grid_availability  = 98 %
  plant_availability = 99 %
  degradation        = series by operations.year { 1: 0 %, 2..: 0.5 % }
}

inputs "PPA" {
  tariff         = 9 USDc/kWh                @source "Tamarind Gold PPA, net of wheeling"
  ppa_term       = 15y
  tariff_haircut = 100 %
}

inputs "Senior and sub-debt" {
  max_total_gearing  = 80 %
  senior_gearing_cap = 70 %
  sub_gearing        = 0 %
  tenor              = 7y
  base_rate          = rates.sofr_3m                      // 4.02% in 2027
  senior_margin      = 6.12 %/y
  min_dscr           = 1.20 x
  lockup_dscr        = 1.10 x
}

inputs "Equity and returns" {
  equity_timing  = option pro_rata | back_ended
  dividend_wht   = 15 %
  interest_wht   = 15 %
  discount_rate  = 10 %/y                  @note "placeholder, not taken from the workbook"
}

inputs "Working capital and VAT" {
  vat_rate        = 15.5 %
  vat_refund_lag  = 12m                     // first refund; 6m after that
  debtor_days     = 60d
  creditor_days   = 30d
}

inputs "Tax" {
  cit_rate           = 25.75 %
  allowance_regime   = option law | policy
  cit_holiday        = option yes | no
  holiday_years      = 5y
  interest_cap       = 75 %                 // of EBITDA
  loss_expiry        = 5y
}

inputs "Sensitivity flexes" {
  capex_flex    = 0 %
  opex_flex     = 0 %
  revenue_flex  = 0 %
  fx_flex       = 0 %
}

/// Each table is one sheet of kestrel_inputs.xlsx (named in model.rfn).
/// Tables whose sheet has the same name need no "sheet" line.

table rates {
  rows     year
  columns  sofr_3m: %/y "3M SOFR",   us_cpi: %/y "US CPI",
           us_ppi:  %/y "US PPI",    sa_cpi: %/y "SA CPI",
           zar_usd: ZAR/USD "USD/ZAR",  rmb_usd: RMB/USD "USD/RMB",
           cash:    %/y "Interest on cash"
}

table specific_yield: kWh/kWp/y          // P-level | kWh/kWp
table irradiation: %                     // Jan..Dec, sums to 100%
table s_curve: %                         // 15 construction months

table epc {                              // 37 lines, 5 groups
  rows     item
  columns  group: text, cost "Cost" in currency, currency: text, duty: % "Custom duty"
}

table shareholders: % {
  rows     shareholder
  columns  equity "% of equity", as_loan "Contributed as SHL"
}

table fixed_opex {
  rows     item
  columns  amount: USD/y "Annual cost", index: column of rates "Indexation"
}
table variable_opex: %                   // % of revenue

table major_maintenance: USD {
  rows     year "Operating year"
  columns  expensed, capitalised
}

table fees sheet "Facility fees" {
  rows     facility
  columns  margin: %/y, arranging: %, commitment: %/y
}

table asset_classes {
  rows     class
  columns  ifrs_rate: %/y "IFRS rate", allowance: text "Tax regime"
}

table development_costs: USD
table lender_advisors {
  rows     advisor
  columns  role: text, fee: USD "Fee cap (USD)"
}

module energy "Energy output" {
  /// Portion of a year's sunshine that falls in this period (unit: y)
  sun_share   = seasonal(inputs.irradiation)

  /// Steps down once per operating year, from year 2
  degradation = decline(inputs.degradation)

  availability = inputs.grid_availability * inputs.plant_availability

  // MWp x kWh/kWp/y x y  gives MWh, so no time conversion is needed
  net: MWh = inputs.capacity_dc * inputs.specific_yield[inputs.yield_case]
           * sun_share * availability * degradation        | during operations
}

module revenue "Revenue" {
  period ppa = operations.start for inputs.ppa_term

  tariff: USD/kWh = inputs.tariff | grow inputs.rates.us_ppi yearly from operations.start
  total:  USD     = energy.net * tariff * inputs.tariff_haircut   | during ppa
}

module opex "Operating costs" {
  for c in inputs.fixed_opex {
    fixed[c]: USD = inputs.fixed_opex[c].amount
                  | grow inputs.fixed_opex[c].index yearly from operations.start
                  | during operations
  }
  for c in inputs.variable_opex {
    variable[c]: USD = revenue.total * inputs.variable_opex[c]
  }
  major_maintenance: USD = inputs.major_maintenance[operations.year].expensed * year_start
                         | grow inputs.rates.us_cpi yearly from operations.start

  total: USD = sum(fixed[*]) + sum(variable[*]) + major_maintenance
}

module construction "Construction costs" {
  /// 37 EPC lines in five groups. Lines priced in ZAR or RMB convert at the fx row.
  epc:      USD = sum(inputs.epc[*].cost in USD)
  customs:  USD = sum(inputs.epc[*].cost in USD * inputs.epc[*].duty)
  spend:    USD = spread(epc + customs, over: construction, profile: inputs.s_curve)

  development: USD = sum(inputs.development_costs) | at construction.first
  advisors:    USD = sum(inputs.lender_advisors.fee) | at construction.first
  vat:         USD = spend * inputs.vat_rate

  total: USD = spend + development + advisors
}

module funding "Sources and uses" {
  /// "Uses of funds (incl. IDC) - copy / paste" in the workbook
  solve uses: USD = construction.total + debt.fees + debt.idc + facilities.fees
                    | during construction

  /// Pro rata or back-ended, as chosen on the Inputs sheet
  funding uses {
    first inputs.equity_timing {
      senior            share debt.senior_gearing
      sub_debt          share debt.sub_gearing
      shareholder_loan  share sum(inputs.shareholders.equity * inputs.shareholders.as_loan)
                              * (1 - debt.senior_gearing - debt.sub_gearing)
      equity            rest
    }
  }
}

module debt "Senior and sub-debt" {
  period tenor = operations.start for inputs.tenor

  all_in: %/y = inputs.base_rate + inputs.senior_margin

  /// CFADS used for sizing. The Solve macro pastes it.
  solve cfads: USD = cash.cfads | tolerance 1 USD

  /// Size: the smaller of the gearing cap and what CFADS carries at the minimum DSCR
  by_gearing:  USD = inputs.senior_gearing_cap * sum(funding.uses)
  by_dscr:     USD = npv(cfads / inputs.min_dscr | during tenor, rate: all_in)
  senior_size: USD = min(by_gearing, by_dscr)
  senior_gearing   = senior_size / sum(funding.uses)

  senior = sculpted_loan(
    draws:  funding.senior,
    rate:   all_in,
    window: tenor,
    cfads:  cfads,
    dscr:   inputs.min_dscr
  )

  sub_gearing = inputs.sub_gearing                                  // 0% in the base case

  idc:  USD = senior.interest | during construction
  fees: USD = senior.arranging_fee + senior.commitment_fee

  senior_dscr_ltm: x = (cfads | last 12m) / (senior.debt_service | last 12m)
  lockup             = senior_dscr_ltm < inputs.lockup_dscr   | during tenor
}

module facilities "VAT, working capital and reserve facilities" {
  vatf = vat_facility(
    vat:        construction.vat,
    refund_lag: inputs.vat_refund_lag,
    rate:       inputs.base_rate + inputs.fees[vatf].margin
  )

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

  dsrf = reserve_facility(target: debt.senior.debt_service | next 6m)
  mrf = reserve_facility(target: opex.major_maintenance | next 12m)

  fees: USD = vatf.fees + wc.fees + dsrf.fees + mrf.fees
}

module tax "Accounting and tax" {
  ifrs       = depreciation(additions: construction.total + funding.capitalised,
                            method:    straight_line,
                            rates:     inputs.asset_classes.ifrs_rate)
  allowances = depreciation(additions: construction.total,
                            method:    inputs.asset_classes.allowance,
                            basis:     inputs.allowance_regime)

  ebitda: USD = revenue.total - opex.total

  /// Interest above the cap is disallowed now and carried forward
  cap:        USD = inputs.interest_cap * ebitda
  balance disallowed: USD {
    + max(0, debt.senior.interest - cap)
    - used
  }
  used:       USD = min(disallowed.opening, max(0, cap - debt.senior.interest))
  deductible: USD = min(debt.senior.interest, cap) + used

  assessable: USD = ebitda - allowances.charge - deductible + cash.interest_income
  losses = tax_losses(profit: assessable | sum by year, expiry: inputs.loss_expiry)

  solve cit: USD = losses.taxable * inputs.cit_rate                   // "CIT due - copy / paste"
               | during not holiday
  period holiday = operations.start for (if inputs.cit_holiday == yes then inputs.holiday_years else 0y)
  deferred:  USD = (ifrs.nbv - allowances.nbv) * inputs.cit_rate
  paid:      USD = cit
}

waterfall cash "Cash flow waterfall" {
  start  revenue.total - opex.total - facilities.wc.change
  add    interest_income = prev(balance.closing) * inputs.rates.cash
  pay    tax             = tax.paid
  pay    construction    = construction.total - funding.total      // nets to zero
  move   mra             = facilities.mrf.net
  = cfads                "Cash flow available for debt service"
  pay    senior_interest  = debt.senior.interest
  pay    senior_principal = debt.senior.principal
  pay    senior_fees      = debt.senior.fees_ops
  = after_senior
  pay    sub_interest     = debt.sub.interest
  pay    sub_principal    = debt.sub.principal
  pay    wcf              = facilities.wc.debt_service
  pay    shl_interest     = equity.shl.interest       unless debt.lockup
  pay    shl_principal    = equity.shl.principal      unless debt.lockup
  = for_equity
  pay    distributions    = available                 unless debt.lockup
  keep   rest             as balance
}

// The workbook's three waterfall sheets come from one declaration
export cash by period, by half, by year

module returns "Valuation and returns" {
  to_equity: USD = - funding.equity - funding.shareholder_loan
                   + cash.distributions + cash.shl_interest + cash.shl_principal
  wht:       USD = cash.distributions * inputs.dividend_wht
                 + cash.shl_interest  * inputs.interest_wht

  equity_irr_pre  = xirr(to_equity)
  equity_irr_post = xirr(to_equity - wht)
  payback         = payback(to_equity)

  fcff: USD = revenue.total - opex.total - construction.total - tax.paid
  project_irr     = xirr(fcff)

  lcoe: USD/MWh = lcoe(costs: construction.total + opex.total + debt.fees,
                       energy: energy.net, rate: inputs.discount_rate)
}

dashboard {
  "Senior debt"            debt.senior_size
  "Equity IRR (post-tax)"  returns.equity_irr_post
  "Project IRR"            returns.project_irr
  "Min senior DSCR"        min(debt.senior.dscr | during debt.tenor)
  "LCOE"                   returns.lcoe
}

check "Balance sheet balances (IFRS)"  statements.bs.difference == 0 tolerance 1 USD
check "Senior debt repaid at maturity" debt.senior.balance.closing == 0 at debt.tenor.end
check "Sub-debt repaid"                debt.sub.balance.closing == 0 at operations.end
check "Cash never negative"            cash.balance.closing >= 0
check "Drawdowns within commitments"   cumulative(funding.senior) <= debt.senior_size
check "Tax base fully used"            tax.allowances.nbv == 0 at operations.end
check "Uses solved"                    funding.uses converged
check "CFADS solved"                   debt.cfads converged
check "CIT solved"                     tax.cit converged

alert "Senior DSCR below lock-up" debt.senior_dscr_ltm >= inputs.lockup_dscr | during debt.tenor

scenario base "Base case (Bidder A EPC)" {}

scenario bid_b "Bidder B EPC price" {
  inputs.epc = sheet "EPC Bidder B"           // same columns, the other bidder
}

scenario lenders "Lenders' yield" {
  inputs.yield_case = p90
}

// The workbook's flex list, as sensitivities
sensitivity inputs.capex_flex, inputs.opex_flex, inputs.revenue_flex   [-10%, +10%]
sensitivity inputs.base_rate          [+1 %/y]
sensitivity inputs.vat_refund_lag     [+3m]
sensitivity inputs.fx_flex            [+10%]

results "Scenario summary" {
  across  all scenarios
  show    debt.senior_size, returns.equity_irr_post, min(debt.senior.dscr), returns.lcoe
}

/// Proves the rebuild matches the original before the original is retired
reconcile against "kestrel-lender-model.xlsm" {
  energy.net                    ~ Ops       "Net energy output"
  revenue.total                 ~ Ops       "Total revenues"
  cash.cfads                    ~ CFW       "Cash flow available for debt service (CFADS)"
  debt.senior.balance.closing   ~ Workings  "Senior debt balance c/f"
  tax.cit                       ~ "Acc & Tax" "Periodic income tax expense"
  tolerance 1 USD
}