# Formula language

> The complete Layerz formula language, operators, functions, time lags, list subscripts, cross-scenario references and reserved period tokens.

This is the same specification the MCP server gives agents (`formula-spec://reference`).

The exhaustive set of operators, functions and reference syntax accepted in an
item's `formula`. Anything not listed here is **not**
supported: there is no `LN`, `EXP` or `AVERAGE` (use `AVG`).

References are by item **name** (back-tick names with spaces or special chars, e.g.
`\`Closing Debt\``). `inputs[]` is auto-derived from the formula at compile time: you don't declare it by hand.

## Operators

| Operator | Meaning | Example |
| --- | --- | --- |
| `+ - * /` | Arithmetic | `Revenue - COGS` |
| `^` | Power (Excel semantics: left-associative, tighter than `*`, `-2^2 = 4`) | `(1 + Rate) ^ Years` |
| `< <= > >= = !=` | Comparison → `1`/`0` | `IF(Margin >= 0, Margin, 0)` |

Division by zero yields `Infinity`/`NaN`
Excel-style (surfaced as `: ` in the editor, wrapped in `IFERROR` on Excel export).

## Aggregate functions (collapse a list dimension)

| Function | Meaning | Example |
| --- | --- | --- |
| `SUM(list)` | Sum across the list dimension (same as a bare list ref) | `Total Payroll = SUM(Salary)` |
| `AVG(list)` | Mean across the list dimension (empty → 0) | `AVG(Margin by SKU)` |
| `COUNTA(list)` | Count of non-zero elements | `COUNTA(Headcount)` |
| `SUMIF(list, cond op value)` | Sum of elements whose condition list matches | `SUMIF(Salary, Tenure > 12)` |
| `AVGIF(list, cond op value)` | Mean of matching elements (empty → 0) | `AVGIF(Salary, Dept = 1)` |
| `COUNTIF(list, cond op value)` | Count of matching elements | `COUNTIF(Salary, Dept = 2)` |

`SUM/AVG/MIN/MAX` applied to a **non-list timed item** instead collapse across
**time** (the whole series → a scalar), e.g. `SUM(Monthly Revenue)` = full-period total.

## Scalar functions (operate element-wise on resolved numbers)

| Function | Meaning | Example |
| --- | --- | --- |
| `MAX(a, b, …)` | Variadic max | `MAX(Revenue, 0)` |
| `MIN(a, b, …)` | Variadic min | `MIN(\`Min DSCR\`_Y-1, DSCR)` |
| `ABS(x)` | Absolute value | `ABS(Net Cash)` |
| `ROUND(x, digits)` / `ROUNDUP(…)` / `ROUNDDOWN(…)` | Excel rounding to `digits` decimals (half away from zero / away from zero / toward zero; negative digits round left of the point) | `ROUNDUP(Clients / 3, 0)` |
| `CEILING(x, step)` / `FLOOR(x, step)` | Round up / down to a multiple of `step` (both arguments required, as in Excel) | `CEILING(Clients / 3, 1)` |
| `POW(base, exp)` / `POWER(…)` | Power (same as `^`) | `POW(1 + Rate, Years)` |
| `IF(cond, then, else)` | `cond` truthy = `≠ 0` | `IF(Revenue > 0, Margin / Revenue, 0)` |
| `AND(a, b, …)` / `OR(a, b, …)` | Variadic logical → `1`/`0` | `IF(AND(x > 0, y > 0), 1, 0)` |
| `NOT(x)` | Logical negation | `NOT(Is Active)` |
| `DATE(year, month, day)` | Excel date serial of a calendar date (month and day roll over) | `EDATE(DATE(Exit Year, 12, 31), 6)` |
| `EDATE(date_serial, months)` | Add months to an Excel date serial | `EDATE(Start Date, 12)` |
| `YEAR(date_serial)` | Calendar year of a date serial | `YEAR(Start Date)` |
| `MONTH(date_serial)` | Month 1–12 of a date serial | `MONTH(Start Date)` |
| `IN_PERIOD(date_serial)` | `1` if the date is in the current period | `IN_PERIOD(Contract End)` |
| `PMT(rate, nper, pv)` | Periodic loan payment (Excel sign: **negative** outflow) | `-PMT(Rate / 12, Term, Principal)` |
| `IPMT(rate, per, nper, pv)` | Interest portion of payment `per` (**negative**) | `-IPMT(Rate, Period, Term, Principal)` |
| `PPMT(rate, per, nper, pv)` | Principal portion of payment `per` (**negative**) | `-PPMT(Rate, Period, Term, Principal)` |
| `IRR(cashflow)` | Whole-series internal rate of return, per period of the series (scalar). A series with no IRR (only outflows, only inflows, no root) is a compute error, never 0; an all-zero series reads 0 | `IRR(Free Cash Flow)` |
| `IRR(cashflow, CUMULATIVE)` | Per-period cumulative IRR: period t = IRR of the series through t (run-up line; 0 before convergence) | `IRR(FCFE, CUMULATIVE)` |
| `NPV(rate, cashflow)` | Whole-series net present value (scalar) | `NPV(0.1, Free Cash Flow)` |
| `YTD(x)` | Year to date: `x` cumulated from January through the current period | `YTD(Revenue)` |
| `TTM(x)` | Trailing twelve months: `x` cumulated over the last 12 months (4 quarters, 1 year) | `TTM(Revenue)` |

