Monte Carlo Simulation for DCF Valuation

Institutional Definition¶

Monte Carlo simulation extends sensitivity and scenario analysis by modelling a full probability distribution of DCF outcomes, drawing each key input from a defined probability distribution — potentially correlated with other inputs — across a large number of iterations, rather than testing a single input at a time or a small number of discrete named cases. This guide addresses how input distributions are defined, how correlation is handled, the practical run-time approaches, and how the resulting output distribution is interpreted.

Why Monte Carlo Extends Sensitivity and Scenario Analysis¶

The Knowledge Centre's existing DCF stress-testing guides each answer a related but distinct question:

• Sensitivity analysis shows how the output changes as one input at a time moves across a defined range, holding all other inputs constant.
• Scenario analysis tests a small number of discrete, internally consistent cases — typically base, upside, and downside — each with a full, specifically chosen set of input values.
• Tornado analysis ranks each input by the magnitude of its individual impact on the output, built from a series of one-at-a-time sensitivity runs.

Monte Carlo simulation differs from all three in kind, not just degree: rather than choosing a small number of specific cases or ranges, it defines a continuous probability distribution for each key input, draws a random value from each distribution simultaneously, calculates the resulting DCF output, and repeats this process a large number of times — typically several thousand or more iterations. The result is not a handful of point estimates but a full distribution of possible outcomes, weighted by how likely each combination of inputs is judged to be. This makes Monte Carlo the appropriate tool specifically when the question being asked is probabilistic in nature — not "what happens in the downside case" but "what is the probability that value falls below a given threshold."

Defining Input Probability Distributions¶

Monte Carlo simulation requires specifying, for each input to be varied, a probability distribution rather than a single range or a discrete set of cases. This is a meaningfully larger modelling commitment than a scenario's simple high/low endpoints, because the distribution's shape and parameters must themselves be justified:

A triangular distribution (defined by a minimum, most-likely, and maximum value) is a common practical choice for DCF drivers because its parameters map directly onto the same low/base/high judgements a scenario analysis would otherwise use — it is often the easiest distribution to build institutional consensus around, since it does not require estimating a standard deviation the way a normal distribution does.

Correlation Between Inputs¶

Treating every input as an independent random draw is the simplest implementation, but it is frequently unrealistic. Several DCF drivers tend to move together:

• Growth rate and reinvestment/capital expenditure — higher growth typically requires proportionally more capital investment
• Growth rate and margin — a demand shock or macroeconomic scenario can depress both simultaneously, or a period of margin expansion can accompany deliberate growth deceleration
• Discount rate and terminal growth — both are sensitive to the same underlying macroeconomic environment (interest rates, inflation expectations)

Ignoring correlation when it is present produces an output distribution that includes combinations of inputs that are statistically implausible — for example, simultaneously drawing the highest plausible growth rate with the lowest plausible reinvestment rate, a combination that may never actually occur together. Where correlation is material, it should be built into the simulation explicitly (for example, using a specified correlation matrix or copula method, or, at a simpler level, defining certain inputs as a function of others rather than as fully independent draws) rather than left unaddressed. Where correlation is not explicitly modelled, that omission should be a disclosed, deliberate simplification rather than an unexamined default.

Running the Simulation¶

Three practical approaches are used in practice, in increasing order of capability:

Native Excel (Data Tables and formulas). A Data Table is built for one- or two-variable sensitivity analysis and is not designed to simulate thousands of iterations across many simultaneously varying, distribution-drawn inputs. A crude Monte Carlo can be approximated in native Excel using RAND()-based formulas feeding each input and a large helper table of iterations, but this approach is slow, difficult to audit, and rarely used for anything beyond a simple illustrative build.

Dedicated Monte Carlo add-ins. Purpose-built Excel add-ins allow input distributions to be defined directly on top of an existing DCF model's cells, run a specified number of iterations efficiently, and produce output distribution statistics and charts automatically. This is the most common approach in institutional practice where the underlying DCF is expected to remain a live, auditable Excel model.

Custom VBA or Python implementations. For the largest number of iterations, the most complex correlation structures, or integration into a broader risk or portfolio system, a custom-coded simulation (in VBA within Excel, or in Python operating on an exported version of the model's logic) offers the most flexibility, at the cost of the simulation logic living outside the native spreadsheet formulas an auditor would otherwise be able to trace directly.

Interpreting the Output Distribution¶

The raw output of a Monte Carlo DCF is a large set of calculated values — one per iteration. That raw output is typically summarized using:

Mean / Median value          — the central tendency of the output distribution
Standard deviation            — the dispersion of outcomes around the central estimate
Percentiles (e.g., P10, P50, P90) — the value below which a given percentage of iterations fell
Probability of value < threshold  — the proportion of iterations producing a value below a
                                     specified reference point (e.g., a proposed purchase price,
                                     a minimum required return, or a lender covenant threshold)

The probability-of-threshold statistic is the output form that most clearly distinguishes Monte Carlo from the other stress-testing methods on this Knowledge Centre: sensitivity, scenario, and tornado analysis can each show that value falls below a given level in a particular case, but none of them can, on their own, attach a probability to that outcome across the full range of plausible input combinations. This is also Monte Carlo's specific value to private equity and infrastructure/project finance audiences, where a probability-weighted view of downside risk (for example, the probability that an equity IRR falls below a minimum hurdle) is often a more directly decision-relevant output than a discrete downside case.

How This Complements the Existing Stress-Testing Guides¶

Monte Carlo is not a replacement for sensitivity, scenario, or tornado analysis — each remains useful for what it specifically does well. Tornado analysis remains the fastest way to identify which individual drivers matter most; scenario analysis remains the clearest way to communicate a small number of specific, named narratives to a decision-making audience; sensitivity analysis remains the simplest way to show a two-variable relationship directly. Monte Carlo is reserved for situations where the specific question is genuinely probabilistic — where a distribution, and a probability attached to a threshold, is more useful to the audience than a discrete set of cases.

Structural Audit Checks¶

Common Errors¶

Continue Reading¶

Prerequisites¶

• Discounted Cash Flow (DCF) Valuation — the parent pillar
• Sensitivity Analysis for DCF Valuation
• Scenario Analysis for DCF Valuation

Related Glossary¶

• Sensitivity Analysis
• Scenario Analysis
• Data Table

Related Technical Guides¶

• Sensitivity Analysis for DCF Valuation
• Scenario Analysis for DCF Valuation
• Tornado Analysis for DCF Valuation

Related Checklists¶

• DCF Model Audit Checklist
• Forecast Assumptions and Driver Checklist