Monte Carlo simulation in Google Sheets, without an add-on

The whole build in five columns, the numbers it produces, and the five things it cannot give you · figures computed 6 September 2026 at seed 20260906

Yes, you can. A Monte Carlo simulation is a loop, a random draw and a sorted column, and a spreadsheet does all three natively. This page gives you the entire build, in formulas you can retype in about ten minutes, on a real model with real numbers. Then it is honest about where that build runs out, because it does, and the places it runs out are specific enough to name and to measure.

Nothing below needs anything installed. If the build turns out to be all you need, that is a good outcome and you should stop there.

The model

A renovation quoted at $30,000. Two things are genuinely unknown: how far the job runs over the quote, and what the walls are hiding. Four cells:

Put the most likely value in each uncertain cell and the sheet answers $37,500. That is the number this page exists to interrogate, because it is not a middle of anything. It is one arithmetic path through a model with two ranges in it, and by the end of this page you will know that about 88% of plausible futures land above it.

The build: five columns and one row, copied down

Every trial is one row. A row draws its own random values, runs the model, and writes one total. Ten thousand trials is ten thousand rows, on their own tab.

Columns A and B are the raw randomness, one draw per uncertain input. Columns C and D turn each draw into a value of the right shape. Column E is the model.

Where those constants come from, so you can do it for your own ranges

C2 and D2 are the same formula twice. It is the inverse of a triangular distribution: give it a number between 0 and 1 and it hands back a value that is common near the most likely figure and rare near the ends, which is what a three-point estimate means. For a low a, a most likely c and a high b, with the draw in A2:

=IF(A2 < (c-a)/(b-a), a + SQRT(A2*(b-a)*(c-a)), b - SQRT((1-A2)*(b-a)*(b-c)))

For the overrun, a is 1.0, c is 1.15 and b is 1.6, so the threshold is 0.15/0.6 = 0.25, and the two products are 0.6 × 0.15 = 0.09 and 0.6 × 0.45 = 0.27. For the repairs, 3000/15000 = 0.2, and the products are 15000 × 3000 = 45,000,000 and 15000 × 12000 = 180,000,000. You can check the formula in your head at the join: feed it 0.25 and both branches meet at 1.15, the most likely value, as they must.

Copy A2:E2 down to row 10001 and you have ten thousand renovations.

Reading the column

The ten thousand totals in E are the answer. Four formulas turn them into something you can say out loud:

The quote is $30,000 and the sheet's own single-number total is $37,500. Half of these ten thousand renovations cost more than $43,100, and one in five costs more than $47,950. That gap, between the number on the quote and the number worth actually holding, is the contingency people forget to set aside, and no amount of staring at a single-cell answer would have shown it. The last row is a tornado chart with the chart taken off: the overrun factor moves the total more than the surprise repairs do, 0.77 against 0.65, so if you can only tighten one estimate before the work starts, tighten that one. It is a narrow win, not a license to ignore the other.

These are not approximate numbers

It is worth being clear about what this build is, because people assume a formula version must be a rough stand-in for a real simulation. It is not. It is the same method.

Fed the same stream of uniform draws, the ten thousand totals this grid produces and the ten thousand the Sortia engine produces on the same model, with Latin hypercube sampling switched off, agree far past any figure you would ever print. Checked at seed 20260906, about four fifths of the pairs are identical to the last digit, and not one of the ten thousand differs from its twin by as much as a millionth of a cent. The gap on the rest has a single cause worth naming: the constants above are the rounded ones you would type, 0.09 and 0.27, while the engine multiplies each range out as it runs, and 1.6 minus 1.0 is not exactly 0.6 in binary arithmetic. The two-branch formula in C2 is the engine's own inverse for a triangular input, written out, so this is not two methods agreeing, it is one method in two places, which is the point. PERCENTILE in Sheets is the same inclusive percentile the engine's own ladder uses. Your sheet will produce different numbers, because RAND hands you a different stream, but not a different kind of number.

Formula-grid columns: the build above, evaluated with a seeded uniform stream so the run can be repeated. Engine column: the same model run through the same engine the add-on runs, 10,000 trials, Latin hypercube sampling, seed 20260906. The model is the shipped Home Renovation: True Cost template, cell for cell. One honest difference in the driver row: CORREL is the ordinary correlation and the engine's tornado ranks by rank correlation instead, which on this run reads 0.75 and 0.63 against the grid's 0.77 and 0.65. A different statistic, the same ranking, and the ranking is what you act on.

Now type anything into an empty cell

Every number on the tab changes. RAND is volatile: it redraws whenever the spreadsheet recalculates, and that includes any edit you make anywhere in the file. Your P80 was $47,951 a second ago and it is something else now. There is no seed argument, so there is no way to ask for that draw back.

This is not a cosmetic annoyance. It is the size of the answer's wobble, made visible, and it is worth measuring rather than shrugging at. Two hundred independent rebuilds of the same model, reading the same P80 each time:

A thousand rows is not enough to quote a number to the nearest hundred dollars: across two hundred rebuilds the P80 covers more than a thousand dollars of ground, and the share of jobs over $50,000 wanders between 8.1% and 13.2%. Ten thousand rows cuts the standard deviation of that P80 from $233 to $82. Latin hypercube, which spreads the draws evenly across each input's range instead of leaving it to chance, reaches $50 at the same ten thousand trials. Fifty thousand ordinary rows are steadier still, at $35, so the honest claim is the narrow one: spreading the draws is worth between two and three times as many ordinary rows on this model, and it is not a substitute for running more of them.

