Monte Carlo simulation in Excel for a decision: what to expect

What does a Monte Carlo simulation in Excel involve?

The task has four parts: a model that computes the result of one trial, random draws for the uncertain inputs, a block of many trials, and a summary of the results. Excel handles all four with built-in functions. A finance team that already builds the model in a sheet only needs to replace fixed inputs with draws and copy the formula down.

The approach suits models with up to about 5 uncertain inputs and a few thousand trials. The spreadsheet stays readable, so a reviewer can open any row and check the arithmetic. This transparency is the main reason that many teams start with Excel before they consider a dedicated tool. The same workbook can be handed to a colleague, who needs no training to follow it.

How do you set up the model sheet for a simulation in Excel?

Place the inputs at the top: for each one, a minimum and a maximum in two cells. Below them, build a table in which every row is one trial. The columns hold the drawn inputs, then the result of the model for that row. A block of 1,000 rows gives a first answer, and 10,000 rows give a steadier one.

A what-if data table in Excel handles only one or two variables (support.microsoft.com data table page), so a row-per-trial layout suits a model with three or more uncertain inputs. Keep the summary on a separate area, and name the cells of the ranges so that formulas read clearly. A short note beside each range gives its source, such as a quote or a past project.

Which Excel formulas draw random inputs for a simulation?

The RAND function returns a random real number that is at least 0 and below 1, evenly distributed, and a new number every time the worksheet calculates. A uniform draw between a minimum and a maximum is therefore the minimum plus RAND times the difference between the maximum and the minimum. Copy the formula down the column.

Take a new service line, all figures illustrative. Customers are drawn between 300 and 700, and the annual margin per customer between 800 and 1,400. The result column multiplies customers by margin and subtracts fixed costs of 520,000. A triangular range needs a longer formula built on RAND, and the uniform draw suits inputs with no favourite value.

What does the Excel simulation return for the service line?

At the midpoints, 500 customers and 1,100 margin give 550,000 and a profit of 30,000 after fixed costs. A simulation with 100,000 trials returns an average of about 30,000 as well, since the ranges are symmetric. The average hides the risk. About 46 percent of the trials end in a loss.

The 10th percentile is a loss of about 164,000, the 50th percentile is a profit of about 16,000, and the 90th percentile is a profit of about 251,000. The project looks acceptable on the midpoint and uncertain on the spread. The midpoint plan shows a profit of 30,000, and the spread shows the other side. The decision maker now knows that almost half the trials lose money. A board can weigh that statement against the upside, which reaches about 251,000 in the best tenth of trials.

Which formulas summarise the results of an Excel simulation?

Three summary formulas cover most needs. AVERAGE returns the mean of the result column. A count of the cells below zero divided by the count of all cells returns the share of trials that lose money. PERCENTILE.INC returns the k-th percentile of a range, where k runs from 0 to 1 and 0.9 gives the 90th percentile.

Add a histogram built from bins of equal width, and mark the plan value on it. A reader sees the shape of the risk in seconds. Label the summary with the number of trials and the date, because the figures change at the next recalculation and a labelled snapshot can be traced. Keep the plan value beside the summary, so that the gap between plan and average stays in view.

What happens to the results each time Excel recalculates?

RAND produces a new number at every calculation, including when you press F9 or enter data in another cell, so every summary figure moves. Excel offers no seed for RAND. The movement is smaller with more rows: five recalculations of 100 rows returned a loss share between 36 and 49 percent, and 1,000 rows narrowed it to between 44 and 47 percent.

With 10,000 rows, the loss share stayed between 46 and 47 percent. Choose a row count at which the headline figure holds to about a point, and paste the final values as numbers to freeze the version used in the decision. A frozen copy also gives the reviewer something stable to open.

When does a decision need more than a Monte Carlo simulation in Excel?

Excel becomes heavy with many inputs, many periods, or linked inputs. Draws in separate columns are independent, so inputs that move together, such as customers and margin in a weak market, need explicit linking, or the spread is understated. Version control and review also become hard when the workbook grows.

The EQOS Asia decision engine takes a deterministic path: its Codex Universalis core returns the same result every time the same facts are entered (EQOS Asia method page, as of October 2026). A reader can try a first score in the simulator and compare it with the simulated range.

Frequently asked questions

About the author

Stéphane Jambu

President, EQOS Forecast Asia

Stéphane Jambu leads EQOS Forecast Asia and works as a consultant in e-commerce, search visibility, and AI. LinkedIn profile