Time Series and Statistical LAMBDAs

Every simulator spills Trials rows by Periods columns. Summarise any period like any other column of trials — =fx.RiskStatsλ(TAKE(B2#, , -1)) for the last one — or hand the whole block to the fan chart.

dt is the length of one period in the units of the rates: annual drift and volatility with monthly steps is dt = 1/12. DegFree swaps the normal shocks for Student t shocks scaled to the same variance — fat tails without changing the volatility. Start for ARMA is the values before period 1, oldest first; for ARIMA it is the last observed level. An ARMA whose AR part is not stationary returns #NUM!: use ARIMA for a series that trends.

Trials may also be a Trials × Periods block of uniforms — bootstrapped or correlated — which is then used as the shocks. And =100 * EXP(fx.RiskCumulateλ(D2#)) turns a block of log returns into prices.

Each fit returns a Parameter / Estimate block. Point the simulator's arguments at its cells and the paths are calibrated to the history, and recalibrate themselves when the history is extended.

For ARIMA, fit the changes: =fx.RiskFitARMAλ(DROP(A2:A500, 1) - DROP(A2:A500, -1), 1, 1), and its Mean is the Drift. On simulated histories every fit recovers the true parameters. The ARMA fit lands within 0.05 of an independent maximum-likelihood implementation and the GARCH fit within 0.01. The jump-diffusion fit leans on sixth moments, which need thousands of observations: treat it as a first guess.

For monthly, weekly or quarterly series with a repeating seasonal pattern: sales, policy counts, call volumes, claims. Five functions are built around a Model block. Fit writes it; Forecast and the simulator read it; Backtest and Rank refit by themselves. A worked sequence on 108 months of history in C4:C111: =fx.RiskSARIMAFitλ(C4:C111) in F2, then =fx.RiskSARIMAForecastλ(C4:C111, F2#, 24) for the next 24 months with a P10 to P90 band, =fx.RiskSARIMABacktestλ(C4:C111, 12) to ask how well last year would have been forecast, =fx.RiskSARIMARankλ(C4:C111) to compare candidate models, and =fx.RiskSARIMAλ(C4:C111, F2#, 24) for 10,000 simulated continuations to feed the fan chart.

Order is {p, d, q, P, D, Q}, up to p, q = 2, P, Q = 1 and d, D = 1. The default is the classic airline model {0,1,1,0,1,1}, a good first choice for business series. Log = TRUE models the logarithm: seasonal swings that grow with the level, a forecast that cannot go negative, and a band wider above than below. The Model block can also be typed by hand, with parameters estimated elsewhere.

The forecast band is exact for the model — the forecast ± z × StDev × √Σψ² — so the percentiles of the simulated paths match the table. Backtest Coverage is how often the actuals fell inside the band: near 80% is right for P10 to P90. Rank fits 12 common orders with the same differencing and compares their AIC on the same residual window; it is a guide, not a verdict, so backtest the leaders. On short series the coefficients are loosely determined by any method while the forecasts agree closely: judge a model by its forecasts and backtests, not by a single coefficient.

fx.RiskChartFanλ reads any block of paths — from a simulator, from SARIMA, or from a model built on them — and returns everything a fan chart needs in one spill: a title row, a header row, then one row per period with the lower percentile, P50, the upper percentile, the band's width, the plan and the mean. The band defaults to P10 to P90 and can be any pair; the headers are formulas, so a chart legend linked to them follows any change.

Plan is the single-number forecast the business already has, drawn as a line against the band — the picture of how likely the plan is. Start opens the fan from a point at period 0, and Labels puts dates on the axis. XL Edge's Insert Monte Carlo Chart → Fan Chart writes the data and draws the chart, and it redraws when the model changes.

Checks on a table of history, one variable per column, or on simulated inputs side by side with HSTACK(B2#, C2#, D2#). Each returns one labelled spill whose size is known in advance, so XL Edge can check there is room before writing it.

Correlation is Pearson, or Spearman on ranks. The upper triangle always shows R; LowerTriangle puts something else below it: 0 only the strong pairs (|R| > 0.90), 1 R squared, 2 p-values, 3 Yes / No for significance at Alpha. Labels = FALSE returns the bare matrix, ready for fx.RiskCopulaGaussλ on the Monte Carlo LAMBDAs page — a copula fitted from history in two formulas.

Normality runs Shapiro–Wilk, Anderson–Darling and Jarque–Bera, each with its p-value and a verdict at Alpha (default 0.05). Be careful with thousands of values: every test then rejects even trivial departures from normality. These tests earn their keep on histories and on model residuals. Multicollinearity reports the VIF — how much a variable's variance is inflated because the others explain it — with Tolerance and R²; above 10 is a warning sign. The eigenvalues come with the condition number, where above 30 signals serious multicollinearity. Outliers gives every row its Mahalanobis distance from the centre and its leverage, each with a flag.

The functions above are built on these. They are ordinary functions and can be called from a sheet, but most models will never need to.

These get a wrong answer rather than an error, so they are stated plainly.

140 of the 557 automated checks that drive Excel directly cover this library, and all pass. Every simulator matches an independent step-by-step recursion cell for cell, to about 10⁻¹⁵, with normal and with Student t shocks; simulated means and variances match their closed forms; and a block of uniforms passed as Trials is used as the shocks exactly. Every fit equals an independent replica of its own method, and lands close to an independent maximum-likelihood fit — within 0.011 for ARMA and 0.008 for GARCH and GJR on the test histories.

SARIMA's fit, forecast, simulated paths, backtest and rank each match an independent replica, the forecast exactly; the percentiles of the simulated paths match the forecast table's band; and the fitted optimum matches an independent optimiser of the same objective to within 0.0001. Every diagnostic matches independent references: correlation, covariance, eigenvalues and distances, Pearson and Spearman p-values, Shapiro–Wilk, Anderson–Darling, Jarque–Bera and VIF. The fan chart's percentiles and means match to 10⁻¹⁶.

The rest cover invalid arguments, each of which must return an error rather than a plausible wrong number; timing at 10,000 trials by 60 and by 250 periods; and a call counter showing that nothing recalculates unless its own inputs change. A further 32 checks install both public gists into blank workbooks, save, reopen and recalculate, and confirm that a function the two share gives the same trials whichever gist goes in second.

Every method here is implemented independently from its original publication; no code was taken. The sources are credited here and inside the library itself.