Reusable functions

The LAMBDA library.

Every reusable function from the lessons, plus a few standalone utilities, in one place. Copy a definition into Formulas > Name Manager > New (or the Advanced Formula Environment), give it the name shown, and call it like a built-in function.

7 functions · 3 topics · updated 7 November 2026

A–Z

All functions, alphabetically.

Function	What it does	Topic	Lesson
`fx_ABM`	Simulates arithmetic Brownian motion paths, X(t) = x0 + mu t + sigma W(t).	Simulation and stochastic processes	02
`fx_Correl`	Correlation matrix of the columns of a data range, built with MAKEARRAY.	Statistics and portfolio	01
`fx_Covar`	Sample covariance matrix of the columns of a data range, built with MAKEARRAY.	Statistics and portfolio	01
`fx_GBM`	Simulates geometric Brownian motion price paths with expected return mu (log drift mu - sigma^2/2).	Simulation and stochastic processes	02
`fx_RandChi2`	Chi-squared random numbers with nu degrees of freedom, also for non-integer nu.	Random numbers	Utility
`fx_RandN`	Normal random numbers with mean mu and standard deviation sigma; standard normal when they are omitted.	Random numbers	Utility
`fx_RandT`	Student t random numbers with nu degrees of freedom, also for non-integer nu.	Random numbers	Utility

By topic

The same functions, grouped by what they solve.

Statistics and portfolio

How variables move together: the building blocks of portfolio and risk calculations.

fx_CorrelCorrelation matrix of the columns of a data range, built with MAKEARRAY.
fx_CovarSample covariance matrix of the columns of a data range, built with MAKEARRAY.

Simulation and stochastic processes

Monte Carlo paths for log-prices and prices, one or many at a time.

fx_ABMSimulates arithmetic Brownian motion paths, X(t) = x0 + mu t + sigma W(t).
fx_GBMSimulates geometric Brownian motion price paths with expected return mu (log drift mu - sigma^2/2).

Random numbers

Draws from common distributions, ready to use on their own or inside simulations. Non-integer degrees of freedom are supported, unlike Excel's T.INV and CHISQ.INV, which truncate them.

fx_RandChi2Chi-squared random numbers with nu degrees of freedom, also for non-integer nu.
fx_RandNNormal random numbers with mean mu and standard deviation sigma; standard normal when they are omitted.
fx_RandTStudent t random numbers with nu degrees of freedom, also for non-integer nu.

Definitions

Signatures, parameters and code.

Inside each function, parameters and LET variables end with an underscore (x0_, n_, Data_) so they never clash with cell addresses or with names defined in a workbook. The Text1_, Text2_… lines are comments: LET accepts names that are never used. Use “Compact” to copy a version without them; in our Excel tests the comments made no measurable difference to speed.

`fx_ABM`

Simulation and stochastic processes

fx_ABM(x0_, mu_, sigma_, T_, n_, [paths_], [direction_])

Simulates arithmetic Brownian motion paths, X(t) = x0 + mu t + sigma W(t).

x0_: Starting value of every path.
mu_: Drift per year of X.
sigma_: Volatility per year.
T_: Horizon in years.
n_: Number of time steps of length T_/n_.
paths_: Optional. Number of paths; default 1.
direction_: Optional. "V" (default): time in rows, one column per path. "H": the transpose.
Returns: An (n_+1) x paths_ array that includes time zero; with "H", its transpose.
Example: =fx_ABM(0, 0, 1, 1, 252)

