Monte Carlo Simulation in Excel: Risk Analysis That Works (2026)

Most financial models lie to you with a single number. A DCF model spits out one enterprise value. A project finance model returns one IRR. But every input driving that output — revenue growth, discount rate, commodity prices — carries uncertainty. Monte Carlo simulation forces your model to confront that uncertainty by running thousands of randomized scenarios, giving you a probability distribution instead of a false-precision point estimate.

The good news: you don't need expensive add-ins like @RISK or Crystal Ball. Excel's built-in RAND(), NORM.INV(), and Data Table feature are everything you need to run a robust Monte Carlo simulation. This guide walks you through building one from scratch.

What Is a Monte Carlo Simulation?

A Monte Carlo simulation is a computational technique that uses repeated random sampling to model the probability of different outcomes in a system with uncertain variables. Instead of plugging in your "best guess" for each assumption, you define a probability distribution for each uncertain input, generate thousands of random draws, and observe the range of outputs.

The method is named after the Monte Carlo Casino in Monaco — a fitting metaphor for modeling outcomes driven by randomness.

Why Finance Professionals Use Monte Carlo

Use Case	What It Models	Key Output
DCF valuation	Revenue growth, margins, WACC	Probability distribution of enterprise value
Project finance	Construction costs, energy prices, utilization	P50/P90 DSCR scenarios
Portfolio optimization	Asset returns, correlations	Value at Risk (VaR), CVaR
Capital budgeting	Project NPV under cost/revenue uncertainty	Probability of negative NPV
Option pricing	Underlying asset price paths	Fair value of exotic options
Real estate pro forma	Occupancy rates, rent growth, cap rates	Distribution of levered IRR

💡 Pro Tip: Monte Carlo is most valuable when your model has 3+ uncertain inputs that interact with each other. For single-variable sensitivity, a standard Data Table or tornado chart is simpler and sufficient.

How Does Monte Carlo Simulation Work in Excel?

Monte Carlo simulation in Excel works by combining random number generation functions (RAND, NORM.INV, RANDBETWEEN) with Excel's Data Table feature to run hundreds or thousands of iterations automatically. Each iteration draws new random values for your uncertain inputs, recalculates your model, and records the output. After all iterations complete, you analyze the collected results statistically.

Here's the core process:

1. Define your uncertain variables and their probability distributions
2. Replace fixed assumptions with formulas that generate random values
3. Link everything to a single output cell (e.g., NPV, IRR)
4. Use a Data Table to run N iterations and capture each result
5. Analyze the distribution of results (mean, percentiles, histogram)

graph TD
    A[Define Uncertain Inputs] --> B[Assign Probability Distributions]
    B --> C[Generate Random Values with RAND/NORM.INV]
    C --> D[Model Recalculates Output]
    D --> E[Data Table Records Result]
    E --> F{More Iterations?}
    F -->|Yes| C
    F -->|No| G[Analyze Distribution of Outputs]
    G --> H[Mean, Median, Percentiles]
    G --> I[Histogram / Frequency Chart]
    G --> J[Probability of Target Outcome]

Which Probability Distributions Should You Use?

Choosing the right probability distribution for each input is the single most important decision in your simulation. Here's a practical guide for financial modelers:

Normal Distribution (NORM.INV)

Best for: variables that cluster around a central value with symmetric upside/downside risk.

=NORM.INV(RAND(), mean, standard_deviation)

Example: If you expect revenue growth of 8% with a standard deviation of 3%:

=NORM.INV(RAND(), 0.08, 0.03)

This generates values like 5.2%, 8.7%, 11.1%, etc. — centered around 8% with most values within one standard deviation.

Triangular Distribution

Best for: variables where you can estimate a minimum, most likely, and maximum value but don't have enough data for a full statistical distribution.

=IF(RAND()<(mode-min)/(max-min),
    min+SQRT(RAND()*(max-min)*(mode-min)),
    max-SQRT((1-RAND())*(max-min)*(max-mode)))

⚠️ Warning: The triangular distribution formula above uses two separate RAND() calls, which means each call generates an independent random number. For a cleaner implementation, store a single RAND() result in a helper cell and reference it in the formula.

Uniform Distribution (RANDBETWEEN)

Best for: variables where any value within a range is equally likely.

=RANDBETWEEN(low * 100, high * 100) / 100

Example: If construction costs could fall anywhere between $45M and $55M:

=RANDBETWEEN(4500, 5500) / 100

Lognormal Distribution

Best for: asset prices, revenue multiples, and anything that can't go negative and has right-skewed outcomes.

=LOGNORM.INV(RAND(), LN(median), volatility)

Distribution Selection Guide

