Running Monte Carlo Simulations in Financial Models

# Monte Carlo DCF Valuation in Excel

## 1. Model Setup

**Inputs (Sheet1, cells B2:B7):**
- B2: Base Revenue (Year 0), e.g., `10000000`
- B3: Revenue Growth Mean = `0.25`
- B4: Revenue Growth StDev = `0.10`
- B5: EBITDA Margin Mean = `0.20`
- B6: EBITDA Margin StDev = `0.05`
- B7: Terminal Growth = `0.03`

**Discount Rate:** Uniform distribution between 10%–15% via `=0.10+RAND()*0.05`

**Revenue Growth & Margin:** Normal distributions via `NORM.INV(RAND(), mean, stdev)`

**Assumption:** Free Cash Flow ≈ EBITDA (simplified; ignore taxes/capex for clarity or subtract a fixed %).

## 2. Simulation Build (single-iteration row)

Build one trial in row 10 (columns D–N):

- **D10 (Discount Rate):** `=0.10+RAND()*0.05`
- **E10 (Margin draw):** `=NORM.INV(RAND(),$B$5,$B$6)`
- **F10:J10 (Growth Y1–Y5):** `=NORM.INV(RAND(),$B$3,$B$4)` (copy across)
- **K10 (Revenue Y1):** `=$B$2*(1+F10)`
- **L10 (Revenue Y2):** `=K10*(1+G10)` → drag through **O10 (Revenue Y5)**
- **P10 (Terminal Value):** `=O10*$E$10*(1+$B$7)/(D10-$B$7)`
- **Q10 (Enterprise Value):**
`=K10*$E$10/(1+D10)^1+L10*$E$10/(1+D10)^2+M10*$E$10/(1+D10)^3+N10*$E$10/(1+D10)^4+O10*$E$10/(1+D10)^5+P10/(1+D10)^5`

Q10 is your simulated Enterprise Value for one trial.

## 3. Running Iterations (1000+ row approach)

Simplest method: copy row 10 down to row 1009.

- Select D10:Q10, then fill down to row 1009. Each row recalculates with fresh `RAND()` draws.
- Press **F9** to regenerate all 1,000 scenarios.
- To freeze results: copy Q10:Q1009 → Paste Special → Values into column S.

*(Alternative: Data Table method — put `=Q10` in T1, create a 1-column data table T2:T1001 with any empty cell as the column input. Excel reruns the model for each row.)*

## 4. Analyzing Results

Using **Q10:Q1009** as your EV distribution:

**Summary statistics (cells T2:T8):**
- Mean: `=AVERAGE(Q10:Q1009)`
- Median: `=MEDIAN(Q10:Q1009)`
- StDev: `=STDEV.S(Q10:Q1009)`
- Min: `=MIN(Q10:Q1009)`
- Max: `=MAX(Q10:Q1009)`

**Percentiles (T10:T14):**
- 5th (VaR95): `=PERCENTILE.INC(Q10:Q1009,0.05)`
- 25th: `=PERCENTILE.INC(Q10:Q1009,0.25)`
- 50th: `=PERCENTILE.INC(Q10:Q1009,0.50)`
- 75th: `=PERCENTILE.INC(Q10:Q1009,0.75)`
- 95th: `=PERCENTILE.INC(Q10:Q1009,0.95)`

**Probability EV > $50M:**
`=COUNTIF(Q10:Q1009,">50000000")/COUNT(Q10:Q1009)`

**Value at Risk (downside, 5%):**
`=AVERAGE(Q10:Q1009)-PERCENTILE.INC(Q10:Q1009,0.05)`

**Histogram:**
1. Create bin edges in column V (e.g., V2:V21 = $0, $10M, $20M … $200M).
2. In W2:W21 use: `=FREQUENCY(Q10:Q1009,V2:V21)` (array-enter with Ctrl+Shift+Enter, or in Excel 365 just Enter).
3. Select V2:W21 → Insert → Column Chart. This shows your EV distribution.

**Interpretation tips:**
- The spread (P95 – P5) quantifies valuation uncertainty.
- If P(EV > $50M) is, say, 68%, there's a 32% downside risk to that threshold.
- Re-run (F9) to confirm stability; if results vary materially, increase to 5,000–10,000 trials.

