Monte Carlo Option Pricing with Excel

Learn how to price options with the Monte Carlo method, and get a pricing spreadsheet for European, Asian, Barrier and Lookback options.

Several methods exist to price options. Binomial trees, for example, calculate the value of an asset over a series of time steps. At every step, the asset price can increase or decrease based on an up or down probability. Then, the option value is calculated sequentially at every point in the tree, from the final point to the first point.

Another approach is the Monte Carlo method, typically used for pricing path-dependent options.

In the first step, we generate many future stock prices. The following equation, for example, describes how a stock price varies over time given a Weiner process.

Monte Carlo Option Pricing Stock Price

  • ST is the stock price at expiry
  • St is the stock  price at the current time
  • T is the time to expiry
  • t is the current time
  • r is the risk free rate
  • σ is the volatility
  • x̄ is a number sampled from a normal distribution
Monte Carlo European Option
Typically, hundreds or even millions of values of x̄ are generated across many many time steps. This gives many future price paths, with ST calculated for each.

Once the entire set of ST is generated, the payoff is calculated. Given a European option, the expected present value of call (ct) and put (pt) options are calculated as follows, where X is the strike price.

Monte Carlo Call Price

Monte Carlo Put Price

The exponential term in the above two equations discounts the price at time t to expiry (at time T). For an Asian option, ST would be replaced with an average price over the whole path.

Then given an entire set of ct or pt, the mean option price is calculated. For example, for a call option, the mean price is

VBA for Monte-Carlo Pricing of European Options

This VBA function uses the principles described above to price a European option. The arguments are

  • c is “C” or “P” (call or put)
  • s is the spot price
  • x is the strike price
  • t is the time to maturity
  • z is the volatility
  • r is the risk free rate
  • q is the dividend yield
  • n is the number of time steps
  • nIter is the number of random iterations per time step
Option Base 1
Function EuropeanOptionMonteCarlo(c As String, s As Double, x As Double, t As Double, z As Double, r As Double, q As Double, n As Double, nIter As Double) As Variant
Dim dt, e, dlns, price, SimVar(), PayVar() As Double
ReDim SimVar(nIter, n + 1)
ReDim PayVar(nIter)
dt = t / n
a = 0
For i = 1 To nIter
    SimVar(i + a, 1) = s
    p = 0
    For j = 1 To n
        If (j - 1) / 250 - Int((j - 1) / 250) = 0 And j > 1 Then p = p + 1
        e = WorksheetFunction.NormSInv(Rnd())
        dlns = (r - q - z ^ 2 / 2) * dt + z * e * dt ^ 0.5
        If j - 250 * p = 1 And p > 0 Then
            SimVar(i + p + a, 2) = SimVar(i + p - 1 + a, 251) * Exp(dlns)
            SimVar(i + p + a, j - 250 * p + 1) = SimVar(i + p + a, j - 250 * p) * Exp(dlns)
        End If
    Next j
    If c = "C" Then
        PayVar(i) = WorksheetFunction.Max(SimVar(i + p + a, j - 250 * p) - x, 0) * Exp(-r * t)
    ElseIf c = "P" Then
        PayVar(i) = WorksheetFunction.Max(x - SimVar(i + p + a, j - 250 * p), 0) * Exp(-r * t)
    End If
    a = a + p
Next i
price = 0
temp = 0
For i = 1 To nIter
    price = price + PayVar(i)
Next i
price = price / nIter
EuropeanOptionMonteCarlo = price
End Function

The Excel spreadsheet at the bottom of this post implements the above VBA.

Monte Carlo Pricing of Standard and Exotic Options in Excel

A spreadsheet that prices Asian, Lookback, Barrier and European options with fully viewable and editable VBA can be purchased here.

Monte Carlo Option Pricing in Excel

The Lookback option has a floating strike, and you can choose an arithmetic or geometric average for the Asian option.

Download Excel Spreadsheet to Price European Options with Monte Carlo