Monte Carlo Simulation in Excel VBA — Code + Free Template (2026)

Worksheet Monte Carlo — RAND() plus a Data Table — is fine for a few thousand trials. But the moment you need 100,000 iterations, triangular or lognormal inputs, or a run that finishes in seconds instead of minutes, you need VBA. In 45 minutes you’ll write a complete, production-quality Monte Carlo macro: the core Randomize/Rnd loop, Box-Muller normal draws, a proper triangular distribution, blazing-fast variant-array output, P10/P50/P90 percentiles, a histogram, and a one-button rerun anyone on your team can use. Every code block is complete and copy-paste ready.

Dr Waqas Rafique · Founder & CTO · PhD, Statistical Machine Learning
LinkedIn · About

What is Monte Carlo Simulation in VBA?

A Monte Carlo simulation answers the question every deterministic spreadsheet dodges: what is the range of outcomes, and how likely is each? Instead of plugging one “best guess” into each input cell, you describe each uncertain input as a distribution — price is normal around £52, units sold are triangular between 800 and 1,500 — then recompute the model thousands of times with fresh random draws. The collection of outputs is the answer: a distribution of profit, complete with a median, a worst-realistic case, and a probability of loss.

Excel can run Monte Carlo two ways. The worksheet way uses RAND() or NORM.INV(RAND(),…) in cells, with a one-column Data Table forcing thousands of recalculations — we cover that in the companion Monte Carlo in Excel (no VBA) tutorial. The VBA way, the subject of this guide, moves the entire trial loop into a macro: the model lives in VBA variables, random numbers come from Rnd, and results stream into an in-memory array. Nothing on the worksheet recalculates until the loop is done.

The difference is not cosmetic. A Data Table recalculates the whole workbook once per trial — on a real model that means 100,000 full recalcs, easily several minutes and sometimes a frozen Excel. The VBA loop computes only the arithmetic the model actually needs, so the same 100,000 trials finish in a second or two. VBA also unlocks things the worksheet approach handles badly: custom distributions, if-then business rules inside a trial, multi-period paths where one year feeds the next, correlated inputs, and reproducible seeded runs for auditing.

The skill transfers everywhere. The exact pattern you will build — seed, loop, draw, compute, store, summarise — is the same one used in risk engines at banks, in project-cost ranging on construction megaprojects, and in pharmaceutical trial design. Excel VBA is simply the most accessible place to learn it, because the model, the code, and the results all live in one file you already know how to use.

Before You Start

Three pieces of groundwork make the build smooth.

You need desktop Excel and a macro-enabled workbook

VBA does not run in Excel Online. Use desktop Excel on Windows or Mac, and save your workbook as .xlsm (macro-enabled) before writing any code — saving as .xlsx silently strips every macro. When you reopen the file, click Enable Content on the security bar or the macro will not run.

Know your model’s logic as plain arithmetic

Write your model out as a formula in words before you code it: profit = units × (price − variable cost) − fixed costs. The VBA loop will compute exactly this line, so any ambiguity in the business logic becomes a bug in the macro. If your model is currently spread across forty cells, condense the chain into a few clear named inputs and one output.

Decide the distribution for each uncertain input

For each input, ask: do I know a minimum, most-likely, and maximum (→ triangular)? A mean and a typical deviation (→ normal)? Just a plausible range with no preferred middle (→ uniform, plain Rnd)? A handful of discrete scenarios with probabilities (→ a discrete draw)? Writing this table on paper first means Step 5’s distribution functions slot straight in.

1.Step 1 — When VBA Beats a Data Table

The worksheet approach — volatile RAND() formulas plus a what-if Data Table — is genuinely good up to a point. Know where that point is so you reach for VBA for the right reasons.

Speed. A Data Table triggers a full workbook recalculation per trial. On a lean model that is fine for 5,000–10,000 rows; on a model with lookups, volatile functions, or linked sheets, 100,000 trials can take minutes and leave Excel unresponsive. A VBA loop executes only the model arithmetic — typically a few dozen floating-point operations per trial — so 100,000 iterations complete in roughly 1–2 seconds on ordinary hardware.

Iteration count. Tail statistics are the whole point of risk analysis, and tails are data-hungry. To pin down a P5 or a probability-of-loss to a stable figure you want 50,000–100,000 trials. Data Tables become painful well before that; VBA does not notice.

Custom distributions. The worksheet can manage uniform (RAND()) and normal (NORM.INV(RAND(),μ,σ)). Triangular is an ugly nested IF; lognormal, discrete scenario draws, and correlated inputs get rapidly worse. In VBA each is a clean five-line Function you write once and reuse forever.

