The Monte Carlo Simulation in Excel

What are some concepts you need to be familiar with?

• Volatility
• Variance
• Probability distributions
• How Monte Carlo works

How do we calculate changes in stock prices?

Changes in stock prices will usually be shown by the formula in the graphic below: tomorrow’s price will be today’s price multiplied by some continuously compounded rate of return. S₁ = S₀ x e^r S₁: Price tomorrow S₀: Price now e^r: Continuously compounded return for the period  

The rate (r) is made up of which two components?

• Fixed Component
• Random Component

What is the fixed component?

This is some rate by which the price drifts away from the current level over a given period. That is in turn made up of a constant drift rate and a stochastic element. The drift rate will usually be a risk-free rate for the period, or the average or an assumed return for that period. However, as trading results in fluctuating returns, the drift rate is subject to a drag that reduces it. Hence, the drift is the chosen drift rate minus the dragging element which is half the variance for that period. The ‘half the variance’ is the stochastic element which we accept as being correct.

What is the random component?

It picks a return randomly based on a probability distribution with the drift as the mean and an associated standard deviation or volatility. Often a normal or lognormal distribution is used for the probability distribution, because mathematically it keeps things relatively simple, but any appropriate distribution could be used.

Which formula can we input in excel for our simulations?

Exp(norm.inv(rand(),mean,stdev)

What is the benefit of using the Monte Carlo Simulation?

• Particularly useful in the pricing of price dependant options, for example barriers options such as knock-ins and knock-outs
• Flexible - it allows us to assume non-standard distributions. We could, for example, assume that the drift rate and/or the volatility are not constant and randomise or adjust those variables accordingly