`IRR` and `NPV` are **root-only**: the formula must be exactly `IRR(x)`,
`IRR(x, CUMULATIVE)` or `NPV(rate, x)`, never nested, with `x` a single item
reference: bare, the current element `x[i]` in a list-mode formula, or a static
slice `x[\`key\`]` (the `rate` may be any constant expression). Any other shape is
rejected at write time (`SCALAR_FUNCTION_MISUSE`). In a list-mode formula
`Partner IRR = IRR(Net cash flow[i])` gives each element its own IRR and the
total row is IRR of the TOTAL series (portfolio IRR, never a sum of IRRs); a bare
`IRR(Net cash flow)` there is the total on every element (warning
`BARE_SERIES_REF_IN_LIST_FORMULA`). The scalar forms collapse
the item to a single value, even under an explicit dated `timeline_ref`; the
`CUMULATIVE` form is temporal (one IRR per period over the growing prefix,
exported as live growing-range `IRR($B$r:Xr)` formulas).

`YTD` and `TTM` read the past periods of a single item reference (bare, lagged,
or `x[i]` in a list-mode formula) and may sit anywhere in an expression; any other
argument is rejected at write time (`WINDOW_FUNCTION_MISUSE`). The window folds
`x` by its own aggregation: a flow sums, an `average` line averages, a `stock`
line reads its value at the current period. YTD restarts every January (calendar
year); a window not yet full at the start of the timeline folds the periods that
exist. A rate does not cumulate: a margin rate to date is
`YTD(Gross margin) / YTD(Revenue)`. A YTD / TTM line is already cumulative, so a
coarser period shows its last value (`stock` by default). Exported as live
`SUM(range)` formulas whose range restarts at each January column.

A `constant` formula reading dated items folds them to one value. A single
dated operand, a linear combination or a ratio folds each operand by its own
aggregation (`Multiple = Proceeds / Invested` is Σ proceeds / Σ invested).
Two or more dated operands combined through a product, a comparison or a
function are evaluated period by period, then folded by the formula's own
aggregation: `Value at exit = Exit flag[i] * Equity value` is Σ(flag × value),
the value in the exit year, not Σflag × Σvalue.

`PMT`/`IPMT`/`PPMT` follow the Excel sign convention and return a **negative**
number for a loan you pay down (cash outflow). Negate the result for a positive
debt-service line, e.g. `Loan Payment = -PMT(Rate / 12, Term, Principal)`.

## Lagged (time-shifted) references

