Compiler

What the compiler builds today

The compiler is written in Go and runs in your browser, compiled to WebAssembly, in the app; it builds everything on the Language page; the Rust and Ruby compilers that came before it are kept as the references it is tested against, model by model and value by value. The Planned language page is the design it is growing towards.

The worked examples use the planned language, so most of them don't compile yet; the first model, the three tutorials and the hotel do, and each is checked on every change by recalculating it in LibreOffice.

How it works, stage by stage (in Go): compiler/ reads the files (the lexer and parser), resolves names and units into a blueprint of the workbook, finds the circular references (circularity.go) and lays the sheets out (layout.go); eval/ calculates it, the way Excel will, for the preview; view/ is what the app's preview shows (the sheets, the audit, the problems), all of it running in the page; the lender-grade workbook and its macros are written by Rufineer's service, for the export. Every file is commented for reading.

Lender-grade models

The hotel example (the Hotel template in New model) is built to the standard a lender's model reviewer expects: a 120-room hotel, two years of construction, twenty years of operation. It has a cash waterfall with a debt service reserve account (DSRA) and a lock-up, an income statement, a cash flow statement and a balance sheet that balance every quarter, returns and cover ratios, a dashboard and seven sensitivities. Everything on this page compiles today, and the test suites recalculate the workbook in LibreOffice with every check passing.

Looking ahead: next

A DSRA usually holds the next two quarters of debt service. next(x) is the mirror of prev(x): the value in the next column, and 0 after the last period.

service_next: USD = next(debt.service)                 "Debt service next quarter"
dsra_target:  USD = service_next + next(service_next)  "DSRA target"

Looking ahead is safe on its own, and so is looking back, but not both in one loop: if a reads next(b) and b reads prev(a), Excel's column H needs column I, which needs column H. The compiler refuses such a loop.

Totals over a window

A DSCR is zero outside the repayment window, so min(dscr) would be 0. Give the window:

min_dscr: x = min(dscr, during: repaying)     "Minimum DSCR"
avg_dscr: x = avg(dscr, during: repaying)     "Average DSCR"

These become MINIFS, AVERAGEIFS, SUMIFS and MAXIFS over the row and the window's flag row. (MINIFS and MAXIFS need Excel 2019 or Microsoft 365.)

Returns: irr and npv

equity_irr: %/y = irr(equity_flow)                                   "Equity IRR"
equity_npv: USD = npv(inputs.cost_of_equity, equity_flow)            "Equity NPV"
llcr:       x   = npv(inputs.interest_rate, cfads_in_tenor, at: operations.start) / capex.total_debt

irr is Excel's XIRR, with each flow dated at the end of its period, so a quarterly model gives an annual rate with no conversion. npv is XNPV, valued at the model's start unless at: names another date: the compiler writes XNPV(…) * (1 + rate) ^ ((at - first date) / 365). A payback period needs nothing new: count the time before the cumulative cash turns positive.

still_to_recover: y = if equity_cumulative < 0 then year_frac else 0 | during operations
equity_payback:   y = sum(still_to_recover)          "Equity payback (years from opening)"

Balance sheets and cash flow statements

A statement's rows were always summed over each financial year. Balances need the value at the year's end instead:

statement balance "Balance sheet" | at year end {
  section "Assets"
  + accounts.building          "Building"
  + accounts.ffe               "Furniture, fixtures and equipment"
  + cash.dsra                  "Debt service reserve account"
  + cash.locked                "Locked-up cash"
  = total_assets               "Total assets"

  section "Liabilities and equity"
  + debt.loan                  "Senior debt"
  + accounts.share_capital     "Share capital"
  + accounts.retained_earnings "Retained earnings"
  = total_funding              "Total liabilities and equity"

  section "Check"
  + total_assets
  - total_funding
  = difference                 "Difference (0 when it balances)"
}

section starts a new block, and subtotals start again below it. A row can name an earlier row of the same statement (+ total_assets), and a row can have its own basis: the cash flow statement sums flows by year but takes opening cash | at year start. The balance sheet check itself runs every quarter, in the model, so an error shows in the quarter it happens:

