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`](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, withOpening Debt = `Closing Debt`_Y-1` - Indexation:
Tariff = Tariff_Y-1 * (1 + Inflation) - Dynamic lag:
Revenue_M-$Delaywhere$Delayis 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-$Xon a monthly item shifts by12 × Xperiods). 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 ownliste_refset);[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 viamapped_to`. - Across lists: a
mapped_tolink 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 readsLine revenue[i]as the sum of its lines, and a formula over Lines readsProgramme units[i]as its programme's value. A group subtotalRepayment[\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).
- Seasonality:
Revenue[i] = Annual Sales[i] / 12 * SeasonalitywithAnnual Saleslist-mode (per product) andSeasonalitya 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). - Ramp-up / cohorts (one primitive for both: an entity×age matrix IS a
cohort):
RampPct = RampCurve_M-$HireLagwhereRampCurveis a scalar curve indexed by age andHireLaga list-mode offset (one lag per rep/cohort). Auto-populate the offset withHireLag = LIST_INDEXwhen 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. - Per-element roll-forward:
Headcount[i] = Hires[i] + Headcount[i]_M-1accumulates each element on its own series ([i]_M-N, never the bare lagged self-ref, which is the total ×N). - 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 bareLAYER(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 withIF(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 (warningIS_ACTUAL_WITHOUT_CUTOVER). Exports as(DATE(<period end>)<=ACTUALS_THROUGH)*1against 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 ownliste_refset). The cohort auto-population primitive: a list-mode formulacohort_lag = LIST_INDEXgives element k the lag k, soretention[i] = retention_curve_M-$cohort_lagreads the curve at each cohort's age with no manual offsets. Exports as a per-row literal.