Append `_M-N` / `_Q-N` / `_Y-N` to a name to read its value `N` periods earlier in
months / quarters / years (`N ≥ 1`). `_M+N` / `_Q+N` / `_Y+N` read forward
(literal `N` only, no `$` offset), e.g. a year-end reserve for next year's costs
`Reserve = \`Costs\`_Y+1 * 0.25`. Write the suffix right after the name
(`x_Y+1`); `x_Y + 1` with spaces is an addition. Cross-granularity is automatic
(on a monthly item `_Y-1` = 12 periods). A read before the first period yields
`0`, except the slot just before it on a `balance`, which is its opening
(`opening_balance`, the element's opening for `Balance[i]_M-1`, the sum of the
openings for a bare list balance). That holds for every reader, the balance's
own flows included: `Interest = Debt_M-1 * Rate` earns on the opening in month
one, `Repayment = MIN(Instalment[i], Debt[i]_M-1)` pays the first instalment.
A forward read past the last period yields `0`.

- Roll-forward: `Closing Debt = \`Opening Debt\` + Drawdowns - Repayments`, with `Opening Debt = \`Closing Debt\`_Y-1`
- Indexation: `Tariff = Tariff_Y-1 * (1 + Inflation)`
- Dynamic lag: `Revenue_M-$Delay` where `$Delay` is another item supplying the
  offset (`$` + its name, or `$` + its uid). The offset value is rounded,
  clamped to `≥ 0`, and converted at the base item's grain (`_Y-$X` on a
  monthly item shifts by `12 × X` periods). In a list-mode formula, a
  list-dimensioned offset resolves PER ELEMENT (each element shifts by its own
  lag: the ramp-up / cohort primitive); a list offset that cannot project onto
  exactly one element (scalar formula, non-1:1 mapping) is rejected
  (`LIST_LAG_REF_AMBIGUOUS`).

Self-referencing lag is allowed. A **zero-lag** cycle (an item depending on itself
or a peer within the same period) is rejected: add an explicit lag on one edge.
A cycle that goes through a forward reference is rejected too (the later period
is not computed yet): read the source item instead of one that depends on you.

## List-dimensioned references (items with `liste_ref`)

- **Bare ref** `Salary` = the item's **total** (sum across the list) in every context.
- **Current element** `Salary[i]`: only inside a **list-mode formula** (its own
  `liste_ref` set); `[i]` is the value for the element being evaluated. Allocation
  example: `G&A[i] = Shared G&A * Revenue[i] / Revenue`.
- **Static slice** `Salary[\`Sales\`]`: one fixed element, or the mapped sum when
  the key is an element of a list reachable via `mapped_to`.
- **Across lists**: a `mapped_to` link is declared once, on either side, and
  serves both directions. With lines linked to their programme
  (`{ label: "Line 1", mapped_to: { Programmes: ["Client A"] } }`), a formula over
  Programmes reads `Line revenue[i]` as the sum of its lines, and a formula over
  Lines reads `Programme units[i]` as its programme's value. A group subtotal
  `Repayment[\`Banks\`]` sums the creditors linked to "Banks", whether the link
  sits on each creditor (`{ Groups: ["Banks"] }`) or on the group element
  (`{ Creditors: ["Bank 1", "Bank 2"] }`). A cross-list `[i]` read with no link
  for any element is rejected (`UNMAPPED_CROSS_LIST_READ`); elements left without
  a link read 0 and are named in a warning (`PARTIAL_CROSS_LIST_READ`).

Lag applies to the whole subscript: `Salary[\`Sales\`]_Y-1`, and `X[i]_M-1`
is the current element's previous-period value: the per-element roll-forward
(`Headcount[i] = Hires[i] + Headcount[i]_M-1`). A bare lagged self-ref in a
list-mode formula is the list TOTAL re-added into every element (×N
compounding): use `[i]_M-N` instead (warning `BARE_SELF_LAG_IN_LIST_FORMULA`).

## 2D patterns (list × time)

A spreadsheet's second dimension (assumptions in rows × assumptions in columns)
maps to **list × time**: the list is the row dimension, the timeline the column
dimension. Golden rule: keep drivers at the SAME grain as the formula, and use
`[i]` for element-by-element arithmetic (a bare ref is always the total).