=LAMBDA(x0_, mu_, sigma_, T_, n_, [paths_], [direction_],
  LET(
    Text1_,  "Default to one path and vertical output",
    m_,      IF(ISOMITTED(paths_), 1, paths_),
    layout_, IF(ISOMITTED(direction_), "V", UPPER(direction_)),
    Text2_,  "Length of each time step",
    dt_,     T_ / n_,
    Text3_,  "Generate uniform r.v.: one row per step, one column per path",
    U_,      RANDARRAY(n_, m_),
    Text4_,  "Inverse method: uniform -> standard normal (endpoints protected)",
    Z_,      NORM.S.INV(IF(U_ = 0, 2^-53, IF(U_ = 1, 1 - 2^-53, U_))),
    Text5_,  "ABM increments: drift plus a normal shock",
    dX_,     mu_*dt_ + sigma_*SQRT(dt_)*Z_,
    Text6_,  "Cumulative sums path by path: lower-triangular matrix of ones",
    L_,      --(SEQUENCE(n_) >= SEQUENCE(1, n_)),
    cum_,    MMULT(L_, dX_),
    Text7_,  "Every path starts at x0_",
    path_,   x0_ + VSTACK(SEQUENCE(1, m_, 0, 0), cum_),
    Text8_,  "V: time in rows, paths in columns; H: the transpose",
    IF(layout_ = "V", path_, IF(layout_ = "H", TRANSPOSE(path_), NA()))
  )
)

Lesson 02 · Simulating Brownian motion

`fx_Correl`

Statistics and portfolio

fx_Correl(Data_)

Correlation matrix of the columns of a data range, built with MAKEARRAY.

Data_: Observations in rows, one column per variable.
Returns: A square array: element (i, j) is CORREL of columns i and j; the diagonal is 1.
Example: =fx_Correl(C5:E10)

=LAMBDA(Data_,
  LET(
    Text1_, "Data_: one column per variable, one row per observation",
    n_,     COLUMNS(Data_),
    Text2_, "Element (i, j) = correlation of columns i and j",
    MAKEARRAY(n_, n_,
      LAMBDA(i_, j_,
        CORREL(CHOOSECOLS(Data_, i_), CHOOSECOLS(Data_, j_))
      )
    )
  )
)

Lesson 01 · Covariance and correlation matrices

`fx_Covar`

Statistics and portfolio

fx_Covar(Data_)

Sample covariance matrix of the columns of a data range, built with MAKEARRAY.

Data_: Observations in rows, one column per variable.
Returns: A square array: element (i, j) is COVARIANCE.S of columns i and j; the diagonal holds the sample variances.
Example: =fx_Covar(C5:E10)

=LAMBDA(Data_,
  LET(
    Text1_, "Data_: one column per variable, one row per observation",
    n_,     COLUMNS(Data_),
    Text2_, "Element (i, j) = sample covariance of columns i and j",
    MAKEARRAY(n_, n_,
      LAMBDA(i_, j_,
        COVARIANCE.S(CHOOSECOLS(Data_, i_), CHOOSECOLS(Data_, j_))
      )
    )
  )
)

Lesson 01 · Covariance and correlation matrices

`fx_GBM`

Simulation and stochastic processes

fx_GBM(S0_, mu_, sigma_, T_, n_, [paths_], [direction_])

Simulates geometric Brownian motion price paths with expected return mu (log drift mu - sigma^2/2).

S0_: Initial price.
mu_: Expected return per year.
sigma_: Volatility per year.
T_: Horizon in years.
n_: Number of time steps of length T_/n_.
paths_: Optional. Number of paths; default 1.
direction_: Optional. "V" (default): time in rows, one column per path. "H": the transpose.
Returns: An (n_+1) x paths_ array of prices that includes the initial price; with "H", its transpose.
Example: =fx_GBM(100, 5%, 20%, 1, 52, 1000)

=LAMBDA(S0_, mu_, sigma_, T_, n_, [paths_], [direction_],
  LET(
    Text1_,  "Default to one path and vertical output",
    m_,      IF(ISOMITTED(paths_), 1, paths_),
    layout_, IF(ISOMITTED(direction_), "V", UPPER(direction_)),
    Text2_,  "Ito: ln S drifts at mu_ - sigma_^2/2, not at mu_",
    drift_,  mu_ - sigma_^2/2,
    Text3_,  "Simulate ln S as an ABM, then take the exponential",
    EXP(fx_ABM(LN(S0_), drift_, sigma_, T_, n_, m_, layout_))
  )
)

Lesson 02 · Simulating Brownian motion

`fx_RandChi2`

Random numbers

fx_RandChi2(n_, nu_, [cols_])

Chi-squared random numbers with nu degrees of freedom, also for non-integer nu.

