Skip to content

Formulas - formulas

This optional sheet defines derived variables using a small formula language. It is primarily useful when a multiplier or organization characteristic should be computed from other variables rather than sampled directly.

Example Formulas

Formula What it does
CombinedEV = Hedginess * FundingAdditionality Creates a derived variable from two existing variables.
PolicyWeight_{region} = probit(PolicyProbability_{region}) Uses placeholders to create one formula per matching region.
Mixture_{region} = draw_from(BaseCase_{region}, 0.7, Upside_{region}, 0.3) Creates a weighted mixture between two variables.
LognormalEV = lognormal_ev_from_xpR(ValueAtMedian, 0.5, 4) Converts a percentile and percentile ratio into an expected value.
Result{a}{b} = x{a}{b} + z{a} + u{b} Example of a valid multi-placeholder formula.

Accepted Structure

Only the first column is read. Each non-empty row should contain one formula assignment of the form:

Name = expression

Examples:

CombinedEV = Hedginess * FundingAdditionality
PolicyWeight_{region} = probit(PolicyProbability_{region})
Mixture_{region} = draw_from(BaseCase_{region}, 0.7, Upside_{region}, 0.3)

Parser rules:

  • Empty rows are ignored.
  • Rows starting with # are treated as comments.
  • The parser keeps only lines of the form name = expression.
  • Additional columns are ignored.

Placeholders

Placeholders let one formula expand into multiple formulas by matching available variable names.

Examples:

  • Combined_{region} = M1_{region} * M2_{region}
  • Result{a}{b} = x{a}{b} + z{a} + u{b}

Important behavior:

  • Single-placeholder patterns are expanded against matching variable names.
  • Multiple-placeholder formulas must be unambiguous.
  • Ambiguous placeholder patterns are warned about and ignored.
  • Later formulas can reference variables created by earlier formulas in the same sheet.

Ambiguous vs valid placeholders

✅ Valid:

Result{a}{b} = x{a}{b} + z{a} + u{b}

Why it works:

  • z{a} constrains the possible values of {a}
  • u{b} constrains the possible values of {b}
  • With variables xa, xb, ya, yb, za, ub → creates Resultab, Resultbb

❌ Ambiguous:

Result{a}{b} = x{a}{b} + y{a}{b}

Why it fails:

  • both placeholders only appear together,
  • the parser cannot work out a unique expansion,
  • the formula is warned about and ignored.
  • Fix: Add separators like x{a}_{b} or reference individually: x{a} + y{b}

Operators And Functions

Supported Operators

  • +
  • -
  • *
  • /
  • ^
  • ()
  • × is normalized to *
  • ÷ is normalized to /

Supported Functions

Function Purpose
ln(x) / LN(x) Natural logarithm
exp(x) / EXP(x) Exponential function
sqrt(x) Square root
abs(x) Absolute value
lognormal_ev_from_xpR(x, p, R, cap_p, cap_R) Computes a lognormal expected value from a percentile and a percentile ratio
probit(p, cap_p) Inverse standard normal CDF
draw_from(m1, w1, m2, w2, ...) Row-wise stochastic choice between candidate variables

Notes:

  • Function names are case-insensitive where documented, for example ln and LN.
  • draw_from expects alternating value, weight pairs.
  • cap_p and cap_R let you disable the default capping behavior in probit() and lognormal_ev_from_xpR().

Custom Function Details

lognormal_ev_from_xpR(x, p, R, cap_p = TRUE, cap_R = TRUE)

Purpose:

  • Computes the expected value of a lognormal distribution from:
  • a known percentile value x,
  • the percentile p,
  • and the ratio R = P95 / P05.

Arguments:

  • x: percentile value and must be positive
  • p: percentile in (0, 1)
  • R: ratio greater than 1

Behavior:

  • By default, p is capped to [0.001, 0.999].
  • By default, R is capped to be at least 1.001.
  • If capping is disabled, invalid values produce an error.

Example:

LognormalEV = lognormal_ev_from_xpR(ValueAtMedian, 0.5, 4)

probit(p, cap_p = TRUE)

Purpose:

  • Returns the inverse standard normal CDF, equivalent to qnorm(p).

Arguments:

  • p: probability in (0, 1)

Behavior:

  • By default, p is capped to [0.001, 0.999].
  • If capping is disabled, invalid values produce an error.

Example:

PolicyWeight = probit(0.95)

draw_from(m1, w1, m2, w2, ...)

Purpose:

  • Chooses between candidate variables row by row using weights.

Arguments:

  • alternating value, weight pairs

Behavior:

  • Each weight can be a scalar or a simulation-length vector.
  • Negative weights are capped to 0 with a warning.
  • At least one weight must be positive for each simulation row.

Examples:

Mix = draw_from(BaseCase, 0.7, Upside, 0.3)

Choice = draw_from(ScenarioA, WeightA, ScenarioB, 1)

Formula Validation

The current parser:

  • validates expansion against available variable names from imp and fos,
  • ignores formulas that cannot be expanded,
  • ignores formulas that still reference undefined variables after expansion,
  • records warnings rather than immediately failing for most bad formulas.

Preview-specific validation also checks parameters used inside:

  • lognormal_ev_from_xpR(...)
  • probit(...)
  • draw_from(...)

For example, negative draw_from weights are warned about and capped to 0 during evaluation.

Output Behavior

  • Formula-defined variables are only operationally important if they are referenced by later formulas or by the fos sheet.
  • If a formula-generated variable is used in fos but is missing from imp, the engine can add it to the multiplier table automatically.
  • If a formula-generated variable reuses the name of an existing multiplier and is used in fos, the formula overrides that multiplier's sampled distribution and its correlations are reset to 0.

Practical Notes

  • Treat formulas as a code-like sheet, not a normal tabular sheet.
  • The engine currently reads only the first column.
  • A formula can be syntactically valid but still be ignored if its placeholders do not expand against the available variable set.