1. **Seasonality**: `Revenue[i] = Annual Sales[i] / 12 * Seasonality` with
   `Annual Sales` list-mode (per product) and `Seasonality` a scalar monthly
   curve. Prefer a monthly-grain driver over a yearly one read from a monthly
   formula (the sum-split ÷k surprises on partial years).
2. **Ramp-up / cohorts** (one primitive for both: an entity×age matrix IS a
   cohort): `RampPct = RampCurve_M-$HireLag` where `RampCurve` is a scalar
   curve indexed by age and `HireLag` a list-mode offset (one lag per
   rep/cohort). Auto-populate the offset with `HireLag = LIST_INDEX` when
   elements are ordered by start date. NOT an INDEX/MATCH job: a lookup
   "value at age k" is a variable time shift on a curve.
3. **Per-element roll-forward**: `Headcount[i] = Hires[i] + Headcount[i]_M-1`
   accumulates each element on its own series (`[i]_M-N`, never the bare
   lagged self-ref, which is the total ×N).
4. **Allocation**: `G&A[i] = Shared G&A * Revenue[i] / Revenue` (per-element
   numerator, bare-total denominator).

## Cross-branch references: `LAYER(ref, branch)`

Read an item's value **in another branch** (scenario) from inside a formula:

- `Revenue Variance = Revenue - LAYER(Revenue, budget_2024)`
- `Variance % = (Revenue - LAYER(Revenue, \`Budget 2024\`)) / LAYER(Revenue, \`Budget 2024\`)`

The second argument is a **branch id** (`default`, `layer_x`) or a branch
**display name** (back-tick it if it has spaces; case-insensitive). The operand
is evaluated under that branch's checkout basis `[base, branch]`: the same
semantics as the KPI `compare_to` widget. Rules:

- **No lag** inside `LAYER()`; lag the host formula instead.
- In a list-mode formula, `LAYER(Units[i], budget)` is the **current element's**
  value under the branch (`Units[i] - LAYER(Units[i], budget)` = per-element
  gap); the bare `LAYER(Units, budget)` is the list **total**, as everywhere.
- Inside a LAYER-triggered sub-compute, nested `LAYER()` refs collapse to that
  sub-compute's own basis (so an item can never depend on itself across
  branches without the cycle being rejected).
- An unknown branch is a per-item compute error.
- On Excel export the cell keeps its **computed value** (no live-Excel
  equivalent exists); it is reported in the export's frozen-formula diagnostic.

## Reserved identifiers

- `CURRENT_PERIOD`: Excel serial date of the **last day** of the period being
  computed (period-end, not period-start; for date math).
- `PERIOD_YEAR`: **calendar year** of the period being computed. The
  date-gating primitive: `IF(PERIOD_YEAR >= Commissioning Year, Revenue, 0)`
  replaces the hand-rolled self-referential year counter. Exports as each
  column's literal.
- `PERIOD_INDEX`: **0-based position** of the period being computed on the
  item's timeline. Exports as each column's literal.
- `IS_ACTUAL`: **1** on a closed period under the actuals cutover
  (`actuals_through`, the viewed branch's override when it has one), **0**
  after. Same rule as the A / E / F headers: a coarse period straddling
  the cutover (E) reads 0. Switch logic at the cutover with
  `IF(IS_ACTUAL, Actual line, Forecast line)` instead of a hand-typed
  actual/forecast flag: the switch moves by itself when a sync extends the
  window. No cutover → 0 everywhere (warning `IS_ACTUAL_WITHOUT_CUTOVER`).
  Exports as `(DATE(<period end>)<=ACTUALS_THROUGH)*1` against a named date
  cell on the Intro sheet.
- `LIST_INDEX`: **0-based position** of the current element in the enclosing
  list-mode formula's list. Only valid inside a list-mode formula (its
  own `liste_ref` set). The cohort auto-population primitive: a list-mode
  formula `cohort_lag = LIST_INDEX` gives element k the lag k, so
  `retention[i] = retention_curve_M-$cohort_lag` reads the curve at each
  cohort's age with no manual offsets. Exports as a per-row literal.
