The planned language

About this page

This is the design, not the manual. It describes what Rufineer is planned to become, including rulers, events, groups, waterfalls, scenarios and packages. Much of it doesn't compile yet. For what you can write today, see the Language page; the Compiler page lists what is built.

The design will change as it is built. The rule that shapes it: the language keeps a small, fixed set of words, and a new capability is a function or a library component unless it can't be one. Where this page shows new syntax for something a function could do, expect the function.

Timeline

The timeline sets the model's columns. Phases run one after another, in the order you write them.

timeline {
  start          2027-01-01
  fiscal_year    ends Dec
  construction   15m   step month        // monthly columns while building
  operations     25y   step quarter      // quarterly afterwards
}

Each phase gives you a set of names to use in formulas:

Column width

What Rufineer builds today: a step per phase, as in the example above: month, quarter, half (six-monthly) or year, written after the phase's length. step on a line of its own is the step of every phase that doesn't give one (a quarter if there is none). Each phase must be a whole number of its steps. The Timing sheet gets a Months in period row and the Inputs sheet each phase's months per period, so the columns change width at the phase boundaries; year fractions, escalation, profiles, the financial year and the statements all follow the dates. A change of step inside a phase (step month for 12m, then quarter) and detail rulers below are designs, not built yet; rfn refuses them rather than guessing.

timeline {
  start          2027-01-01
  development    6m    step month        // 6 monthly periods
  construction   18m   step quarter      // 6 quarterly periods
  operations     20y   step half         // 40 half-yearly periods
}

Set step once for the whole model, or per phase. Changing it per phase keeps a single ruler whose columns change width. Kestrel works this way: 15 monthly columns, then quarterly.

The step can also change part-way through a phase, or at a date. Coral Bay and Oriole run monthly until commercial operation and six-monthly after it; some lenders want the first operating year monthly. v0.4

operations   25y   step month for 12m, then quarter
step         month until operations.start, then half      // whole model, one switch point

The Mbira models use a different layout. Their main ruler is quarterly, and construction also has its own monthly sheets. detail gives you that layout:

timeline {
  start          2024-10-01
  step           quarter
  construction   36m   detail month      // adds a monthly ruler for construction only
  operations     until construction.start + inputs.concession
}

module cons_cost "Construction costs" monthly { … }

A monthly module lives on the detail ruler. When a quarterly module reads one of its lines, the compiler adds a Timeline aggregation row. Flows are summed into the quarter, and balances take the value at quarter end.

Stages: several assets with their own dates v0.4

A phased build-out has one set of phases per stage. Oriole builds three solar phases, each with its own financial close, construction period and life. Declare the stages as a list, give each one its phases, and every phase name takes an index.

list stages = inputs.stages.rows                  // phase_1, phase_2, phase_3

timeline {
  start   2019-01-01
  step    month until operations[phase_1].start, then half
  for s in stages {
    construction[s]  from inputs.stages[s].financial_close for inputs.stages[s].build
    operations[s]    inputs.stages[s].life
  }
}

module phase[s] "Phase {s}" for s in stages { … }  // one sheet per stage

construction[s], operations[phase_2].year and the other phase names work as before. A module declared for s in stages becomes one sheet per stage, and sum(phase[*].revenue) adds them up.

Windows

tenor     = window(from: operations.start, for: inputs.tenor)
grace     = window(from: operations.start, for: 6m)
repayment = window(from: grace.end, to: tenor.end)
open[g]   = window(from: inputs.gate_open[g], to: operations.end)   // one per toll gate

Rulers

Most lender models run on more than one time ruler: operations monthly, the debt six-monthly on the payment dates, tax and statements yearly. v0.5 A ruler names each one, and a module says which ruler it lives on.

timeline {
  start          2025-01-01
  construction   34m
  operations     38y
  ruler monthly  month                       // traffic, revenue, operating costs
  ruler finance  half  ends May, Nov         // debt service falls in May and November
  ruler annual   year  ends Dec              // tax and financial statements
}

module traffic "Traffic and revenue" on monthly { … }
module debt    "Senior debt"         on finance { … }
module tax     "Tax"                 on annual  { … }

When a line crosses from a shorter ruler to a longer one, the compiler writes the aggregation row for you, using the line's unit to decide how:

