What is Monte Carlo?
Monte Carlo simulation runs the same model many times using random draws for uncertain inputs.
Instead of one number, you get a distribution (histogram, mean, percentiles).
Why are we doing this?
This is a sensitivity/risk tool. In real finance work, you can simulate uncertainty in many factors:
yield (interest rates), beta, tax rate, credit spread,
years to maturity, growth, margins, etc.
Important: Build the simulation in Excel (show formulas). Do not use a website calculator to produce final results.
Why this helps: It gives you a visual idea of how Monte Carlo repeats the same model thousands of times,
which is exactly what your Excel homework is doing when each row represents one simulation trial.
Class video
Click below to watch the class walkthrough video directly on this page.
Using the Telecom. Services industry inputs below, run a Monte Carlo simulation in Excel where the
market borrowing yield changes randomly across trials. In this version, keep the
cost of equity fixed at the industry level so you can clearly see how interest rates affect
debt cost and WACC.
When yield increases, what happens to WACC? Why?
Report WACC mean, P5, P50, and P95, and include a histogram.
Why this industry?
Telecom. Services has much higher debt weight than Semiconductor, so interest-rate changes affect the
debt side of WACC much more clearly.
3) Yield simulation assumption
Simulated input:
Annual yield (YTM)
Distribution used in Excel:
=MAX(0, NORM.INV(RAND(), YieldMean, YieldSD))
Center (mean): 5.29%
Spread (SD): 0.75%
Floor: 0%
Key idea: In this homework, “interest rate changes” = “yield changes.” We simulate yield (YTM), then use that simulated yield as the before-tax cost of debt in the WACC formula.
What is RAND()? What is NORM.INV()? ▾
RAND() gives Excel a random number between 0 and 1 each time the sheet recalculates.
Think of it as generating a random percentile such as 0.12, 0.48, or 0.93.
NORM.INV(probability, mean, standard_dev) takes that percentile and converts it into an actual value from a
normal distribution with your chosen mean and standard deviation.
Excel part
What it means in simple language
RAND()
Pick a random percentile between 0 and 1
NORM.INV(RAND(), YieldMean, YieldSD)
Turn that percentile into one random yield from a bell-shaped distribution
MAX(0, ...)
Do not allow the simulated yield to go below 0%
Example idea:
If RAND() = 0.50, NORM.INV(0.50, 5.29%, 0.75%) gives a yield near the center of the distribution.
If RAND() = 0.95, it gives a relatively high yield.
If RAND() = 0.05, it gives a relatively low yield.
Why this works: The formula NORM.INV(RAND(), YieldMean, YieldSD) is Excel's way of saying:
“Draw one random yield from a normal distribution centered at 5.29% with spread 0.75%.”
How to use the visualizer:
Enter μ = 0.0529 and σ = 0.0075 if you want to work in decimals, or use μ = 5.29 and σ = 0.75 if you prefer percent-style numbers consistently.
The tool lets you enter the mean and standard deviation, then explore probabilities and percentiles on the normal curve.
That is the same bell-curve logic behind NORM.INV().
4) Excel build (crystal-clear step-by-step)
Step A — Create an Inputs box (top of your Excel sheet) ▾
Put these in cells near the top (you choose the exact cells). You will reference them in your formulas.
Input
Value
Notes
Industry
Telecom. Services
Chosen because debt weight is much higher
Beta
0.63
Given (Damodaran)
Tax rate (T)
3.40%
Given
Wd = D/(D+E)
49.0%
Given
We = 1 − Wd
formula
=1 − Wd
Fixed Ke (cost of equity)
6.75%
Use Damodaran industry cost of equity as a fixed input
Yield mean (annual)
5.29%
Use current industry borrowing cost as the center
Yield SD (annual)
0.75%
Assumption; you may justify a different SD
You may choose a different yield standard deviation if you justify it, but keep assumptions reasonable.
Step B — Build the Monte Carlo table (this is the simulation) ▾
How many trials? Each row is one trial. Do at least 1,000 rows (minimum). Recommended: 5,000.
This is what makes it Monte Carlo.
Start your simulation table on a new row (for example row 15). Copy formulas down for 1,000–5,000+ rows.
Col
Variable
Excel formula (example)
Meaning
A
Trial #
=ROW()-14
1, 2, 3, …
B
Yield (YTM) — random draw
=MAX(0, NORM.INV(RAND(), YieldMean, YieldSD))
Random interest-rate scenario
C
Ke (fixed)
=Ke_Fixed
Hold cost of equity fixed so the debt-channel effect is clearer
D
Kd (before-tax)
=Yield
Use simulated market borrowing yield as debt cost
E
Kd after-tax
=Kd*(1-T)
Tax shield on debt
F
WACC
=Wd*Kd_after_tax + We*Ke
WACC for that trial
Optional (freeze one run): After you finish, copy your results columns and use Paste Special → Values
so your numbers do not change each time Excel recalculates.
Step B2 — Use a Data Table to run many simulations automatically (Excel trick) ▾
Idea: Build the model for one trial first. Then use a Data Table so Excel recalculates that same model thousands of times and stores one result in each row.
This is a very common classroom way to do Monte Carlo in Excel because RAND() changes every time Excel recalculates. In other words, Excel keeps drawing a new random percentile, and NORM.INV() converts it into a new simulated yield.
Column
What to put there
Example
D
Trial numbers
1, 2, 3, ..., 5000 or 10000
E
Top cell points to WACC result cell
=B4
Example layout:
D1 = Trial
E1 = WACC
D2 = 1
D3 = 2
D4 = 3
... copy down to 5000 or 10000
E2 = =B4 (or wherever your one-trial WACC result is)
Build your model for one random trial first.
Create a column of trial numbers such as 1 to 5000.
In the top row next to that column, link to your output cell for WACC.
Select the whole rectangle of the simulation table.
Go to Data → What-If Analysis → Data Table.
Leave Row input cell blank.
For Column input cell, point to any unused blank cell (for example, $Z$1).
Click OK. Excel will automatically generate one recalculated result for each row.
Important: The Column input cell does not need to be an important model input. It can be any unused blank cell. Its job is simply to force Excel to recalculate the RAND-based model again and again.
Tip: After your table is generated, copy the results and use Paste Special → Values if you want to freeze one final run before making charts and summary statistics.
Step C — Create the histogram (Excel instructions) ▾
Goal: Create a histogram of your simulated WACC column.
Method 1 (recommended): Insert → Histogram
Select your WACC results range (for example, F15:F5014).
Go to Insert → Insert Statistic Chart → Histogram.
Right-click the horizontal axis → Format Axis → set Number of bins (for example, 20–30).
Input range = your WACC range; check Chart Output.
Step D — Summary statistics you must report ▾
Compute these from your WACC range. Example ranges below assume WACC is in F15:F5014.
What to compute
Excel formula example
Meaning
WACC mean
=AVERAGE(F15:F5014)
Average WACC across all trials
WACC P5
=PERCENTILE.INC(F15:F5014,0.05)
5th percentile of WACC
WACC P50
=PERCENTILE.INC(F15:F5014,0.50)
Median WACC
WACC P95
=PERCENTILE.INC(F15:F5014,0.95)
95th percentile of WACC
How to read percentile: P5 means about 5% of outcomes are at or below that value. P50 is the median. P95 means about 95% of outcomes are at or below that value.
Optional extra Excel skill — COUNTIF (not required for the homework): COUNTIF lets you count how many simulation outcomes meet a rule. This is useful when you want to estimate probabilities from your Monte Carlo results.
Question
Example COUNTIF formula
Interpretation
How many WACC outcomes are above 6%?
=COUNTIF(F15:F5014,">6%")
Counts trials where WACC is greater than 6%
How many WACC outcomes are below 5%?
=COUNTIF(F15:F5014,"<5%")
Counts trials where WACC is less than 5%
How many yields are at least 7%?
=COUNTIF(B15:B5014,">=7%")
Counts high-yield scenarios
Turn COUNTIF into an estimated probability:
Probability(WACC > 6%)
=COUNTIF(F15:F5014,">6%")/COUNT(F15:F5014)
Probability(WACC < 5%)
=COUNTIF(F15:F5014,"<5%")/COUNT(F15:F5014)
Interpretation example: If COUNTIF(F15:F5014,">6%")/COUNT(F15:F5014) equals 0.18, then about 18% of your simulated trials produced a WACC above 6%.
5) What to submit (one per team)
Teamwork: Teams of 2–3. Submit one Excel file + one PDF memo per team (one upload per team).
Excel file with:
Inputs box
Monte Carlo table (≥ 1,000 trials; recommended 5,000)
Histogram chart
Summary stats
One-page memo (PDF, max 1 page) including:
Your assumptions (especially yield mean and yield SD)
File names: TeamName_MonteCarlo_WACC.xlsx and TeamName_MonteCarlo_WACC.pdf
6) Optional AI prompts (to generate full results)
Reminder: These prompts ask AI to generate the simulation outcome itself. If you use AI, you still need to understand the logic and check the results.
Prompt A — Ask AI to generate the full result in HTML / JavaScript ▾
Create a full single-file HTML page with embedded CSS and JavaScript that actually performs a Monte Carlo simulation and shows the final results.
Topic: Monte Carlo in Excel — how yield changes WACC for Telecom. Services.
Use these assumptions:
- Industry: Telecom. Services
- Wd = 49.0%
- We = 51.0%
- Fixed Ke = 6.75%
- Tax rate = 3.40%
- Yield mean = 5.29%
- Yield SD = 0.75%
- Number of trials = 5,000
What the page must do:
1. Randomly simulate 5,000 yield values from a normal distribution.
2. Compute before-tax Kd = yield.
3. Compute after-tax Kd = yield × (1 − tax rate).
4. Compute WACC = Wd × Kd_after_tax + We × Ke.
5. Display a data table showing at least the first 20 trials.
6. Report WACC mean, P5, P50, and P95.
7. Create a histogram of simulated WACC.
8. Add a short written conclusion explaining:
- when yield rises, WACC rises
- why the higher after-tax cost of debt pushes WACC up
9. Add a section called “How to Read the Histogram” that explains in simple student-friendly language:
- the x-axis shows simulated WACC values
- the y-axis shows how often those WACC values occur
- taller bars mean more common outcomes
- bars on the left are lower WACC outcomes
- bars on the right are higher WACC outcomes
- if the histogram is narrow, WACC outcomes are more stable
- if the histogram is wide, WACC outcomes are more spread out and uncertain
- where the mean, P5, P50, and P95 fit into interpretation
Design requirements:
- clean classroom style
- cards, rounded corners, clear spacing
- Light / Warm / Dark theme buttons
- Print button
Important:
- Put all CSS and JavaScript in the same HTML file.
- Actually calculate the results inside JavaScript.
- Output the full HTML code only.
Prompt B — Ask AI to generate the full result in Python ▾
Write a complete Python script that performs the full Monte Carlo simulation and prints the final homework results.
Topic: How yield changes WACC for Telecom. Services.
Use these assumptions:
- Wd = 49.0%
- We = 51.0%
- Fixed Ke = 6.75%
- Tax rate = 3.40%
- Yield mean = 5.29%
- Yield SD = 0.75%
- Number of trials = 5,000
What the script must do:
1. Simulate 5,000 yield values from a normal distribution.
2. Compute before-tax Kd = yield.
3. Compute after-tax Kd = yield × (1 − tax rate).
4. Compute WACC = Wd × Kd_after_tax + We × Ke.
5. Create a pandas DataFrame with the simulation results.
6. Print the first 20 rows of the data table.
7. Report WACC mean, P5, P50, and P95.
8. Create a histogram of simulated WACC using matplotlib.
9. Print a short conclusion explaining:
- when yield rises, WACC rises
- why the higher after-tax cost of debt pushes WACC up
10. Print a short section called “How to Read the Histogram” that explains in simple student-friendly language:
- the x-axis shows simulated WACC values
- the y-axis shows how often those WACC values occur
- taller bars mean more common outcomes
- bars on the left are lower WACC outcomes
- bars on the right are higher WACC outcomes
- if the histogram is narrow, WACC outcomes are more stable
- if the histogram is wide, WACC outcomes are more spread out and uncertain
- where the mean, P5, P50, and P95 fit into interpretation
Requirements:
- Use numpy, pandas, and matplotlib.
- Add clear comments.
- Make the code beginner-friendly.
- Output the full Python code only.