Distribution LAMBDAs for Monte Carlo Simulations

Every distribution is an inverse-CDF transform of one uniform stream, u, from fx.RiskUλ. None uses rejection sampling or a loop, which is what lets each one be a single spilling formula. Argument order follows XLRisk and @RISK, so an existing model migrates by name.

Twenty-five of the thirty have a closed form or are built on Excel's own GAMMA.INV, BETA.INV and NORM.S.INV. The other five — the inverse Gaussian, the skew normal and the three noncentral distributions — have no inverse in Excel, so they are sampled by numeric inversion: the CDF is evaluated once on a 4,096-point grid and every trial is interpolated from it. That is accurate to about 10⁻⁸ relative, and 100,000 trials take about a second.

Degrees of freedom may be fractional. Excel's own CHISQ.INV, T.INV and F.INV silently truncate them to whole numbers, which is why none of them is used.

A distribution describes one input. A copula describes how several inputs move together. Price and volume, claim frequency and severity, interest rates and defaults are rarely independent, and a model that samples them as if they were understates how often the bad cases arrive at the same time.

Each copula spills a block of correlated uniforms, one column per input. Feed one column into each distribution's Trials argument and every input keeps exactly the distribution you gave it, while moving with the others: =fx.RiskCopulaGaussλ({1,0.7;0.7,1}, 10000, 20) in C2, then =fx.RiskNormalλ(100, 15, CHOOSECOLS(C2#, 1)) and =fx.RiskLogNormλ(0.5, 0.4, CHOOSECOLS(C2#, 2)). The copula owns the randomness, so give it its own VarID; the distributions fed from it take none. Its streams never collide with theirs.

The choice is about the tails. The Gaussian copula is the standard, symmetric choice, with no extra tendency for extremes to coincide. The Student t adds that tendency in both tails: fewer degrees of freedom, stronger. Clayton concentrates dependence in the lower tail — inputs that crash together — and Gumbel in the upper tail — inputs that boom together. Frank is symmetric with lighter tails than the Gaussian.

For two inputs, Clayton and Frank also take a negative Theta, so the inputs move in opposite directions: price up, volume down. The three Archimedean copulas — Clayton, Gumbel and Frank — take an optional Rotation, as in the VineCopula R package: 180 moves the tail to the other corner, and 90 or 270 turns positive dependence negative. XL Edge's Insert Monte Carlo Copula prompts for the correlation matrix or Theta, writes the block, and shows the formula to feed each column into a distribution.

These read a spilled range of trials — the output of a distribution, or of a model built from several — and replace the separate results sheet that simulation add-ins generate. The statistics follow the Analysis ToolPak's Descriptive Statistics: the same names, in the same order, with the trial count first and percentiles last.

Every block starts with a header row holding the variable's name: the Name argument, or else the text in the cell above the trials. Pass a header cell such as A1 and the block keeps up when it is renamed. XL Edge's Insert Monte Carlo Statistics writes any of them for a selected result, and the variables tables for several results selected at once.

Three functions put a number on the loss tail of a simulated result. Confidence defaults to 0.95. The trials are read as P&L by default — gains positive, losses negative, so the loss tail is on the left — and LossesPositive = TRUE reads them as loss amounts instead. Either way the answer is reported as a positive loss. XL Edge's Insert Monte Carlo Risk Measures writes all three for a spilled range at a confidence level you choose.

Two functions return the data behind the three charts every Monte Carlo report needs, each starting with a title row. fx.RiskChartHistλ puts everything a histogram needs into one spill — the title, the P10, P50 and P90 lines and the bins — so the lines can never drift from the bars they sit on. The bins are sized so that P10 and P90 fall exactly on bin edges, and the coloured band ends exactly at its lines.

fx.RiskTornadoλ ranks the inputs by how far each one moves the output. By default it reports the output's mean when an input sits in its lowest 10% of trials and when it sits in its highest 10%, in the output's own units; the "rank" method reports each input's Spearman rank correlation instead.

XL Edge's Insert Monte Carlo Chart writes the data and draws a native Excel chart from it, with the title linked to the title row. Because the chart reads a live spill, it redraws when the model, the seed or the bin count changes. There is no picture to regenerate.

Two functions sit underneath all of the above. Most models never call them directly.

The distributions that Excel cannot invert directly are built on these. They are ordinary functions and can be called from a sheet, but most models will never need to.

Argument conventions differ between Monte Carlo tools, and these get a wrong answer rather than an error, so they are stated plainly.

322 automated checks drive Excel directly, and all 322 pass. The HDR generator reproduces the self-check values published with the SIPmath standard to within 5 × 10⁻¹⁴, so it agrees with the reference implementations in other languages. Every closed-form distribution matches an independent reference inverse-CDF implementation fed the same uniforms, with a worst case of 3 × 10⁻¹², and every discrete distribution matches trial for trial. The five numerically inverted distributions are checked by a CDF round trip instead, because reference inverses are themselves unreliable far out in some tails: the worst relative error is about 10⁻⁸. The three risk measures match an independent implementation of the same estimators under both sign conventions.

Every copula matches an independent reference cell for cell, given the same uniforms; its Kendall's τ matches the closed form; every column passes a uniformity test; and Clayton shows its lower-tail and Gumbel its upper-tail dependence. The statistics blocks match an independent implementation on every row. Histogram counts tie to the trial count, the P-lines and S-curve equal independently computed percentiles, and tornado swings and rank correlations match independent calculations, including an input with tied values.

The rest cover monotonicity — a larger uniform never gives a smaller value, the property the copulas rely on — shape and moment tests at 20,000 trials, invalid-argument cases that must each return an error rather than a plausible wrong number, timing (the slowest distribution runs 100,000 trials in about a second), and a full recalculation plus a save, close and reopen, after which every value is identical and nothing shows #NAME?.

Most of the mathematics here is textbook — the normal inverse, the exponential's −mean·ln(1 − u), the two-branch triangular quantile. What is borrowed is narrower, and it is credited here and inside the library itself.