A module without on uses the first ruler listed. Going the other way, from a longer ruler to a shorter one, always needs a word: | spread even spreads the amount over the shorter periods, | at end puts it in the last one, and | hold repeats a rate or a flag. The Mbira detail month layout is a ruler that only covers construction. A step day ruler covers daily loans, such as the daily bond in the library.

Values and units

16_000_000 USD     9 USDc/kWh     98.5 %     6.12 %/y     1.30 x
2027-01-01         15m   25y   30d   6w         Q3 2028      FY2030

• % is a plain number: 98.5 % is 0.985. %/y is a rate per year.
• Units multiply and cancel: MWp × kWh/kWp/y × y gives MWh. Units with the same dimension convert automatically (MWh and kWh, km and m, USDc and USD), and the factor appears in the formula.
• Time rule: when a formula gives X/y and the line is declared as X, the compiler multiplies by year_frac. With monthly then quarterly columns, the right fraction is used in each column. Rates per day or per month convert the same way, using period.days or the months in the period.
• Rates per year used on shorter periods are simple by default (r × year_frac). Write effective(rate) to compound instead, (1+r)^year_frac − 1: a 12%/y hurdle is 2.87% a quarter, not 3%, so a hurdle balance agrees with XIRR.
• A number without a unit scales anything, but it can't be added to money: revenue + inputs.storeys and revenue + 500 are errors (write 500 USD). A bare 0 or 1 fits anywhere, as in max(0, cash) and 1 + growth.
• Declare your own units: unit vehicle, unit oz = 31.1035 g.
• Solar plants are sized in peak and AC watts: MWp, kWp and MWac are power like MW, so 5.5 USD/kWp/y × 48 MWp is money per year.
• Real estate units: floor and land area in sqm, sqft, ha and acre (they convert; not m2, because m is a month), and counted things: unit (apartments, houses), lot, space (parking), room or key, and bed. A night is a day, so 120 key × 70 % × 95 USD/room/night is money per day and is converted to each period.
• A date plus or minus a duration is a date: operations.start + 12.5y is EDATE(start, 150), and + 30 d adds days. A date minus a date is a number of days.

Day count v0.3

model "Kestrel Energy 45 MWac" {
  day_count  act/365                       // act/365 | act/360 | 30/360 | months
}

senior_interest: USD = senior.opening * all_in | day_count act/360   // one facility differs

year_frac follows the model's day-count convention. A line can use a different one, which lenders often ask for on a single facility. The Timing sheet gets one year-fraction row per convention in use, labelled with its name.

Currencies

currency USD                                   // base currency, in model {}
currency ZAR, RMB, ZiG                         // others the model uses
fx ZAR/USD = inputs.rates.zar_usd                // a column of an Excel table

epc: USD = sum(inputs.epc[*].cost in USD)      // converted at each period's fx

Adding amounts in two different currencies is a compile error until one of them is converted with in.

// FX forecast from inflation differentials, times any real change    v0.4
fx MXN/USD = ppp(17.53, local: inputs.macro.mx_cpi, foreign: inputs.macro.us_cpi)
           * grow(inputs.macro.real_fx_change)

// a unit chosen by a switch: the loan is in pesos or dollars
debt_currency = option MXN | USD
balance loan: inputs.debt_currency { + funding.debt in inputs.debt_currency  - principal }

// returns in real terms
project_irr_real = xirr(real(project_cash, by: inputs.macro.us_cpi, base: timeline.start))

Inputs

Everything in an inputs block goes to the Inputs sheet in input styling. Each block becomes a titled section. Formulas read inputs as inputs.name.

inputs "Commercial" {
  // constant
  tariff        = 9 USDc/kWh                   @source "Tamarind Gold PPA"

  // constant that escalates
  om_fixed      = 40_000 USD/y  | grow 2 %/y from timeline.start

  // time series; ".." holds the last value
  base_rate     = series by year { 2020: 2.00, 2021: 2.25, 2022..: 2.50 } %/y
  margin        = series by operations.year { 1..3: 2.00, 4..6: 1.75, 7..: 1.50 } %/y

  // choice: a dropdown in Excel
  yield_case    = option p50 | p75 | p90
}

A short keyed list can stay on one line: yield = { p50: 2_300, p90: 2_070 } MWh/y. Anything with rows and columns lives in the data workbook (next section).