check "Balance sheet balances"  accounts.total_assets == accounts.total_funding  tolerance 1 USD

The cash waterfall

A waterfall is ordinary lines: CFADS, less debt service, the DSRA topped up, drawn or released, a lock-up while the DSCR is below 1.15x, and what is left distributed. See the hotel example's cash module, and the annual waterfall statement that shows it year by year.

The dashboard

dashboard {
  section "Returns"
  returns.project_irr     "Project IRR (post-tax)"
  returns.equity_irr      "Equity IRR"

  section "Debt"
  debt.min_dscr           "Minimum DSCR"
  debt.llcr               "LLCR at opening"

  chart column "Revenue, EBITDA and net profit" {
    income.total_revenue  "Total revenue"
    income.ebitda         "EBITDA"
  }
  chart line "DSCR" { debt.dscr }
}

Each result is read live from its sheet (=Returns!$D$20); the Dashboard shows, it never calculates. Results must be single values; charts take lines that change over time: statement rows (one column per year) or module lines (one per period).

Sensitivities

sensitivities {
  results {
    returns.equity_irr   "Equity IRR"
    debt.min_dscr        "Min DSCR"
  }
  case "ADR −10%"            { inputs.adr * 90 % }
  case "Interest rate +1%"   { inputs.interest_rate + 1 %/y }
  case "Combined downside" {
    inputs.adr                  * 95 %
    inputs.stabilised_occupancy - 3 %
  }
}

How the workbook does it, without breaking a house rule:

• Every change is an input on the Inputs sheet ("ADR: multiply by 90%"), and a sensitivity case switch at the top of the sheet picks the active one (the base case, or a case by its title).
• A Scenarios sheet works out each changed input's value in the active case. The model's name for the input (inputs.adr) now means that value, so switching the case flows through everything.
• The RunSensitivities macro (in the workbook with Solve, behind a button) makes each case active, runs Solve, and pastes the results into a table on the Sensitivities sheet, next to each case's change from the base case. Every case is solved in full, so interest during construction and anything else in a solve loop follow the case. Excel data tables can't do that.
• An alert shows while a case other than the base case is active.

The hotel's results

A second example: a toll road

examples/express_fiji is a 40-year highway concession: four years of construction, 36 years of tolls, 65% senior debt at a fixed rate. Traffic is in vehicles per year and tolls in pounds per vehicle, so the compiler checks veh/y × GBP/veh. The arrangement fee, the commitment fee and interest during construction all depend on the size of the loan they help fund: three solve lines. The loan is repaid in level annual instalments, written as the annuity formula:

instalment: GBP = drawn * period_rate / (1 - (1 + period_rate) ^ -repayments)
                                                 | during in_repayment

Depreciation runs ahead of principal repayments, so cash builds up in the company that can't be paid as dividends. The model pays it back to the shareholders when the concession ends, in the equity IRR. With the heavy-vehicle toll at £19.81 (2023 prices) the equity IRR is 23%.

Editor support

The app's editor and preview are built on the same compiler as the export, so what they show is exactly what the workbook will contain.

What the compiler writes

Formulas use a fixed set of functions. OFFSET, INDIRECT, LAMBDA and dynamic arrays are not used, so the output stays auditable in any Excel version.

Error messages

Errors say what is wrong and how to fix it, in plain terms.

error[unit]: can't add USD and MWh
  --> revenue.rfn:4:18
   |
 4 | total: USD = energy.net + tariff
   |              ^^^^^^^^^^   ^^^^^^ USD/kWh
   |              MWh
   = help: did you mean `energy.net * tariff`?  (MWh × USD/kWh → USD)

error[circular]: these lines depend on each other
   funding.uses → debt.senior.draws → debt.senior.interest → debt.idc → funding.uses
   = help: break the loop with `solve`, for example
           solve uses: USD = construction.total + debt.idc | during construction

warning[time]: `inputs.om_fixed` is USD/y but `ops.fixed_opex` is declared USD
   → multiplying by year fraction (0.083 in monthly columns, 0.25 in quarterly)