n_: Number of rows (draws per column).
nu_: Degrees of freedom, > 0; need not be an integer.
cols_: Optional. Number of columns; default 1.
Returns: An n_ x cols_ array of independent chi-squared draws.
Example: =fx_RandChi2(1000, 4.5)

Uses GAMMA.INV(U, nu/2, 2). CHISQ.INV truncates nu: CHISQ.INV(0.95, 4.5) returns the same 9.49 as with 4 degrees of freedom, while the correct quantile is 10.29.

=LAMBDA(n_, nu_, [cols_],
  LET(
    Text1_, "One column unless cols_ is given",
    c_,     IF(ISOMITTED(cols_), 1, cols_),
    Text2_, "Generate uniform r.v.; RANDARRAY can return 0, where the inverse fails",
    U_,     RANDARRAY(n_, c_),
    Text3_, "Chi-squared(nu_) = Gamma(shape nu_/2, scale 2); GAMMA.INV accepts non-integer nu_, CHISQ.INV truncates it",
    GAMMA.INV(IF(U_ = 0, 2^-53, U_), nu_ / 2, 2)
  )
)

Standalone function: copy the code into Name Manager and use it in any workbook.

`fx_RandN`

Random numbers

fx_RandN(n_, [mu_], [sigma_], [cols_])

Normal random numbers with mean mu and standard deviation sigma; standard normal when they are omitted.

n_: Number of rows (draws per column).
mu_: Optional. Mean; default 0.
sigma_: Optional. Standard deviation; default 1.
cols_: Optional. Number of columns; default 1.
Returns: An n_ x cols_ array of independent normal draws.
Example: =fx_RandN(1000, , 0.2)

=LAMBDA(n_, [mu_], [sigma_], [cols_],
  LET(
    Text1_, "Defaults: mean 0, standard deviation 1, one column",
    m_,     IF(ISOMITTED(mu_), 0, mu_),
    s_,     IF(ISOMITTED(sigma_), 1, sigma_),
    c_,     IF(ISOMITTED(cols_), 1, cols_),
    Text2_, "Generate uniform r.v.; RANDARRAY can return 0, where the inverse fails",
    U_,     RANDARRAY(n_, c_),
    Text3_, "Inverse method: uniform -> normal with mean m_ and standard deviation s_",
    m_ + s_ * NORM.S.INV(IF(U_ = 0, 2^-53, U_))
  )
)

Standalone function: copy the code into Name Manager and use it in any workbook.

`fx_RandT`

Random numbers

fx_RandT(n_, nu_, [cols_])

Student t random numbers with nu degrees of freedom, also for non-integer nu.

n_: Number of rows (draws per column).
nu_: Degrees of freedom, > 0; need not be an integer.
cols_: Optional. Number of columns; default 1.
Returns: An n_ x cols_ array of independent Student t draws. For a location-scale t, use mu + sigma * fx_RandT(...).
Example: =fx_RandT(1000, 5.5)

Built as Z / SQRT(X / nu) with Z standard normal and X chi-squared(nu). T.INV truncates nu: T.INV(0.95, 5.5) equals T.INV(0.95, 5). Tested on 200,000 draws: P(T <= 2) with nu = 5.5 is 0.9518 against 0.9516 exactly.

=LAMBDA(n_, nu_, [cols_],
  LET(
    Text1_, "One column unless cols_ is given",
    c_,     IF(ISOMITTED(cols_), 1, cols_),
    Text2_, "Two independent uniform r.v. arrays",
    U1_,    RANDARRAY(n_, c_),
    U2_,    RANDARRAY(n_, c_),
    Text3_, "Z standard normal, X chi-squared(nu_) via GAMMA.INV (non-integer nu_ allowed)",
    Z_,     NORM.S.INV(IF(U1_ = 0, 2^-53, U1_)),
    X_,     GAMMA.INV(IF(U2_ = 0, 2^-53, U2_), nu_ / 2, 2),
    Text4_, "Student t(nu_) = Z / SQRT(X / nu_): T.INV would truncate nu_ to an integer",
    Z_ / SQRT(X_ / nu_)
  )
)

Standalone function: copy the code into Name Manager and use it in any workbook.