# Monte Carlo Simulation: Startup DCF Valuation

## 1. Model Setup

**Inputs & Assumptions:**
- Base Year 1 Revenue: $5M (cell B1)
- Revenue Growth: Normal distribution, mean 25%, stdev 10%
- EBITDA Margin: Normal distribution, mean 20%, stdev 5%
- Discount Rate: Uniform distribution, 10–15%
- Terminal Growth Rate: 3% (constant, cell B2)
- Tax Rate: 25% (constant, cell B3)

**Distributions:**
- Revenue Growth: `=NORM.INV(RAND(),0.25,0.10)`
- EBITDA Margin: `=NORM.INV(RAND(),0.20,0.05)`
- Discount Rate: `=RAND()*0.05+0.10`

---

## 2. Simulation Build

**Layout: Each row = one Monte Carlo iteration. Columns A–H = years 1–5 + DCF metrics.**

**Row 1 (Headers):**
- A1: `Iteration` | B1: `Year1_Rev` | C1: `Y2_Rev` | D1: `Y3_Rev` | E1: `Y4_Rev` | F1: `Y5_Rev` | G1: `Disc_Rate` | H1: `Enterprise_Value`

**Iteration Column (A2:A1001):**
- A2: `=ROW()-1` (copy down to A1001 for 1000 iterations)

**Discount Rate (G2, copy to G1001):**
- G2: `=RAND()*0.05+0.10`

**Revenue Build (B2:F2, copy down to row 1001):**
- B2: `=$B$1*(1+NORM.INV(RAND(),0.25,0.10))` (Year 1 revenue with growth)
- C2: `=B2*(1+NORM.INV(RAND(),0.25,0.10))` (Year 2, copy to D2:F2)

**EBITDA & Free Cash Flow (I2:M2, copy down):**
- I2 (Y1 EBITDA): `=B2*NORM.INV(RAND(),0.20,0.05)`
- J2 (Y2 EBITDA): `=C2*NORM.INV(RAND(),0.20,0.05)` (copy to K2:M2 for years 3–5)
- N2 (Y1 FCF): `=I2*(1-$B$3)` (after-tax EBITDA, copy to O2:R2)

**Terminal Value (S2, copy down):**
- S2: `=R2*(1+$B$2)/(G2-$B$2)` (Gordon Growth Model)

**PV of Cash Flows (T2:Y2, copy down):**
- T2: `=N2/(1+G2)^1` (PV Year 1 FCF)
- U2: `=O2/(1+G2)^2` (copy to V2:X2 for years 2–5)
- Y2: `=S2/(1+G2)^5` (PV of terminal value)

**Enterprise Value (H2, copy to H1001):**
- H2: `=SUM(T2:Y2)`

---

## 3. Running Iterations

**No Data Table needed.** The formulas above use `RAND()` and recalculate across 1000 rows automatically. Press **Ctrl+Shift+F9** to force recalculation and generate new random draws.

To preserve results, copy H2:H1001 and paste as values in a new column before recalculating.

---

## 4. Analyzing Results

**Summary Statistics (cells in column Z):**
- Z1: `Mean EV` → `=AVERAGE(H2:H1001)`
- Z2: `Median EV` → `=MEDIAN(H2:H1001)`
- Z3: `10th %ile` → `=PERCENTILE.INC(H2:H1001,0.10)`
- Z4: `90th %ile` → `=PERCENTILE.INC(H2:H1001,0.90)`
- Z5: `Probability > $50M` → `=COUNTIF(H2:H1001,">50")/1000`

**VaR (5th percentile, worst-case downside):**
- Z6: `=PERCENTILE.INC(H2:H1001,0.05)`

**Histogram:**
Create bins ($20M–$150M in 10M increments) and use `COUNTIFS()` to count results in each bin. Insert a column chart to visualize the distribution.

---

**Key Insight:** The probability cell (Z5) directly answers: "What % of simulations yield >$50M valuation?" Rerun (Ctrl+Shift+F9) multiple times; results should stabilize around the true probability.

Of course. Here is a step-by-step guide to building the Monte Carlo DCF simulation for your startup valuation in Excel.