Inputs on a sheet v0.3

An inputs block can also take its values from the data workbook. You declare each input's name and unit in Rufineer; the number lives on a sheet with an Input column and a Value column. A sponsor or technical adviser can then change an assumption without opening the source.

inputs "Plant" sheet "Plant" {
  capacity_ac:        MWac     "AC capacity"
  capacity_dc:        MWp      "DC capacity"
  grid_availability:  %
  plant_availability: %
  tariff:             USDc/kWh "Single set tariff"
}

Rows are matched by the quoted label, or by the name when the label is left out (grid_availability finds Grid availability). Other columns on the sheet, such as Unit or Source, are ignored, and Add Missing Data Sheets writes the sheet for you with every label already filled in. An input can still carry | grow or @source as usual.

Optional tags on any input: @source "…" is printed beside the input and listed in a sources appendix, @note "…" becomes a cell comment, and @range 0%..100% becomes a data-validation rule.

Tables from Excel

Each model has one data workbook, named once in the model block. Every table in the model is a sheet in that workbook. You type, paste and check the numbers in Excel, and the Rufineer source says what each sheet means.

model "Mbira Toll Road" {
  currency  USD
  data      "toll_inputs.xlsx"
}

/// 2018 classified counts on the approach to each gate
table aadt: vehicle/day {
  rows     gate "Toll gate"
  columns  light "Light motor vehicle", minibus, bus, heavy, haulage,
           abnormal "Abnormal load"
}

Read it aloud: table AADT, in vehicles per day. Its rows are gates, and its columns are vehicle classes.

• The table name finds the sheet. aadt reads the sheet AADT, and spend_curve reads Spend curve. Case, spaces and underscores don't matter. When the names differ, say which sheet: table toll: USD/vehicle sheet "Tolls".
• Row 1 holds the headers. Data runs down to the first empty row. The sheet tab is the title, so there is nothing to skip.
• rows names the column that labels each row. Labels become names: Chidza becomes chidza. aadt.rows is the list of gates.
• columns lists the columns the model uses. Quote the header only when it differs from the name (minibus finds Minibus). The unit after the table name covers every column, and a column can set its own: length: km "Length (km)". Text and date columns are marked text and date.
• Other columns are ignored. Column B above (Side) isn't listed, so notes, sources and workings can sit beside the data.
• Time series. columns by year reads years across the top. A sheet whose rows are years or dates is matched to the model's periods.
• Shortest form. table spend_curve: % with no braces takes column A as the rows and names every other column from its header.

Use a table as aadt[chidza, heavy], gates[g].opens or products.length (a whole column). Arithmetic between columns runs row by row, and sum adds up the rows. A table with one value column reads directly: growth[heavy].

Let Rufineer make the workbook

$ rfn data