Variable Type	Recommended Distribution	Excel Function
Revenue growth rate	Normal	NORM.INV(RAND(), mean, stdev)
Commodity prices	Lognormal	LOGNORM.INV(RAND(), ln_mean, vol)
Construction costs	Triangular	Custom formula (min, mode, max)
Interest rates	Normal (bounded)	MAX(0, NORM.INV(RAND(), mean, stdev))
Binary events (win/lose deal)	Bernoulli	IF(RAND()<prob, 1, 0)
Project timeline	PERT/Triangular	Weighted triangular formula
Tax rates	Uniform	RANDBETWEEN(low, high)

Step-by-Step: Build a Monte Carlo Simulation in Excel

Let's build a practical example: estimating the NPV of a 5-year project with uncertain revenue, costs, and discount rate.

Step 1: Set Up Your Base Case Model

Create a simple project cash flow model with these assumptions:

Assumption	Base Case	Distribution	Parameters
Initial investment	$10,000,000	Fixed	N/A
Annual revenue	$4,000,000	Normal	Mean = $4M, SD = $600K
Annual costs	$2,200,000	Normal	Mean = $2.2M, SD = $300K
Revenue growth	5%	Normal	Mean = 5%, SD = 2%
Cost inflation	3%	Normal	Mean = 3%, SD = 1%
Discount rate	10%	Normal	Mean = 10%, SD = 1.5%

Step 2: Replace Fixed Inputs with Random Formulas

In cells designated for your stochastic inputs, replace the fixed values:

Cell B2 (Revenue):      =NORM.INV(RAND(), 4000000, 600000)
Cell B3 (Costs):        =NORM.INV(RAND(), 2200000, 300000)
Cell B4 (Rev Growth):   =NORM.INV(RAND(), 0.05, 0.02)
Cell B5 (Cost Inflate): =NORM.INV(RAND(), 0.03, 0.01)
Cell B6 (Discount):     =MAX(0.01, NORM.INV(RAND(), 0.10, 0.015))

ℹ️ Note: The MAX(0.01, ...) wrapper on the discount rate prevents impossible negative discount rates. Always add logical bounds to your random inputs.

Step 3: Build the Cash Flow and NPV Calculation

With the random inputs feeding into your model, calculate annual free cash flow and NPV:

Year 1 FCF:  =B2 - B3
Year 2 FCF:  =B2*(1+B4) - B3*(1+B5)
Year 3 FCF:  =B2*(1+B4)^2 - B3*(1+B5)^2
Year 4 FCF:  =B2*(1+B4)^3 - B3*(1+B5)^3
Year 5 FCF:  =B2*(1+B4)^4 - B3*(1+B5)^4

Then calculate NPV:

=NPV(B6, Year1FCF, Year2FCF, Year3FCF, Year4FCF, Year5FCF) - InitialInvestment

Every time the spreadsheet recalculates (press F9), each RAND() generates new random values, producing a different NPV. That's one "simulation."

Step 4: Set Up the Data Table for 1,000 Iterations

This is where the magic happens. Excel's Data Table runs your model N times automatically:

1. In column A (say A10:A1009), enter the numbers 1 through 1,000 (these are iteration counters)
2. In cell B9, enter a formula that references your NPV output cell: =G7 (wherever your NPV lives)
3. Select the range A9:B1009 (the header row plus all 1,000 rows)
4. Go to Data → What-If Analysis → Data Table
5. Leave the Row input cell blank
6. For the Column input cell, select any empty cell that is NOT used in your model (e.g., Z1)
7. Click OK

Excel will populate column B with 1,000 different NPV results — each calculated with a fresh set of random inputs.

💡 Pro Tip: The column input cell trick works because Excel's Data Table forces a recalculation for each row. Since your model uses RAND(), every recalculation generates new random values even though the "input" cell doesn't actually affect anything. This is the standard workaround for running Monte Carlo without VBA.

Step 5: Analyze the Results

With 1,000 NPV values in column B, calculate summary statistics:

Mean NPV:           =AVERAGE(B10:B1009)
Median NPV:         =MEDIAN(B10:B1009)
Standard Deviation:  =STDEV(B10:B1009)
Minimum NPV:        =MIN(B10:B1009)
Maximum NPV:        =MAX(B10:B1009)
5th Percentile:     =PERCENTILE(B10:B1009, 0.05)
95th Percentile:    =PERCENTILE(B10:B1009, 0.95)
P(NPV < 0):         =COUNTIF(B10:B1009, "<0") / 1000

That last formula — probability of negative NPV — is arguably the most important output. If your Monte Carlo shows a 35% chance of negative NPV, that's a fundamentally different risk profile than a base case showing positive NPV.

