Browse the documentation

Reference

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

OperatorMeaningExample
+ - * /ArithmeticRevenue - COGS
^Power (Excel semantics: left-associative, tighter than *, -2^2 = 4)(1 + Rate) ^ Years
< <= > >= = !=Comparison → 1/0IF(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)

FunctionMeaningExample
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 elementsCOUNTA(Headcount)
SUMIF(list, cond op value)Sum of elements whose condition list matchesSUMIF(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 elementsCOUNTIF(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)

FunctionMeaningExample
MAX(a, b, …)Variadic maxMAX(Revenue, 0)
MIN(a, b, …)Variadic minMIN(\Min DSCR`_Y-1, DSCR)`
ABS(x)Absolute valueABS(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 = ≠ 0IF(Revenue > 0, Margin / Revenue, 0)
AND(a, b, …) / OR(a, b, …)Variadic logical → 1/0IF(AND(x > 0, y > 0), 1, 0)
NOT(x)Logical negationNOT(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 serialEDATE(Start Date, 12)
YEAR(date_serial)Calendar year of a date serialYEAR(Start Date)
MONTH(date_serial)Month 1–12 of a date serialMONTH(Start Date)
IN_PERIOD(date_serial)1 if the date is in the current periodIN_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 0IRR(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 periodYTD(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`](therate 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 bareIRR(Net cash flow)there is the total on every element (warningBARE_SERIES_REF_IN_LIST_FORMULA). The scalar forms collapse the item to a single value, even under an explicit dated timeline_ref; the CUMULATIVEform is temporal (one IRR per period over the growing prefix, exported as live growing-rangeIRR($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 + 1with spaces is an addition. Cross-granularity is automatic (on a monthly item_Y-1= 12 periods). A read before the first period yields0, 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 * Rateearns 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 yields0`.

  • 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-Ninstead (warningBARE_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.