Logic inside a trial. Real models have rules: if demand exceeds capacity, cap it and pay overtime; if cash dips below zero, draw on the facility at 9%. Encoding rules in worksheet Monte Carlo means helper columns and brittle IF chains. In VBA they are ordinary If statements right where the trial is computed.

2.Step 2 — Enable the Developer Tab & Tour the VBA Editor

The Developer tab is hidden by default. Go to File → Options → Customize Ribbon, tick Developer in the right-hand list, and click OK. (Mac: Excel → Preferences → Ribbon & Toolbar.) The tab gives you the Visual Basic button, the Macros list, and the Insert menu for form controls you will use in Step 8.

Open the editor with Alt+F11 (or Developer → Visual Basic). Three panes matter:

Project Explorer (top-left, Ctrl+R if hidden) lists every open workbook and its components — worksheets, ThisWorkbook, and Modules. Your simulation code belongs in a standard module: right-click your workbook’s project → Insert → Module. A new item “Module1” appears; you can rename it to modMonteCarlo in the Properties window (F4).

Code window (the big pane) is where you type. Immediate window (Ctrl+G) is your scratchpad — type ? Rnd and press Enter to see a random number right now, which is also a quick check that VBA is alive.

Finally, put this line at the very top of the module, above any Sub:

Option Explicit forces you to declare every variable with Dim. In a simulation that runs a line of arithmetic 100,000 times, a typo’d variable name silently becoming a new empty variable is the single most expensive bug you can have — this one line makes it a compile error instead.

3.Step 3 — Structure the Model on a Worksheet

Resist the temptation to bury assumptions inside the code. Create a sheet named Model and lay out the inputs where humans can see, audit, and change them. We will use a product-launch profit model throughout — the same one as the no-VBA tutorial:

Select B2 and use the Name Box (left of the formula bar) to name it PriceMean; repeat for PriceSD, UnitsMin, UnitsML, UnitsMax, VCMin, VCMax, FixedCosts, and nSims. Named ranges mean the VBA reads Range("PriceMean") instead of Range("B2") — the code survives someone inserting a row, and it reads like English.

Add a second empty sheet named Output. The macro will write every simulated profit into column A there, plus a summary block. Keeping raw results on their own sheet keeps the Model sheet clean and lets you point charts and PERCENTILE.INC formulas at a stable location.

4.Step 4 — Write the Core Simulation Loop

Here is the heart of the macro — a complete, runnable first version. Paste it into your module under Option Explicit:

Walk through what each part does. Dim … As Long declares the loop counter as a Long — an Integer overflows at 32,767, far below your iteration counts. ReDim results(1 To nSims) sizes the results array once, up front; growing an array inside a loop is catastrophically slow. Randomize seeds VBA’s generator from the clock — call it exactly once, outside the loop. Calling it inside the loop reseeds from the timer every pass, which on fast machines produces runs of identical “random” numbers.

Rnd() returns a uniform random Single in [0, 1). The variable-cost line shows the standard recipe for a uniform draw on any interval: min + Rnd * (max − min). The price and units lines call two functions, RandNormal and RandTriangular, that do not exist yet — that is Step 5. The model line is your business logic, and results(i) = profit banks the trial.

One performance flaw is deliberate here so you can feel the fix later: the loop reads Range("PriceMean").Value on every iteration. Worksheet reads are ~1,000× slower than variable reads. Step 6 hoists them out of the loop, and the speed-up is dramatic.

5.Step 5 — Normal & Triangular Distributions in VBA

Flat Rnd says every value in the range is equally likely — rarely true of prices, demand, or costs. Two distribution functions cover the vast majority of business modelling. Paste both into the same module.

Normal draws with the Box-Muller transform

The Box-Muller transform turns two independent uniform draws into a standard normal: Sqr(−2×Log(u1)) × Cos(2π×u2). Multiply by the standard deviation and add the mean to get any normal you like. The Do…Loop While u1 = 0 guard matters: Rnd can return exactly 0, and Log(0) raises a runtime error — a bug that surfaces roughly once per few million draws, i.e. exactly when you scale up to serious iteration counts. The constant 6.28318530717959 is 2π; VBA has no built-in Pi.

The alternative is one line — Application.WorksheetFunction.Norm_Inv(Rnd, mean, sd) — but each call crosses from VBA into Excel’s calculation engine and back. Inside a 100,000-iteration loop that overhead is measurable; Box-Muller in pure VBA is typically 5–10× faster.

Triangular draws with the inverse-CDF method

The triangular distribution is the workhorse of expert-judgement modelling because its three parameters — minimum, most likely, maximum — are exactly what a sales director or project manager can actually estimate. The function inverts the triangular CDF: a uniform draw u below the mode-fraction f lands on the rising side of the triangle, above it on the falling side, with the square roots mapping uniform probability onto the triangle’s shape.

Bonus: a discrete scenario draw