### 1. Model Setup

First, we will set up the core inputs and assumptions for the model.

*   In cell **B2**, enter the Year 0 Revenue: `10000000`
*   In cell **B3**, enter the Mean Revenue Growth: `0.25`
*   In cell **B4**, enter the Stdev for Revenue Growth: `0.10`
*   In cell **B5**, enter the Mean EBITDA Margin: `0.20`
*   In cell **B6**, enter the Stdev for EBITDA Margin: `0.05`
*   In cell **B7**, enter the Minimum Discount Rate: `0.10`
*   In cell **B8**, enter the Maximum Discount Rate: `0.15`
*   In cell **B9**, enter the Terminal Growth Rate: `0.03`
*   In cell **B10**, enter the Tax Rate: `0.25`

The stochastic variables will be modeled using these distributions:
*   **Revenue Growth & EBITDA Margin:** Normal Distribution (`NORM.INV(RAND(), mean, stdev)`)
*   **Discount Rate:** Uniform Distribution (`min + (max - min) * RAND()`)

### 2. Simulation Build

This section builds a single-trial DCF model that recalculates with every change in the sheet (e.g., pressing F9).

1.  **Generate Random Variables for One Trial:**
    *   In cell **D2** (Discount Rate for this trial), enter: `=B7+(B8-B7)*RAND()`
    *   In cells **D5:H5** (Annual Random Revenue Growths), enter this formula in **D5** and drag it across to **H5**: `=NORM.INV(RAND(),$B$3,$B$4)`
    *   In cells **D6:H6** (Annual Random EBITDA Margins), enter this formula in **D6** and drag it across to **H6**: `=NORM.INV(RAND(),$B$5,$B$6)`

2.  **Build the 5-Year DCF Forecast (Columns D-H for Years 1-5):

### 1. Model Setup
Inputs (A1:B8):  
A1: Initial Revenue ($M), B1: `10`  
A2: Growth Mean, B2: `0.25`  
A3: Growth Stdev, B3: `0.1`  
A4: Margin Mean, B4: `0.2`  
A5: Margin Stdev, B5: `0.05`  
A6: r Min, B6: `0.1`  
A7: r Max, B7: `0.15`  
A8: Terminal Growth, B8: `0.03`  

Assumptions: Constant g ~ NORM.INV(RAND(),0.25,0.1), m ~ NORM.INV(RAND(),0.2,0.05), r ~ uniform B6:B7 via B6+(B7-B6)*RAND(). Rev_t = B1*(1+g)^t; EBITDA_t = Rev_t * m; EV = Σ_{t=1-5} EBITDA_t/(1+r)^t + [EBITDA_5*(1+0.03)/(r-0.03)]/(1+r)^5.

### 2. Simulation Build
A12: `Iterations`  
A13: `1`; select A13:A1012, fill series (1-1000).  
B13: `=NORM.INV(RAND(),$B$2,$B$3)` (growth)  
C13: `=NORM.INV(RAND(),$B$4,$B$5)` (margin)  
D13: `=$B$6+($B$7-$B$6)*RAND()` (r)  
E13: `=$B$1*$C13*SUMPRODUCT((1+B13)^ROW(INDIRECT("1:5")),(1/(1+D13))^ROW(INDIRECT("1:5")))+$B$1*(1+B13)^5*$C13*(1+$B$8)/(D13-$B$8)/(1+D13)^5` (EV)

### 3. Running Iterations
Select A13:E13, copy/paste to E1012 (1000 rows). Press F9 to rerun sims.

### 4. Analyzing Results
F1: `Mean` F2: `=AVERAGE(E13:E1012)`  
F3: `P10` F4: `=PERCENTILE.INC(E13:E1012,0.1)`  
F5: `P90` F6: `=PERCENTILE.INC(E13:E1012,0.9)`  
F7: `Prob >$50M` F8: `=COUNTIF(E13:E1012,">50")/1000`  
F9: `95% VaR (P5)` F10: `=PERCENTILE.INC(E13:E1012,0.05)`  
Histogram: Data > Data Analysis > Histogram (Input: E13:E1012; Bin: G13:G23 = 0,10,...,100). Chart distribution.