The simulation framework: four steps, three sources of scenarios

Why simulate, and the four steps

A simulation starts from assumptions about how some input variables can move, generates many possible scenarios for them, and measures what each scenario does to an output you care about: a portfolio's profit and loss, a security's value, a fund's volatility.

Step 1, setup/parameterize. Define the goal and the output variable (a price, a return, a value), choose the input variables (risk factors such as share returns or yield changes), and decide the calculations that turn inputs into outputs.

Step 2, scenario generation. Produce the input scenarios. They range from a few user-defined cases to thousands of machine-generated vectors, one vector per scenario.

Step 3, scenario evaluation (calculation engine). Apply the calculations from step 1 to every scenario, for example asset returns, then portfolio returns from the weights, then risk measures. Step 4, result compilation/analysis. Gather all outputs into a distribution and draw conclusions: expected value, dispersion, downside tail, value at risk.

Three ways to generate scenarios

Historical simulation uses the changes in the risk factors that actually happened, for example each trading day of the past year, and applies them to today's positions. No randomness is added: the past *is* the set of scenarios.

Bootstrapping also starts from historical data but adds randomness: it draws observations from the sample with replacement, so the same day can be used several times and another not at all. That creates many synthetic datasets with the same empirical distribution as the original.

Monte Carlo simulation makes distributional assumptions (for example, normal returns with a chosen mean, standard deviation and correlation) and draws random numbers from those distributions. History may be used to estimate the parameters, but the scenarios themselves are generated by the model.

Because the differences sit almost entirely in step 2, a well-designed system has one central engine that can process scenarios from any source, in large enough numbers to give precise estimates.

When simulation beats a formula

Simple parametric shortcuts (such as a normal-distribution VaR) work well for linear positions and well-behaved returns. Simulation earns its cost when the output is a non-linear function of the inputs, such as an option, or when the payoff depends on the whole path of prices, such as an option on the average price or a callable mortgage.

The quality of any simulation depends on its assumptions, explicit or implicit. Historical simulation implicitly assumes the past repeats; bootstrapping assumes the sample is representative; Monte Carlo explicitly assumes a model. The output is only as realistic as that assumption.

One engine, three fuels: replayed history, reshuffled history or model-made randomness. Ask first where you trust the scenarios to come from, then pick the method.

Building it in a spreadsheet

All three methods can be prototyped in Excel or Google Sheets, although production systems usually run in Python, MATLAB or purpose-built software. The building blocks are a handful of functions.

For Monte Carlo, RAND() returns a uniform number between 0 and 1 and NORM.INV(RAND(), 0, 1) turns it into a standard normal draw (LOGNORM.INV gives a lognormal one). For bootstrapping, INDEX(B:B, RANDBETWEEN(2, N)) picks a random historical row, with replacement. For historical simulation, SORT and PERCENTILE.INC/PERCENTILE.EXC (or SMALL) rank the outcomes and read off the tail.