Stack cumulative probabilities (0.25, then 0.25+0.60 = 0.85) and test the uniform draw against each threshold. Multiply demand by the returned factor inside the loop and your simulation now mixes macro scenarios with input noise — something a worksheet Data Table struggles to express at all.

6.Step 6 — Write Results to the Output Sheet Fast

The naive way to save results — Cells(i + 1, 1).Value = profit inside the loop — is the number-one Monte Carlo performance killer. Every cell write crosses the VBA/Excel boundary, may trigger recalculation, and may repaint the screen. 100,000 cell writes can take minutes. The professional pattern has three parts:

1. Switch Excel’s overheads off while the macro runs, and guarantee they come back on even if the code errors. 2. Hoist all worksheet reads out of the loop into variables. 3. Collect results in a 2-D variant array and write the whole thing in one assignment. Here is the upgraded macro — this replaces Step 4’s version:

The key line is .Range("A2").Resize(nSims, 1).Value = block — one boundary crossing instead of 100,000. The array must be 2-D (1 To nSims, 1 To 1) to land in a column; a 1-D array assigned to a vertical range repeats its first element down the column, a classic gotcha. Name a Model-sheet cell RunSeconds for the timer line, or delete that line if you prefer.

The error handler matters more than it looks. If the macro dies halfway with ScreenUpdating off and calculation on manual, Excel appears broken to the user — nothing repaints, formulas stop updating. The CleanExit block guarantees the application state is restored on every path out of the Sub.

7.Step 7 — Summarise with Percentiles & a Histogram

A hundred thousand numbers in column A are not an answer. Decision-makers need five: the median, the P10–P90 band, the mean, and the probability of a loss. Add this summary block to the end of RunMonteCarloFast, just before the timer line (it reuses the out array, so no sheet reads are needed):

WorksheetFunction.Percentile_Inc accepts a VBA array directly — no need to read results back off the sheet. P10 means 10% of simulated outcomes fall below this value (a realistic downside), P50 is the median, and P90 a realistic upside; together the P10–P90 band brackets the central 80% of futures. P(loss) — the fraction of trials below zero — is often the single number a board most wants.

Binning a histogram

To see the distribution, bin the outcomes. The quickest route on the sheet: in F2:F21 build 20 bin edges from min to max with =MIN(A:A)+(ROW()-1)*(MAX(A:A)-MIN(A:A))/20, select G2:G21, and enter the array formula:

(In Microsoft 365 it spills automatically; in older Excel confirm with Ctrl+Shift+Enter.) Chart F against G as a clustered column with gap width ~3% and you have the classic Monte Carlo bell. Alternatively, Insert → Statistic Chart → Histogram bins the raw column directly with zero formulas. If the shape is skewed right with a fat left tail near zero, that asymmetry — invisible in any single-point estimate — is exactly the insight the simulation exists to surface.

8.Step 8 — Turn It into a Reusable One-Button Macro

The last step turns your macro from “a thing the analyst runs from Alt+F8” into a tool the whole team uses. Three finishing touches:

Parameterise from cells. You already read nSims from a named cell — that is the pattern. Anything a user might want to vary (iterations, the scenario probabilities, even which distribution to use via a dropdown) should live on the Model sheet, validated with Data Validation so nobody types “lots” into the iterations cell. Guard it in code too:

Add a button. Developer → Insert → Button (Form Control) — the grey one in the top-left of the gallery, not the ActiveX version. Draw it on the Model sheet, pick RunMonteCarloFast in the Assign Macro dialog, and rename it to “▶ Run 100,000 simulations”. Form Controls are stabler than ActiveX, work on Mac, and survive being copied between workbooks.

Report the run. The Timer lines you added in Step 6 write elapsed seconds to RunSeconds; add Range("RunStamp").Value = Now beside it. A status-bar progress indicator keeps long runs honest:

Put the progress check on a coarse stride (Mod 10000) — updating the status bar every iteration would cost more than the simulation itself. Remember Application.StatusBar = False in the CleanExit block to hand the bar back to Excel.

That is the complete tool: assumptions on a sheet, distributions as reusable functions, a fast loop, a one-shot write, a percentile summary, a histogram, and a button. Change an assumption, click, and 100,000 fresh futures arrive in under two seconds.

Try It: VBA vs Data Table Speed Demo

This widget runs the tutorial’s exact profit model — Normal price, Triangular units, Uniform variable cost, £20,000 fixed costs — live in your browser. Pick an iteration count and hit Run: you get the elapsed milliseconds, the P10/P50/P90, the probability of loss, and a histogram of outcomes. JavaScript loops, like VBA loops, compute in memory — so watch how gently the time grows from 1k to 100k trials, then toggle the comparison note to see why a worksheet Data Table cannot scale the same way.

