Solar notes over six project loans

Based on community-solar-portfolio-model.xlsm

A securitisation. Six community-solar project companies in Massachusetts each carry a 20-year term loan. Two holding companies pool them, and an issuer sells Class A1 and A2 notes backed by the loans. The model sizes the loans, prices the notes, and works out yield, duration, average life and prepayment penalties. 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/notes/.

Library: E. Consolidating Separate Projects / Consolidated Debt Issues (6 files)
Lower tier: Six loans (Peru, Russell, Westport, Tolland, Westhampton, New Marlborough) in two holding companies; monthly transfers of a third of the next quarterly debt service
Upper tier: Note A1 USD 30m at 4.05% over 20 years; Note A2 USD 5m at 9.35% over 7 years; both priced at 110%
Fees: Servicing 0.10% and 0.05% of opening balance; accounting 125 a month; audit 10,000 a year; rating 25,000 up front and 5,000 a year
Rebuild shown: 5 files, 80 lines

Where each sheet goes

Workbook sheets	Rufineer
Inputs, UT Inputs	inputs.rfn and the Projects sheet of the data workbook
Peru, Russell, Westport, Tolland, Westhampton, New Marlborough	one module `for p in inputs.projects`
Sizing, Monthly, Quarterly, Annual	lower_tier.rfn on monthly and quarterly rulers
UT Cash Flows, UT Payment Schedule	upper_tier.rfn: two `bond`s and a waterfall
Pricing, Yield, Premium, Prepayment	pricing.rfn

Source 5 files shown, 80 lines

model.rfn

model "Solar Notes" {
  currency  USD
  data      "solar_notes_inputs.xlsx"
}

timeline {
  start            2021-01-01
  operations       20y
  ruler monthly    month                    // project control accounts
  ruler quarterly  quarter ends Jan, Apr, Jul, Oct   // loan and note payment dates (5th)
}

inputs.rfn

table projects {
  rows     project
  columns  holdco: text "Holding company", loan: USD "Loan amount",
           rate: %/y "Loan rate", revenue: USD/y "Year 1 revenue", opex: USD/y "Year 1 opex"
}

table solar_profile: %                  // Month | Share of yearly output

inputs "Notes" {
  prepay_date = 2030-01-05
  a1_par      = 30_000_000 USD
  a1_coupon   = 4.05 %/y
  a2_par      = 5_000_000 USD
  a2_coupon   = 9.35 %/y
  price       = 110 %
  lob_fee     = 0.10 %/y          // Live Oak Bank servicing, on opening balance
  rw_fee      = 0.05 %/y          // RW Renewables servicing
  accounting  = 125 USD/month
  audit       = 10_000 USD/y
  rating      = 25_000 USD
  surveillance = 5_000 USD/y
  retention   = 5 %               // vertical risk retention
}

lower_tier.rfn

/// One sheet per project company, as in the workbook
module project[p] "{p}" on monthly for p in inputs.projects {
  revenue: USD = inputs.projects[p].revenue * seasonal(inputs.solar_profile)
               * decline(0.5 %/y)
  opex:    USD = inputs.projects[p].opex | grow 2 %/y
  cash:    USD = revenue - opex

  loan = term_loan(amount: inputs.projects[p].loan, rate: inputs.projects[p].rate,
                   window: operations, profile: annuity, pay: quarterly)

  /// Each month, a third of the next quarter's debt service moves to the holding company
  sweep: USD = min(cash, loan.debt_service | next 3m / 3)
}

module holdco "Holding companies" on quarterly {
  for h in ["Pinecrest USFIN1", "Pinecrest USFIN2"] {
    collections[h]: USD = sum(project[*].sweep where inputs.projects[*].holdco == h)
  }
  loan_payments: USD = sum(collections[*])
}

upper_tier.rfn

module notes "Notes" on quarterly {
  a1 = bond(par: inputs.a1_par, coupon: inputs.a1_coupon, pay: quarterly,
            first_principal: 2022-01-05, maturity: 20y, price: inputs.price)
  a2 = bond(par: inputs.a2_par, coupon: inputs.a2_coupon, pay: quarterly,
            first_principal: 2022-01-05, maturity: 7y, price: inputs.price)

  servicing: USD = sum(project[*].loan.balance.opening) * (inputs.lob_fee + inputs.rw_fee)
  admin:     USD = inputs.accounting + inputs.audit + inputs.surveillance
                 + inputs.rating | at timeline.start
}

waterfall issuer "Issuer waterfall" on quarterly {
  start  holdco.loan_payments
  pay    fees            = notes.servicing + notes.admin
  pay    a1_interest     = notes.a1.interest
  pay    a1_principal    = notes.a1.principal
  pay    a2_interest     = notes.a2.interest
  pay    a2_principal    = notes.a2.principal
  keep   rest            as residual
}

pricing.rfn

module pricing "Pricing" {
  a1_yield    = notes.a1.yield           // at 110% of par
  a1_duration = notes.a1.duration
  a1_wal      = notes.a1.wal
  a2_yield    = notes.a2.yield

  /// Three prepayment penalty methods, as on the Prepayment sheet
  make_whole:        USD = notes.a1.prepay(at: inputs.prepay_date, method: make_whole,
                                           spread: 1.25 %/y)
  yield_maintenance: USD = notes.a1.prepay(at: inputs.prepay_date, method: yield_maintenance)
  step_down:         USD = notes.a1.prepay(at: inputs.prepay_date, method: step(5, 4, 3, 2, 1) %)

  retained: USD = inputs.retention * (inputs.a1_par + inputs.a2_par)
}

check "Notes repaid"       notes.a1.balance.closing == 0 at operations.end tolerance 1 USD
alert "Residual never negative" issuer.residual >= 0

What this model needed from the language

Bonds as components. bond gives interest, principal, yield, duration, average life and three prepayment penalty methods.
Two levels of debt. Project loans sit on a monthly ruler per project; notes sit on a quarterly ruler at the issuer, joined by the holding-company pooling.
Modules per project. Six project sheets from one module, grouped by holding company with where.
Fees on balances and fixed fees in the issuer waterfall, in the order the transaction description sets.