How Do You Interpret Monte Carlo Results?

The power of Monte Carlo isn't the mean — it's the shape of the distribution. Here's what to look for:

Key Metrics to Report

• Mean / Expected Value: The average outcome across all simulations. Compare this to your deterministic base case — if they differ significantly, your base case has optimistic (or pessimistic) bias.
• P10 / P50 / P90: The 10th, 50th, and 90th percentile outcomes. Lenders in project finance commonly underwrite to P90 (downside) scenarios.
• Probability of breakeven: What percentage of iterations produce NPV > 0 (or IRR > hurdle rate)?
• Coefficient of Variation (CV): Standard deviation divided by mean. Higher CV = more risk per unit of expected return.

Build a Histogram

Create a frequency distribution to visualize the results:

1. Define bins (e.g., -$5M to $15M in $1M increments)
2. Use =FREQUENCY(B10:B1009, bins) to count how many results fall in each bin
3. Create a bar chart from the frequency data

The resulting histogram shows you the full probability distribution of project outcomes — far more informative than a single NPV number.

Example: If your histogram shows a bell curve centered at $2.5M NPV with a long left tail extending to -$4M, you know the project has positive expected value but meaningful downside risk. A risk-averse firm might reject this project despite the positive mean.

How Many Iterations Do You Need?

A Monte Carlo simulation with too few iterations produces unstable results. Too many wastes computation time. Here's a practical guide:

Scenario	Recommended Iterations	Why
Quick sanity check	500	Fast feedback, rough estimate
Standard analysis	1,000–5,000	Stable mean and standard deviation
Tail risk analysis (VaR)	10,000+	Need precision at extreme percentiles
Regulatory/audit submissions	10,000–100,000	Reproducibility and confidence required

Convergence test: Run 500 iterations and note the mean. Then run 1,000 and compare. Keep doubling until the mean stabilizes (changes by less than 1–2%). That's your minimum iteration count.

⚠️ Warning: Excel's Data Table slows down significantly beyond 5,000 rows. For 10,000+ iterations, consider using VBA with Application.ScreenUpdating = False to avoid interface lag, or switch to a LAMBDA-based approach that avoids volatile functions.

Common Mistakes in Monte Carlo Simulations

1. Ignoring Correlations Between Inputs

If revenue and costs are correlated (they usually are — higher revenue often means higher COGS), treating them as independent random variables overstates uncertainty. Use the Cholesky decomposition technique to introduce correlation:

=NORM.INV(RAND(), mean1, sd1)   ' Variable 1 (independent)

' Variable 2 (correlated with Variable 1):
=NORM.INV(corr * NORM.S.INV(RAND()) + SQRT(1 - corr^2) * NORM.S.INV(RAND()), mean2, sd2)

2. Using Unrealistic Distributions

Don't default to normal distributions for everything. Revenue can't go negative, so a lognormal distribution is more appropriate. Construction costs are typically skewed right (overruns are more common than under-budget). Match the distribution to the actual behavior of each variable.

3. Not Anchoring to Historical Data

Your distribution parameters (mean, standard deviation) should be grounded in historical data or industry benchmarks — not guesses. If you're modeling oil prices, use the actual historical volatility of crude oil. If you're modeling SaaS churn, use your trailing 12-month data.

4. Forgetting to Lock the Random Seed

Each time Excel recalculates, RAND() generates new values and your entire simulation changes. To create a reproducible simulation for auditing or presentation, either:

• Copy-paste your results as values (Ctrl+Shift+V)
• Use VBA to set Rnd(-1) before each run for a fixed seed

Monte Carlo Without VBA: The Data Table Method vs. LAMBDA Approach

Data Table Method (Excel 2016+)

Pros: No coding required, works in any modern Excel version, easy to set up.

Cons: Slow beyond 5,000 iterations, can't easily parallelize, results recalculate every time the sheet changes.

LAMBDA + REDUCE Method (Excel 365)

For Excel 365 users, you can build a Monte Carlo engine using LAMBDA and dynamic arrays that avoids the Data Table entirely:

=LET(
    n, 1000,
    sims, SEQUENCE(n),
    revenues, NORM.INV(RANDARRAY(n), 4000000, 600000),
    costs, NORM.INV(RANDARRAY(n), 2200000, 300000),
    npvs, revenues - costs,
    PERCENTILE(npvs, {0.05, 0.25, 0.5, 0.75, 0.95})
)

ℹ️ Note: This simplified LAMBDA example shows a single-period model. For multi-period cash flows, you'd need nested LAMBDA functions or a helper column approach. The Data Table method remains more practical for complex multi-year models.