There is a workaround for the reshuffling and you should know it: select column E, copy, and paste values only into a fresh column. The numbers stop moving, because they stop being formulas. What you have then is a frozen sample rather than a reproducible one. You can defend it by showing the column, but nobody can regenerate it from your model, and if you change an input you start again from scratch.

The five things this build cannot do

1. Give you a number somebody else can reproduce. There is no seed, so a colleague who rebuilds your sheet gets a different answer, and so do you tomorrow. Freezing by paste-values preserves the number and throws away the derivation. This is the gap that matters most in any setting where the figure gets reviewed, which is most settings where it is worth computing. What a seeded draw looks like.
2. Handle a model that is not one row. The pattern above works because the whole renovation model fits in a single row of formulas. A three-year savings projection is thirty-six months of compounding, so one trial is thirty-six columns wide and ten thousand trials is a 360,000-cell block with every reference re-pointed by hand. A schedule with predecessors, a loan amortisation, anything that iterates: the grid stops being a ten-minute build and becomes a project of its own.
3. Let two inputs move together. Every RAND is independent of every other, so this build quietly assumes the overrun and the hidden repairs have nothing to do with each other. Real cost lines move together, because one wage settlement or one bad winter reaches all of them in the same year, and independent draws make the tail look thinner than it is. The general fix is to reorder each input's column so the ranks match a target correlation matrix, which needs that matrix's Cholesky factor, and it is not something you write between two other formulas. What it is worth: the same four cost lines run twice put a reserve at the independent P95, and the correlated run breaches it on about 15% of trials rather than the 5% it was sold as.
4. Draw a shape you cannot invert by hand. Triangular was the friendly case: two lines of algebra, done above. A bell curve is one function call, =NORM.INV(A2, mean, sd). Beyond those, each shape needs its own inverse written out, and several of the ones people actually reach for, a frequency of events multiplied by a severity, a wait that ends when a target is hit, a count, have no elementary inverse at all. The Sortia engine ships 36 shapes for this reason. If you do not know which one your data wants, ranking the candidates is its own job.
5. Get to a steady answer in fewer rows. Ordinary random draws clump, and the table above is what clumping costs: at ten thousand trials each, the formula grid's P80 has a standard deviation of $82 against $50 for draws that were spread deliberately, so it takes between two and three times as many ordinary rows to match them. Every technique that buys precision per trial, Latin hypercube among them, has to sit outside the cell that draws the number.

When the formula build is the right answer

Often. It is genuinely the right tool when:

• the model fits on one row, which covers most cost, margin and break-even questions;
• there are two or three uncertain inputs, not fifteen;
• the shapes you need are ones you can write down, so uniform, triangular or normal;
• the inputs really are independent, or the answer does not turn on the tail;
• you want the shape of the answer, not a figure that will be quoted back at you.

If all five are true, build the grid. You will learn more from watching your own model wobble than from any explanation of why it wobbles, and you will own every formula in it.

What installing changes, specifically

Sortia runs the same method on the model already sitting in your sheet, without a grid. You point it at the cells that are guesses, say what each one could be, point it at the answer cell, and press run. The differences are the five above, in the same order: a seed box, so the same model returns the same numbers on any machine; the model can be any shape, because the engine re-evaluates your actual cells rather than needing them flattened into a row; rank correlation between inputs, entered pair by pair or read from a matrix on your sheet; 36 distribution shapes, including the ones with no closed-form inverse; and Latin hypercube sampling, on by default. The report lands on its own tab with the percentile ladder, the tornado ranking and a written reading of the result, and it names the trial count and the seed so the run can be checked.

The trial ceiling is on the performance page, measured rather than claimed: 100,000,000 trials in a single run. A model built on a function the in-browser engine does not implement falls back to recalculating the sheet for every trial, and that slower path stops at 300 trials, which the panel tells you before it starts. Monte Carlo is one of the five Pro engines. Every free install includes five full-quality runs on your own numbers, at any model size, shared across all five Pro engines rather than five for each.

Where these numbers came from

Every figure on this page was computed on 6 September 2026 from the shipped Home Renovation: True Cost model, quote 30000, overrun triangular 1.0 / 1.15 / 1.6, repairs triangular 0 / 3000 / 15000, total = quote × overrun + repairs. The formula-grid columns evaluate exactly the formulas printed above, against a seeded uniform stream at seed 20260906 so the run repeats. They were evaluated outside a spreadsheet for that reason and that reason only: a sheet cannot be seeded, which is the whole subject of the section above. The engine column is the same engine the add-on runs, 10,000 trials with Latin hypercube. The rebuild table is 200 runs of each build, at seeds 500,000 + 13k for k = 0 to 199, reporting the lowest and highest P80 seen and the standard deviation across the 200. Compare on the standard deviation: the highest and lowest of a run of rebuilds is itself a jumpy figure, and over only twenty rebuilds it moves by more than the gaps between these rows. The two-to-three-times figure is the ratio of the two variances at ten thousand trials each, which is 2.7 here and 2.6 on a second run of a thousand rebuilds at other seeds, so it is quoted as a range and not as a point. Percentiles are the inclusive kind, which is what =PERCENTILE() returns. Build the grid yourself and you will land near these figures and not on them, which is the point of the rebuild table.

Next

• Monte Carlo simulation, the method, and every model on this site that uses it.
• The renovation model in full, the one this page borrowed.
• What ignoring correlation costs you, priced in reserve dollars.
• Reproducible random draws, with a seed.
• Which distribution fits your data, ranked.
• Performance, the measured ceilings.
• Pricing, if you decide the formula-only build is not worth rebuilding every time the model changes.
• Start here, if the spreadsheet itself is the new part.

Something here wrong, or a build you think is better? Email support@sortia.io.