Coral Bay Solar 94 MWp

Based on coral-bay-solar-model.xlsx

A Mexican solar project with a peso or dollar loan, an FX forecast built from inflation differentials, lenders who size debt on P50 and P99 at the same time, and returns shown in dollars, pesos and real dollars. The whole calculation sits on one 505-row sheet. Design example: written in the full v0.5 language. The compiler builds the working core today, so this one doesn't compile yet. Source: examples/design/coralbay/.

Workbook: 10 sheets; the model is one sheet of 505 rows by 209 columns
Timeline: Development from Jan 2018 (12 months), FC Jan 2019, 3 months construction, COD Apr 2019, 25 years; monthly before COD, six-monthly after
Plant: 94.3 MWp, PVSYST P50 1,985.5 kWh/kWp direct, 1,615 after an 81.4% performance ratio; tariff USD 35/MWh escalating 1.5%
Capital: USD 745.87/kW (EPC 708.58 plus 5% development), USD 70.3m in total; 80% debt, 16-year tenor
Outputs: Project IRR pre-tax 5.4% (USD), 9.9% (MXN), 3.8% (real); equity IRR 9.3%; LCOE USD 40.1/MWh
Rebuild shown: 8 files, 166 lines

Where each sheet goes

Workbook sheets	Rufineer
Financial Model (timing, macro, operations, capex, revenue, opex, debt, tax, returns)	model.rfn, energy.rfn, capex.rfn, operations.rfn, debt.rfn, returns.rfn
Summary Page	the `dashboard` in returns.rfn
PVSYST, Helioscope	the P50 input and `pvalue` for P90 and P99
Cost Detail	the EPC sheet of `coralbay_inputs.xlsx`
Inverter Replacement	one `level(…)` line
Notes	doc comments and `@source` tags
Annual Model, PVWatts, Mexico Exchange Rate (empty)	not needed

Source 8 files shown, 166 lines

model.rfn

model "Coral Bay Solar 94 MWp" {
  currency   USD
  currency   MXN
  display    thousands
  data       "coralbay_inputs.xlsx"
  sheets     one                  // the original keeps everything on one sheet
}

timeline {
  start          2018-01-01
  development    12m
  construction   3m
  operations     25y
  step           month until operations.start, then half
}

/// Peso forecast from purchasing-power parity, times the real change
fx MXN/USD = ppp(17.53, local: inputs.macro.mx_cpi, foreign: inputs.macro.us_cpi)
           * grow(inputs.macro.real_fx_change)

inputs.rfn

table macro {                           // one row per year
  rows     year
  columns  us_cpi: %/y "USD inflation", mx_cpi: %/y "Mexican inflation",
           real_fx_change: %/y "Real change in exchange rate"
}

table epc: USD/kW sheet "EPC"          // module, inverter, tracker, BOS, installation

inputs "Energy" {
  capacity        = 94.319 MWp
  degradation     = 0.5 %/y
  p50_direct      = 1_985.5 kWh/kWp/y      @source "PVSYST"
  yield_sd        = 6 %
  performance     = { p50: 81.36, p90: 78.0, p99: 76.0 } %
  yield_case      = option p50 | p90 | p99
}

inputs "Revenue and costs" {
  tariff             = 35 USD/MWh
  tariff_escalation  = 1.5 %/y
  om                 = 5.3 USD/kW/y
  om_escalation      = 1.5 %/y
  insurance          = 0.05 %/y          // of total cost
  include_inverters  = option no | yes
  inverter_cost      = 79.28 USD/kW
  development_share  = 5 %               // of total cost, including itself
  debtor_days        = 40d
  creditor_days      = 15d
  tax_rate           = 30 %
  depreciation_life  = 15y
}

inputs "Financing" {
  debt_currency   = option MXN | USD
  funding_order   = option pro_rata | equity_first
  max_gearing     = 80 %
  dscr_p50        = 1.35 x
  dscr_p99        = 1.00 x
  tenor           = 16y
  dsra_cover      = 6m
  libor_fixed     = 2.0 %/y
  mexico_interbank = 5.0 %/y
  spread_case     = option flat | increasing | high
  spreads         = { flat: 1.5, high: 2.5 } %/y
  increasing      = 1.5 %/y | grow 0.15 %/y yearly from operations.start
  peso_spread     = 2.0 %/y
  discount_rate   = 6 %/y                 // nominal, for LCOE
}

energy.rfn

module energy "Energy" {
  /// P90 and P99 come from the P50 and the 6% uncertainty
  direct: kWh/kWp/y = pvalue(inputs.p50_direct, sd: inputs.yield_sd, level: inputs.yield_case)
  yield:  kWh/kWp/y = direct * inputs.performance[inputs.yield_case]     // P50: 1,615.4

  capacity: MWp = inputs.capacity * decline(inputs.degradation)
  output:   MWh = capacity * yield * operations.share         // first period may be partial
}

capex.rfn

function gross_up(base, rate) = base * rate / (1 - rate)

module capex "Capital expenditure" {
  epc_per_kw:  USD/kW = sum(inputs.epc)                                   // 708.58
  dev_per_kw:  USD/kW = gross_up(epc_per_kw, inputs.development_share)   // 37.29