Write the table lines first, then run rfn data (or Add the missing sheets in the app's workbook view). It adds one sheet per table with the headers already in row 1, in input styling, plus a Contents sheet that lists each sheet, its unit and the Rufineer line that reads it. Existing values are never touched. If you rename a column in Rufineer, the old header is flagged rather than overwritten. The order of work is: declare, rfn data, fill in the numbers, rfn check.

At build time

• The compiler opens the workbook read-only, checks every header, row label and value, and copies the numbers into the model's Inputs sheet in input styling. Each block carries its source: toll_inputs.xlsx › AADT. The workbook you send to lenders does not depend on the data file.
• Write data "toll_inputs.xlsx" linked to keep live external links instead.
• rfn.lock records a fingerprint of every sheet. The preview's Problems tab lists the sheets that changed since the last build, down to the cell.

error[data]: no sheet for table `spend_curve` in toll_inputs.xlsx
   = found: Contents, Tolls, AADT, Growth, Gates, Construction fees, Spend curve (monthly), …
   = help: add  sheet "Spend curve (monthly)"  to the table line

error[data]: column "Heavy vehicles" not found on sheet AADT
  --> inputs/traffic.rfn:11:12
   = found: Toll gate, Side, Light motor vehicle, Minibus, Bus, Heavy, Haulage, Abnormal load
   = help: did you mean "Heavy"?

error[data]: AADT!F4 is text ("225 "), expected a number
   = help: remove the trailing space, or add `blank as 0` to the table to allow empty cells

Two small tables can share a sheet if you need them to. Point at the header row: table growth: %/y sheet "Traffic" at A10.

Lines

/// Doc comment: becomes the row description in Excel
name: unit = expression | modifier | modifier    "Optional label"

• The unit is optional when it can be worked out. Declare it when you want the check, or the time conversion.
• The row label is the name in sentence case (profit_before_tax becomes Profit before tax) unless you give one.
• The compiler works out whether a line is a constant or a time series. A line that reads any time series is a time series.

Modifiers

Read the | as “then”.

Choices and conditions

fee = if drawdown > 0 then 2 % * drawdown else 0

principal = match inputs.profile {
  level_principal    => size / count(repayment)
  sculpted           => cfads / target_dscr - interest
  level_debt_service => pmt(all_in, periods_left(repayment), senior.opening) - interest
}

// with no subject, the first true condition wins
fee = match {
  construction.year == 1 => inputs.fee[construction_year_1]
  operations             => inputs.fee[operations]
}

Switched blocks v0.4

dsra_mode = option funded | letter_of_credit

when inputs.dsra_mode == funded {
  dsra = reserve(target: debt.debt_service | next 6m)
}
when inputs.dsra_mode == letter_of_credit {
  lc_fee: USD = (debt.debt_service | next 6m) * inputs.lc_fee_rate
}

Everything inside a when block is built either way and multiplied by the switch, so rows never appear or disappear when someone flips an input. Reading a switched-off line gives zero.

Functions v0.4

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

/// A development fee of 5% of total cost, where total cost includes the fee. No loop needed.
development: USD = gross_up(epc.total, inputs.development_share)

A function is written out in full wherever it is used. It never becomes a VBA function or a LAMBDA, so the workbook stays readable in any Excel.

The escape hatch v0.4

special: USD = excel "=MAX(0,{rev}-{cost})*{flag}"
               with rev: ops.revenue, cost: ops.opex, flag: operations

For the rare formula Rufineer cannot express, write the Excel yourself. Each {name} becomes a cell reference on the same sheet, imported like any other line, so the FAST rules still hold. The preview's Problems tab lists every excel line, so reviewers know where to look.

Time functions

Events and bands

v0.5 An event is the first period where something becomes true. Upstream contracts pay bonuses when cumulative production passes a threshold. A tax equity partnership flips when the investor has earned its target return. A plant is overhauled every 25,000 running hours.

event first_oil   = first(production > 0 bbl)
event bonus_25    = first(cumulative(production) >= 25 MMbbl)
event flip        = first(earned(partnership.tax_investor.after_tax, inputs.flip_yield))

bonus: USD = 5_000_000 USD | at bonus_25
overhaul: USD = inputs.overhaul_cost | every 25_000 h of running_hours

Each event gives flip.date, flip.flag (1 in the period it happens), flip.before and flip.after. earned(cash, rate) is true once the cash flows have reached that return. It keeps a balance that grows at the rate and falls with each distribution, the same way tax equity models track a flip, so no running IRR is needed. | every N of line repeats an amount each time a cumulative quantity passes another multiple of N.

Bands and tiers

/// Contractor's share of profit oil falls as the R-factor rises
profit_share = bands(r_factor, { ..1.0: 50, 1.0..1.5: 45, 1.5..2.0: 40,
                                   2.0..2.25: 30, 2.25..2.5: 20, 2.5..: 15 } %)

/// Royalty charged slice by slice, like income tax brackets
royalty: USD = tiered(production, rates: { ..10_000 bbl: 5 %, 10_000 bbl..: 12.5 % }) * price

/// Insurance steps up with the ship's age
insurance: %/y = bands(ship.age, { ..10y: 0.2, 10y..15y: 0.3, 15y..: 0.4 } %/y)

bands applies one rate to the whole amount, chosen by the band the driver falls in. tiered charges each slice at its own rate. The bands can also come from a sheet of the data workbook, one row per threshold.

Balances

A balance is anything that carries forward from one period to the next: a loan, a reserve account, fixed assets, tax losses, cash.

balance loan: USD {
  + drawdown
  - principal
}

This builds the FAST corkscrew: an opening row (equal to last period's closing), one row per flow, and a closing row. Use loan.opening, loan.closing and loan.drawdown anywhere.

balance shl: USD {
  start 0
  + cons.shareholder_loan
  + capitalised
  - cash.shl_principal
  floor 0                // adds a check that it never goes negative
}

Recovering up to a cap v0.5

balance cost_pool: USD {
  + capex + opex
  - recovered
}
recovered: USD = min(cost_pool.available, inputs.cost_oil_cap * revenue)

.available is the opening balance plus this period's additions, before anything is taken out. It covers production-sharing cost recovery, tax losses used up to a limit, deferred fees paid when cash allows, and catch-up payments.

Groups

A for loop writes one line per item: one per asset class, gate, vehicle class or debt tranche.

list gates    = [chidza, tonde, nyuni, pfuma]
list vehicles = [light, minibus, bus, heavy, haulage, abnormal]

for g in inputs.active_gates, v in vehicles {
  revenue[g, v]: USD = count[g, v] * fee[v]
}
total: USD = sum(revenue[*, *])

v0.5 A loop can filter its items: for p in inputs.projects where inputs.projects[p].include == yes. A module can loop too, which gives one sheet per item (one per cell tower, rooftop or battery lease) and a consolidation with sum(site[*].ebitda). Sums can filter too: sum(project[*].sweep where inputs.projects[*].holdco == h).

In Excel this is one row per pair, plus a total. If a scenario changes active_gates, every loop that reads it changes with it.

Funding

A funding block says who pays for the uses of funds, and in what order. Read it top to bottom: first one source, then the next.

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
}

A step can share each period's need between several sources. Oriole draws 40% of the equity up front, then shares the rest with the lenders pro rata:

funding uses {
  first  equity  up to inputs.upfront_equity * equity.commitment
  then   pro_rata {
    senior     share inputs.senior_gearing
    mezzanine  share inputs.mezzanine
    equity     rest
  }
}

v0.5 Grants and subsidies are sources like any other: then grant share 20 %, or first grant up to inputs.grant for a fixed amount.

What Rufineer builds today: a first pro_rata { … } step, whose sources each take a share of every period's need, optionally up to a limit; then then name up to limit sources, each paying what is still needed up to its own limit, in order; and one rest source, last. A source with a limit gets a drawn to date row, so each period sees what is left of the limit. Tutorial 2 uses all of it:

funding uses {
  first pro_rata {
    senior  share senior_share  up to debt.senior_size
  }
  then pref_a    up to inputs.pref_a
  then pref_b    up to inputs.pref_b
  then shl       up to inputs.shl_size
  then ordinary  rest
}

Rules are pro_rata, back_ended (debt first, equity last, usually backed by a letter of credit) and equity_first. The rule can be an input, so a switch on the Inputs sheet picks it: first inputs.funding_order { … }. Each source becomes a line such as funding.senior, and a check confirms that sources equal uses.

Waterfalls

A waterfall pays items in priority order out of the cash available. Each tier produces three rows: cash available, amount paid, and cash left after.

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

export cash by period, by half, by year

• pay name = due pays min(available, due). Any unpaid amount is kept as cash.shortfall.name.
• fund reserve tops a reserve up to its target and takes releases back.
• = name is a subtotal, for example cash.cfads.
• add brings in extra cash, such as interest income. move records a transfer to or from an account, such as a maintenance reserve.
• unless flag and when flag switch a tier off or on.
• export … by half, by year writes extra summary sheets. Kestrel's CFW, Bi-annual CFW and Annual CFW sheets all come from one waterfall.

Sharing cash between partners v0.4

split to_equity {
  main_investor  95 %
  edm             5 %   carried          // receives its share, funds nothing
  funded by      equity_draws
}

split to_equity {                          // a promote above a hurdle
  first  investors 100 %               until investors earn 12 %/y
  then   investors 80 %, developer 20 %
}

split divides one cash line between partners. to_equity.edm is EDM's cash, and xirr(to_equity.main_investor) its return. A carried partner receives distributions without putting in capital, so the other partners fund all of equity_draws. A hurdle tier keeps a balance that grows at the hurdle rate and switches to the next tier once it is paid off.

Statements

statement pl "Income statement" | sum by year {
  + ops.revenue
  - ops.opex
  = ebitda
  - tax.depreciation
  - debt.interest
  = profit_before_tax
  - tax.payable
  = net_profit
}

statement bs "Balance sheet" | at year_end { … }

A + or - line is a signed item. = name is a running subtotal. Flow statements use sum by year and balance sheets use at year_end. Statements with the same lines can be added together, which is how the combined Mbira model is built.

Deal structures

v0.5 Some structures appear across whole families of models. Each is built from the pieces above, and the standard library wraps the common ones.

Partnerships and tax equity

split partnership among tax_investor, sponsor {
  capital = equity.contributions    tax_investor 60 %
  cash    = cash.distributable      tax_investor 95 % until flip, then 15 %
  income  = tax.taxable_income      tax_investor 95 % until flip, then 15 %
  credits = tax.ptc                 tax_investor 95 % until flip, then 15 %
}

event flip = first(earned(partnership.tax_investor.after_tax, inputs.flip_yield))

investor_account = capital_account(partnership.tax_investor,
                                   dro: inputs.dro, itc_basis: 50 %)

Each row splits one line between the parties, and the last party takes the rest. partnership.tax_investor.cash, .income, .credits and .after_tax are ready to use. Tax attributes such as income, losses and credits are split the same way as cash. capital_account keeps the 704(b) account, with the deficit restoration obligation and minimum gain.

Bonds and notes

a1 = bond(par: 30_000_000 USD, coupon: 4.05 %/y, pay: quarterly,
          first_principal: 2022-01-05, maturity: 20y, price: 110 %)

a1.yield        // yield to maturity at the issue price
a1.duration     // modified duration
a1.wal          // weighted average life
a1.prepay(at: 2030-01-05, method: make_whole, spread: 1.25 %/y)

Refinancing

refi = refinance(senior, at: operations.start + 5y,
                 with: sculpted_loan(rate: 5.5 %/y, window: 15y, cfads: cash.cfads, dscr: 1.25 x),
                 fees: 1 %)

refinance repays the old loan from the new one on the date and moves the fees into the uses. Mini-perms use profile: balloon(40 %) or bullet with a cash sweep in the waterfall.

Leases

lessor = lease(cost: capex.total, term: 20y, residual: 20 %,
               yield: 7 %/y, tax_benefits: tax.itc + tax.depreciation_shield)
rent: USD = lessor.rent          // solved in closed form for the lessor's yield

Forward-looking values

/// Reserve-based loan: redetermined every six months
borrowing_base: USD = 45 % * pv_ahead(net_revenue, rate: 8 %/y) | sampled every 6m
required_repayment: USD = max(0, rbl.opening - borrowing_base)

llcr: x = pv_ahead(cash.cfads | during tenor, rate: all_in) / loan.opening
plcr: x = pv_ahead(cash.cfads, rate: all_in) / loan.opening

pv_ahead(x, rate:) is, in each period, the present value of that line's remaining flows. It covers LLCR, PLCR, borrowing bases, going-concern values and terminal values. | sampled every 6m keeps a value fixed between redeterminations.

Circularity and targets

Project finance models have loops: interest during construction depends on the debt, and the debt depends on the funding need. FAST breaks these loops with copy-paste macros. In Rufineer you write the loop as a solve line, which looks like = but breaks the loop.

solve idc:  USD = debt.interest | during construction
solve size: USD = min(by_gearing, by_dscr)   tolerance 1 USD

Each solve line becomes three rows:

1. Calculated: the live formula.
2. Pasted: the values the rest of the model reads.
3. Difference: calculated minus pasted. The compiler adds a check on it by itself, … solved (run the Solve macro), so the master check fails until the Solve macro has been run.

The compiler also writes one Solve macro. It copies calculated into pasted until every difference is within tolerance, and a button for it goes on the Dashboard. Any loop that is not broken by a solve is a compile error, and the error shows the full path of the loop.

How the loop is broken v0.5

solve idc: USD = debt.interest | during construction | by paste       // default: FAST copy-paste
solve idc: USD = debt.interest | during construction | by function    // a VBA function iterates in memory
solve fee: USD = 2 % * uses                          | by algebra     // the compiler finds the closed form

by paste is the FAST default: pasted values, a Solve button and a check. by function writes the loop as a VBA function, like the parallel models in the library. The workbook then recalculates live with no button, which suits sensitivities and data tables. by algebra asks the compiler for a closed form (fee gross-ups, IDC at a flat rate, sculpting with fees and DSRA) and fails with an explanation if it has none.

Seeking a target

bid_toll = seek(inputs.toll, returns.equity_irr == inputs.target_irr)  "Bid toll"

Built: see Meeting a target on the Language page. seek changes one input until a target is met: the bid tariff, the availability payment, the gearing at a minimum DSCR. It is a function, not a keyword (an earlier sketch was seek … so that …), and the Solve macro does the search, settling the loops at each try, with a check that the target is met; never Excel's Goal Seek dialog. Still to come: algebra when the target is linear in the input (an availability payment is a PMT), and seeks that move together (a toll and a gearing) solved as one system.

Where a closed form exists, use it. A sculpted repayment of cfads / dscr − interest needs no solve, because interest is charged on the opening balance. Sizing the loan on the DSCR is another matter: the size is the present value of cfads / dscr, and the fees, the IDC and the DSRA that the loan pays for depend on the size, so that loop does need a solve.

Scenarios and results

scenario base "Base case" {}

scenario disaster "Disaster" {
  inputs.price_flex = -10 %
  inputs.opex_flex  = -10 %
  inputs.rate_flex  = +1 %/y
}

scenario stress "Stress" extends disaster {   // v0.4: start from another case
  inputs.yield_case = p90
}

sensitivity inputs.capex_flex, inputs.opex_flex  [-10%, +10%]

Each scenario becomes a column on the Scenarios sheet, and one selector cell picks the active case. The Inputs sheet shows Base and Live side by side, and the model only reads Live. There is one copy of the model and no data tables.

results "Scenario results" {
  across  all scenarios
  show    equity.irr, min(debt.dscr), sum(cash.cfads)
}

results "Payback: aluminium price vs stock turn" {
  across  inputs.material_price[al_rod] = [3_000, 3_500, 4_000] USD/t
  by      inputs.stock_turn             = [4, 6, 8] w
  show    valuation.payback
}

results "Development value" {                // v0.5: probability-weighted cases
  across  scenarios weighted { base: 60 %, delayed: 25 %, abandoned: 15 % }
  show    returns.npv
}

A results block is filled by a macro. The macro runs each case, then pastes the outputs into a table stamped with the date and a fingerprint of the model. v0.3 If the model changes after the run, the fingerprint no longer matches and a built-in check flags the table as stale until the macro is run again. scenario(debt).size reads a value saved from another case's run. The training model uses this to size debt in the debt case and keep that size fixed in the equity cases.

Keeping the base case's value: base_case(…)

senior_size: USD = base_case(inputs.gearing * capex.project_cost)  "Senior loan"

A lenders' sensitivity asks what happens to this financing if things go worse: the loan has been signed, and a capex overrun doesn't resize it. base_case(…) around a calculation keeps, in every sensitivity case, the value the line had in the base case. The compiler writes three rows: the live formula, the base case's value and the line itself, IF(active case = 0, live, base case). The Solve macro stores the base case's value whenever it runs with the base case active (Run sensitivities runs the base case first), and a check fails until the stored value is up to date. It works for a one-number line (a debt size) and for a whole row (a sculpted repayment profile). In a model without sensitivity cases it changes nothing. This is what scenario(debt).size below does for named scenarios.

Two cases at once: under v0.4

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

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(inputs.max_gearing * capex.total, by_p50, by_p99)

Lenders often size debt on two cases together: P50 at 1.35x and P99 at 1.00x in Coral Bay. x under p99 is the same line computed with that scenario's inputs. The compiler copies only the lines it needs onto a Parallel: P99 sheet, next to the live model. This replaces hand-built parallel models such as the one on Oriole's Parallel Model sheet.

Dashboards and snapshots

dashboard {
  "Equity IRR (post-tax)"  returns.equity_irr_post
  "Min senior DSCR"        min(debt.senior.dscr | during debt.tenor)
}

snapshot "Valuation at key periods" {
  at    commissioning: operations.start,  year_1: operations.start + 1y
  show  valuation.nav, valuation.going_concern
}

A dashboard lists headline outputs. Add columns to show the same rows for several models side by side. A snapshot reads lines at chosen dates.

Checks and alerts

check "Balance sheet balances"  statements.bs.difference == 0   tolerance 1 USD
check "Senior debt repaid"      debt.loan.closing == 0 at debt.tenor.end

alert "DSCR below lock-up"      debt.dscr >= inputs.lockup_dscr | during debt.repayment

A check is a warning that something in the model is wrong. An alert is a business result someone should see, such as a covenant breach. Both go on the Checks sheet with a master flag, and the master check appears in every sheet header.

tolerance says how far apart the two sides may be and still pass; without it they must agree to a millionth. The same word on a solve line sets how close its calculated and pasted rows must be (1 unit of money by default).

Some checks are added for you: every solve line gets one that fails until the Solve macro has been run, every base_case line one that fails until the base case's values are stored, and every profile one that it adds up to 100% as laid out along its phase (a table longer than the phase loses its last values, and the check says so).

Components and packages

component term_loan(amount: USD, rate: %/y, window: period, dscr: x, cfads: USD) {
  balance loan: USD { + draw  - repay }
  draw:     USD = amount | at window.start
  interest: USD = loan.opening * rate
  repay:    USD = clamp(cfads / dscr - interest, 0, loan.opening) | during window
}

senior = term_loan(amount: debt.size, rate: debt.all_in,
                   window: debt.tenor, dscr: 1.30 x, cfads: cash.cfads)
// senior.interest, senior.loan.closing …

v0.4 A component is used like a function call: senior = term_loan(…). This replaces the v0.3 form use term_loan as senior(…). Inside a loop, index the name: loan[t] = term_loan(…).

The standard library (std) includes sculpted_loan, reserve, reserve_facility, vat_facility, working_capital, depreciation (straight line, reducing balance, MACRS, ZIMRA allowances) and tax_losses (with expiry). A new user can build a complete model from library blocks.

The compiler builds two components today, each written as a whole line:

losses = tax_losses(profit: taxable)
plant  = depreciation(cost: capex.total_cost, life: inputs.asset_life, from: operations.start)

depreciation is straight line with the book value kept as a corkscrew: a cost that is one number is added in the first period that starts on or after from, a cost that is a line (capex as it is spent) as it comes. Each amount is written off over its own life from the period it is placed in service, so capex added later (a replacement, a second phase) starts its own life and doesn't speed up the older cost. Each period's charge is MIN(cost within its life × months in period / life in months, opening + added), where the cost within its life is trailing(placed in service, life): counting months makes a 20-year life exactly 240 months of charges whatever the day count, and the MIN keeps the book value from going below zero. plant is the charge; plant.book_value, plant.opening, plant.added, plant.cost_to_date, plant.in_service and plant.within_life are its other rows. Use it for tax allowances too, with their own life: the difference between the two book values is what deferred tax is charged on.

Packages

package concession {
  requires  rev.total: USD, cons_cost.total: USD, ops.maintenance: USD
  provides  cons_fin, debt, reserves, equity, tax, cash, metrics, statements, checks
}

model "Mbira Border Post" {
  include "../concession"
}

A package is a folder of modules with a stated interface. The toll road and border post share one, so a fix to the debt logic reaches both.

Portfolios

portfolio "Mbira Combined" {
  include "../toll"   as toll    prefix "Toll_"
  include "../border" as border  prefix "Brdr_"
}

Reconciling a rebuild

When you rebuild an existing workbook, you need proof that the new model matches it. A reconcile block names the lines to compare.

reconcile against "kestrel-lender-model.xlsm" {
  cash.cfads                   ~ CFW       "Cash flow available for debt service (CFADS)"
  debt.senior.balance.closing  ~ Workings  "Senior debt balance c/f"
  tolerance 1 USD
}

When a label appears more than once on a sheet, add which one you mean: ~ Valuation "Payback period" [2] takes the second. ~ Workings row 240 matches by row number when there is no usable label. v0.3

At build time the compiler finds each row in the original by its sheet and label and copies its stored values into a Reconcile sheet. Each value sits beside the matching live formula, with a difference row and a check. Open the new workbook in Excel: when every difference is zero, the rebuild matches.

Styling and documentation

style {
  font      "Calibri"
  primary   #1B5E35
  accent    #C9A050
  inputs    fill #FFF2CC  font #0000FF
  imports   font #0000FF
  number    "#,##0;(#,##0);–"
}

/// comments become the row description column. // comments stay in the source. @source tags are printed beside inputs.