VBA Method (Any Excel Version)

For maximum control and performance, VBA lets you run 100,000+ iterations in seconds:

Sub RunMonteCarlo()
    Dim i As Long, n As Long
    n = 10000
    Application.ScreenUpdating = False
    
    For i = 1 To n
        Calculate  ' Forces recalculation of RAND() functions
        Cells(i + 1, 10).Value = Range("G7").Value  ' Record NPV
    Next i
    
    Application.ScreenUpdating = True
End Sub

For comprehensive VBA implementation guidance — including performance optimization, error handling, and when to choose VBA over native Excel approaches — see our VBA for financial modeling guide. When iteration counts scale beyond what VBA handles comfortably or when Python's statistical libraries become essential, our guide to Excel vs Python for financial analysis maps out exactly when to move the simulation outside the spreadsheet.

Real-World Application: Project Finance DSCR Simulation

Here's how a Monte Carlo simulation adds value in infrastructure project finance modeling. Suppose you're modeling a toll road with uncertain traffic volume and toll rates:

graph LR
    A[Traffic Volume<br/>Normal: 50K±8K] --> D[Revenue<br/>=Traffic × Toll]
    B[Toll Rate<br/>Uniform: $2.50-$3.50] --> D
    D --> E[Net Revenue<br/>After OpEx]
    C[Operating Costs<br/>Triangular: $8M-$12M-$15M] --> E
    E --> F[DSCR<br/>=Net Revenue / Debt Service]
    F --> G{DSCR Distribution}
    G --> H[P50 DSCR: 1.45x]
    G --> I[P90 DSCR: 1.12x]
    G --> J[P(DSCR < 1.0x): 4.2%]

The P90 DSCR of 1.12x and the 4.2% probability of DSCR falling below 1.0x are exactly the metrics lenders use to assess credit risk. This analysis would be impossible with a single deterministic model.

Frequently Asked Questions

Can you run a Monte Carlo simulation in Excel without add-ins?

Yes. Excel's built-in RAND(), NORM.INV(), and Data Table features provide everything needed for Monte Carlo simulation. You don't need @RISK, Crystal Ball, or any third-party add-in. The Data Table method handles up to 5,000 iterations efficiently, and VBA extends that to 100,000+ for complex models.

How many iterations should a Monte Carlo simulation have?

For most financial analyses, 1,000 to 5,000 iterations produce stable results. The mean typically converges within 1,000 runs, but tail risk metrics (5th/95th percentiles, VaR) need 5,000–10,000 iterations for precision. Run a convergence test: if doubling iterations changes your mean by less than 1%, you have enough.

What is the difference between Monte Carlo simulation and sensitivity analysis?

Sensitivity analysis in Excel changes one variable at a time while holding others constant (e.g., "what if revenue drops 10%?"). Monte Carlo varies all uncertain inputs simultaneously using probability distributions, capturing interaction effects between variables. Monte Carlo gives you a probability distribution of outcomes; sensitivity gives you a point-to-point comparison. In fixed income applications, bond duration in Excel provides the closed-form linear approximation for parallel rate shifts — a useful starting point for calibrating your Monte Carlo rate inputs before simulating the full distributional range of portfolio outcomes.

How do you add correlation to a Monte Carlo simulation in Excel?

Use the Cholesky decomposition method. Generate independent standard normal random variables with NORM.S.INV(RAND()), then multiply by a lower-triangular Cholesky matrix derived from your correlation matrix. This transforms independent draws into correlated ones while preserving each variable's marginal distribution.

Is Monte Carlo simulation accurate for financial forecasting?

Monte Carlo is as accurate as your input assumptions. The technique itself is mathematically rigorous — with enough iterations, the simulated distribution converges to the true distribution. The key is calibrating your probability distributions to real data: historical volatility, industry benchmarks, and expert judgment. Garbage in, garbage out still applies.

From Point Estimates to Probability Thinking

The shift from deterministic models to probabilistic ones isn't just a technical upgrade — it's a mindset change. When a colleague asks "what's the NPV of this project?" and you answer with "there's a 72% probability of positive NPV, with an expected value of $2.8M and a 5th-percentile downside of -$1.4M," you're providing genuinely useful decision-support information.

Tools like VeloraAI can accelerate this workflow by generating the random input formulas and Data Table structure from a natural language description of your model's uncertain variables — turning a 30-minute setup into a 2-minute prompt.

Start with a simple model. Replace two or three fixed assumptions with NORM.INV(RAND(), mean, stdev). Run a 1,000-iteration Data Table. Look at the histogram. Once you see the distribution of outcomes that was hiding behind your single-point estimate, you'll never go back to deterministic modeling.