  development: USD = spread(dev_per_kw * inputs.capacity, over: development, profile: even)
  epc:         USD = spread(epc_per_kw * inputs.capacity, over: construction, profile: even)
  total:       USD = development + epc                                     // 70,350k

  funding total {
    first inputs.funding_order {
      debt    share inputs.max_gearing
      equity  rest
    }
  }
}

operations.rfn

module ops "Revenue and operating costs" {
  tariff:  USD/MWh = inputs.tariff | grow inputs.tariff_escalation yearly from operations.start
  revenue: USD     = energy.output * tariff

  om:        USD = inputs.capacity * inputs.om
                 | grow inputs.om_escalation yearly from operations.start | during operations
  insurance: USD = sum(capex.total) * inputs.insurance | during operations

  when inputs.include_inverters == yes {
    replacements: USD/kW   = inputs.inverter_cost | grow 1.5 %/y | every 10y from operations.start
    inverters:    USD      = inputs.capacity * level(replacements, rate: 10 %/y) | during operations
  }

  opex:   USD = om + insurance + inverters
  ebitda: USD = revenue - opex
  wc = working_capital(revenue: revenue, costs: opex,
                       debtor_days: inputs.debtor_days, creditor_days: inputs.creditor_days)

  depreciation: USD = sum(capex.total) / inputs.depreciation_life | during operations
  tax:          USD = max(0, ebitda - depreciation - debt.interest in USD) * inputs.tax_rate
}

debt.rfn

module debt "Debt" {
  period tenor = operations.start for inputs.tenor
  ccy = inputs.debt_currency                          // MXN in the base case

  base_rate: %/y = match ccy { MXN => inputs.mexico_interbank,  USD => inputs.libor_fixed }
  spread:    %/y = match ccy {
    MXN => inputs.peso_spread
    USD => match inputs.spread_case { increasing => inputs.increasing,
                                      flat => inputs.spreads[flat], high => inputs.spreads[high] }
  }
  all_in: %/y = base_rate + spread

  /// Lenders size on both cases at once: the lowest of the three wins
  cfads_p50: USD = cash.cfads
  cfads_p99: USD = cash.cfads under p99
  by_gearing: USD = inputs.max_gearing * sum(capex.total)
  by_p50:     USD = npv(cfads_p50 / inputs.dscr_p50 | during tenor, rate: all_in)
  by_p99:     USD = npv(cfads_p99 / inputs.dscr_p99 | during tenor, rate: all_in)
  solve size: USD = min(by_gearing, by_p50, by_p99)

  balance loan: ccy {
    + capex.debt in ccy
    - principal
  }
  interest:  ccy = loan.opening * all_in
  principal: ccy = clamp((cash.cfads in ccy) / inputs.dscr_p50 - interest, 0, loan.opening)
                 | during tenor
  debt_service: USD = (interest + principal) in USD
  dscr: x = cash.cfads / debt_service | during tenor

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

returns.rfn

waterfall cash "Cash flow" {
  start  ops.ebitda - ops.wc.change - ops.tax
  = cfads
  pay    debt_service  = debt.debt_service
  fund   debt.dsra
  keep   rest          as to_equity
}

module returns "Returns" {
  project_cash: USD = ops.ebitda - ops.wc.change - capex.total
  after_tax:    USD = project_cash - ops.tax

  /// The Summary Page shows every IRR three ways
  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))
  irr_after_tax = xirr(after_tax)
  equity_irr    = xirr(cash.to_equity - capex.equity)

  lcoe:      USD/MWh = lcoe(costs: capex.total + ops.opex, energy: energy.output,
                            rate: inputs.discount_rate)
  lcoe_real: USD/MWh = real(lcoe, by: inputs.macro.us_cpi, base: timeline.start)
}

dashboard {
  "Cost per kW"             sum(capex.total) / inputs.capacity
  "Project IRR pre-tax"     returns.irr_usd, returns.irr_mxn, returns.irr_real
  "Equity IRR"              returns.equity_irr
  "Min DSCR"                min(debt.dscr)
  "Debt to capital"         debt.size / sum(capex.total)
  "LCOE per MWh"            returns.lcoe, returns.lcoe_real
}

scenarios.rfn

scenario base "Base case (P50, peso loan)" {}

scenario p99 "Lenders' P99" {
  inputs.yield_case = p99
}

scenario dollar_loan "Dollar loan" {
  inputs.debt_currency = USD
  inputs.spread_case   = increasing
}

scenario inverters "With inverter replacement" extends base {
  inputs.include_inverters = yes
}

What this model needed from the language

Two lender cases at once. Debt is the lowest of 80% of cost, P50 CFADS at 1.35x and P99 CFADS at 1.00x. cash.cfads under p99 builds the P99 cash flows on a parallel sheet.
A unit chosen by a switch. The loan is in pesos or dollars. Declaring its lines in ccy makes every conversion follow the switch.
Statistics in the inputs. P90 and P99 come from the P50 and a 6% uncertainty through pvalue, which matches the workbook to the decimal.
A fee that includes itself. Development is 5% of a total that includes development. A function does the algebra, so there is no loop.
A levelised provision. The Inverter Replacement sheet becomes one level line with its NPV proof built in.
One sheet. sheets one keeps the single-sheet layout the modeller chose, with modules as titled sections.