In finance, option pricing is a term used for estimating the value of an option contract using all known inputs. Monte Carlo Simulation is a popular algorithm that can generate a series of random variables with similar properties to simulate realistic inputs. In this guide, we're going to show you how to calculate Option Pricing using Monte Carlo Simulations in Excel.

Download Workbook

Monte Carlo Simulation

Monte Carlo simulation is a special type of probability simulation which is mainly used to determine the risk factors by observing the cluster of possible results. First developed for finding the possible outcomes of a solitaire game, Monte Carlo takes its name from the famous casino in Monaco.

The simulation takes random values of the inputs within constraints and the results are recorded as more iterations are run. Then, you get a rather big pool of answers created from all those random inputs.

We can simulate the possible future stock prices and then use them to find the discounted expected option payoffs.

Using the statistical formulas NORM.S.INV and VBA, we can generate random variables in normal distribution and run the simulation as many times as necessary..

European-style Options Pricing

In this example, we are going to be using the Black-Scholes formula to calculate a European-style option pricing model, which restricts its options execution until the expiration date. There are two major types of options: calls and puts.

  • Call is an option contract between the buyer and the seller of the call option, to exchange a security at a set price.
  • Put is an option contract which gives the purchaser of the put option the right to sell an asset, at a specified price, by a specified date to the seller of the put.

The Black-Scholes formula is a popular approach for calculating European put and call options. In its simplest form, the Black-Scholes model involves underlying assets of a risk-free rate of return and a risky share price. The following equation shows how a stock price varies over time:

How to calculate Option Pricing using Monte Carlo Simulations in Excel - Black-Scholes

St = Stock price at time t

r = Risk-free rate

t = time

σ = T he volatility of the stock's returns; this is the square root of the quadratic variation of the stock's log price process

ε = random generated variable from a normal distribution

δ = Dividend yield which was not in the Black-Scholes model originally. The original model was for pricing options on non-paying dividends stocks.

Once the formula is run thousands or million times, you will have the set of St values. The payoff values can be calculated with the following formula, where K is the strike price:

Calculating Option Pricing with VBA

Let’s pass these formulations into a VBA code. We are going to create a user defined function (UDF) which can be used as a built-in function like SUM or VLOOKUP. Our function name is “EuropeanOptionMonteCarlo”.

Public Function EuropeanOptionMonteCarlo(OptionType As String, S As Double, K As Double, r As Double, sigma As Double, T As Double, Div As Double, N As Double, nIt As Double)

  Dim i As Integer, j As Integer
  Dim Payoff() As Double, St As Double, dt As Double, e As Double, price As Double

  ReDim Payoff(1 To nIt)

  dt = T / N

  Randomize 'new seed for random number generation

  For i = 1 To nIt
    St = S
    For j = 1 To N
      e = WorksheetFunction.NormSInv(Rnd())   'random factor
      St = St * Exp((r - Div - sigma ^ 2 / 2) * dt + sigma * Sqr(dt) * e) 'European option formula
    Next j

    If OptionType = "Call" Then 'Call or Put
      Payoff(i) = WorksheetFunction.Max(St - K, 0) * Exp(-r * T)
    ElseIf OptionType = "Put" Then
      Payoff(i) = WorksheetFunction.Max(K - St, 0) * Exp(-r * T)
    End If

  Next i

  For i = 1 To nIt
    price = price + Payoff(i)   'Total of iterations
  Next i

  EuropeanOptionMonteCarlo = price / nIt  'Return average of iterations as the function's result

End Function
[/vb]

Once the UDF is ready, we are ready to see the result in Excel.
How to calculate Option Pricing using Monte Carlo Simulations in Excel