Advanced Monte Carlo VBA Tips

Correlate two inputs

Price and demand are rarely independent — cut price and units usually rise. The simplest sound method: draw two independent standard normals z1, z2 with RandNormal(0, 1), then build the second correlated draw as z2c = rho * z1 + Sqr(1 - rho ^ 2) * z2, where rho is the desired correlation (e.g. −0.5). Feed z1 into the price transform and z2c into demand. Ignoring real correlations is the most common way Monte Carlo results end up too optimistic, because diversification you don’t actually have appears to cancel risk.

Seeded, reproducible runs for audit

Before Randomize, the pair Rnd -1 then Randomize 42 fixes the random sequence: every run with seed 42 produces identical results. Store the seed in a cell next to the iteration count. When a number in a board pack is questioned six weeks later, you can regenerate the exact run that produced it — the difference between “trust me” and evidence.

Multi-year paths

Wrap a second loop: For yr = 1 To 5 inside For i = 1 To nSims, carrying cash forward year to year and applying rules (draw on the overdraft below zero, repay above a buffer). Store the year-5 cash position — or the minimum across the path, which is what covenant tests care about — as the trial result. This is precisely the structure that worksheet Monte Carlo cannot express without heroic helper-column engineering, and it pairs naturally with the cash flow forecast model.

Which input drives the risk?

Store each trial’s inputs alongside its output (a 4-column block instead of 1), then correlate each input column with the profit column using CORREL. The input with the largest absolute correlation dominates your risk — usually demand, sometimes price. Rank them in a bar chart and you have a tornado-style sensitivity view assembled from simulation data; the deterministic version of that analysis is covered in sensitivity analysis in Excel.

From macro to shared live dashboard

A .xlsm full of VBA cannot be emailed past many corporate filters and will not run in a browser. When the simulation needs an audience rather than an author, upload the output table to DataHub Pro — it turns the results sheet into an interactive web dashboard with percentile charts your stakeholders open from a link, no macros, no security warnings.

Worked Example: Product Launch, 100,000 Trials

Pulling the whole tutorial together with the Model-sheet numbers from Step 3: price ~ Normal(£52, £4), units ~ Triangular(800; 1,100; 1,500), variable cost ~ Uniform(£18, £26), fixed costs £20,000. The single-point “base case” most spreadsheets would report is 1,100 × (52 − 22) − 20,000 = £13,000 profit. Now run RunMonteCarloFast with 100,000 iterations (typical elapsed time: ~1.3 s) and the summary block tells a richer story:

Note what the simulation changed. The deterministic answer was “£13,000”. The probabilistic answer is “most likely around £13,700, an 80% chance of landing between £2,800 and £26,300, and a ~3.5% chance of losing money.” Those are different conversations: the second one supports a real decision — perhaps the 3.5% loss risk is acceptable, perhaps it triggers a cheaper launch variant. Rerun with the seed fixed and the percentiles reproduce exactly; rerun with a fresh seed and P50 moves by only a few pounds at 100,000 trials, which is precisely why the high iteration count — free in VBA, painful in a Data Table — is worth having.

Your histogram should show a roughly bell-shaped distribution, mildly right-skewed, with the left tail just crossing zero. If it looks very different — bimodal, or a hard cliff — suspect a logic bug in the trial computation before suspecting the business.

Common Errors & Fixes

Every run produces identical results

Randomize is missing, or you called Rnd with a negative argument somewhere, freezing the seed. Add a single Randomize at the top of the Sub (outside the loop). If you want identical runs, that is the seeding recipe — just make it deliberate and documented.

Run-time error 5 — Invalid procedure call (in RandNormal)

Rnd returned exactly 0 and the code took Log(0). Use the Do … Loop While u1 = 0 guard from Step 5. The error is rare at 1,000 trials and near-certain over many large runs — the classic bug that only appears in production.

Overflow (error 6)

A counter or accumulator is declared As Integer, which tops out at 32,767. Declare loop counters and counts As Long and money/values As Double.

The output column shows the same value repeated

You assigned a 1-D array to a vertical range. Excel needs a 2-D array shaped (rows, 1) — use ReDim block(1 To nSims, 1 To 1) as in Step 6, or transpose, or write with Application.Transpose (slower and capped at ~65k elements, so the 2-D shape is the right fix at scale).

Excel looks frozen / formulas stopped calculating after a crash

The macro errored while ScreenUpdating was off or calculation was manual, and never restored them. Run a one-line fix in the Immediate window: Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic — then add the On Error … CleanExit pattern from Step 6 so it cannot happen again.

The macro is gone when I reopen the workbook

The file was saved as .xlsx, which strips VBA. Save as .xlsm, and if macros are disabled on open, click Enable Content on the yellow security bar.