Building a Monte Carlo Option Pricer in Excel

The Monte Carlo pricing method is a flexible and powerful technique. Within a basic Monte Carlo pricing framework a simulation is set up that produces random realized option payoffs. The simulation is then run many times and the resultant payoffs are averaged to obtain option valuations.

Task A: Set Up the Simulation

For each currency pair within the simulation the following market data is required:

• Spot (S): the current exchange rate in a given currency pair
• Interest rates (rCCY1 and rCCY2): continuously compounded risk-free interest rates in CCY1 and CCY2 of the currency pair
• Volatility (σ): the volatility of the spot log returns

We start in Black-Scholes world so only a single volatility (no term structure or smile) and single interest rates (no term structure) are specified at this stage.

The simulation contains multiple time steps so the time (measured in years) between steps must be defined. For daily time steps, weekends can be removed and it is usually assumed that there are 252 trading days in the year; hence the time step is . As a sense check, at the 252nd step, time should be exactly 1:

The following formula is used for calculating the spot evolution between time steps within the simulation:

where is generated in Excel using =NORMSINV(RAND()).

The spot path generated on the sheet represents one sample of the simulation. Pressing F9 in Excel recalculates the sheet and generates a new sample.

As a check, the realized volatility of the spot path can be calculated. If the simulation is set up properly, the realized volatility should be approximately equal to the volatility input:

It is also useful to plot spot against time to judge whether the generated path looks realistic:

Try flexing market data inputs to see how the spot paths are impacted; test low volatility, high volatility, low interest rate differential, high positive and negative interest rate differential, and different initial spot values.

Task B: Set Up a Vanilla Option Payoff and the Monte Carlo Loop

A vanilla option payoff at maturity can now be calculated. First, set up the payoff details and copy the spot and time at maturity from the appropriate simulation step into the payoff calculation area. Recall that vanilla option payoffs are max(S_T − K, 0) for a CCY1 call and max(K − S_T, 0) for a CCY1 put.

In addition, the payoff is at maturity and it therefore needs to be present valued back to the horizon to calculate the option value. Payoff P&L is naturally in CCY2 so this is present valued using the CCY2 discount factor . In addition, the CCY2 pips option value at the horizon can be converted into CCY1 terms by dividing by the inception spot.

When the sheet recalculates, the vanilla option payoff should update:

A reasonable question at this point would be: “If the vanilla payoff only depends on spot at maturity, why are daily time steps required within the Monte Carlo?” Good question, and for a single vanilla payoff, daily time steps are not required; but as the framework is extended to price multiple options with different expiry dates, or for options with path dependence, more frequent time steps will be required and it is easier to set up the simulation properly from the start.

The number of runs (i.e., the number of times the simulation is rerun) now needs to be added as an input. Run count, timing, and the option value should be added as outputs:

The Monte Carlo is controlled by a VBA subroutine that does the following:

1. Loads up relevant config.
2. Loops around, recalculating the simulation and collecting the relevant results.
3. Calculates outputs and pushes them back onto the sheet.

The VBA code can be written in fewer than 20 lines:

Option Explicit
Option Base 0 'Ensures arrays start at 0
Sub RunMonteCarlo1()
    Dim MCRuns As Long, Count As Long
    Dim TimeStart As Date, TimeEnd As Date
    Dim VanillaPayoffs() As Double
    'Load Settings
    MCRuns = Range("NumberOfRuns")
    ReDim VanillaPayoffs(MCRuns - 1) As Double
    Range("MCTimer") = "Running"
    'Loop
    TimeStart = Now
    Count = 0
    While (Count < MCRuns)
        ActiveSheet.Calculate
        '(Recalculates the sheet: equivalent to pressing F9)
        Range("Count") = Count + 1
        VanillaPayoffs(Count) = Range("VanillaPayoff")
        Count = Count + 1
    Wend
    TimeEnd = Now
    'Outputs
    Range("MCVanillaPrice") = Application.WorksheetFunction _
     .Average(VanillaPayoffs)
    Range("MCTimer") = (TimeEnd - TimeStart) * 24 * 60 * 60
    '(Conversion of Time from Days into Seconds)
End Sub

Before starting the Monte Carlo, ensure that this sheet is the only one loaded into the Excel, so the “Calculate” command does not also cause other sheets to recalculate.

If everything is set up correctly, the subroutine runs the Monte Carlo and on the sheet the run count increments up to the number of runs. When the Monte Carlo is finished, the timing and option price are output:

For a vanilla option, the Monte Carlo Black-Scholes price can be compared to a closed-form Black-Scholes price. However, the closed-form price should be calculated in the VBA subroutine and pushed onto the sheet rather than being calculated in the sheet because that would potentially slow down the Monte Carlo. Using the OptionPrice function from Practical C enables this with one additional line of code (don't put it within the loop):

    'Calculate Closed-form Option Price
    Range("CFVanillaPrice") = OptionPrice(Range("PayoffDirection") = 1, _
     Range("S_Initial"), Range("Strike"), Range("T_Maturity"), _
     Range("rCCY1"), Range("rCCY2"), Range("vol")) / Range("S_Initial")

Due to limitations of the Application.WorksheetFunction.Average function, in earlier versions of Excel a running total variable must be used instead of an array if the number of runs is over 65,000. The advantage of using an array (if possible) is that information about the distribution of payoffs can be calculated.

As the number of runs increases, the Monte Carlo outputs get more accurate but the calculation takes longer. Changing the number of runs within an example Monte Carlo generates the following results:

Only relatively simple derivative payoffs have closed-form solutions but a key feature of Monte Carlo pricing is that any payoff can be priced.

Task C: Set Up Multiple Payoffs

With only relatively minor tweaks, the Monte Carlo simulation can be expanded to output four option prices at once:

1. European vanilla call
2. European vanilla put
3. European digital call
4. European digital put

To generate European digital prices, the payoff formula must be adjusted to, for example, =IF(FinalSpot > Strike, 1, 0). Plus note that the digital payout must be present valued back to the horizon in the same currency as the payout.

The VBA code also needs to be extended to pick up the four payoffs:

Sub RunMonteCarloMultiPayoffs()
    Dim MCRuns As Long, Count As Long
    Dim TimeStart As Date, TimeEnd As Date
    Dim VanillaCallPayoffs() As Double
    Dim VanillaPutPayoffs() As Double
    Dim DigitalCallPayoffs() As Double
    Dim DigitalPutPayoffs() As Double
    'Load Settings
    MCRuns = Range("NumberOfRuns")
    ReDim VanillaCallPayoffs(MCRuns - 1) As Double
    ReDim VanillaPutPayoffs(MCRuns - 1) As Double
    ReDim DigitalCallPayoffs(MCRuns - 1) As Double
    ReDim DigitalPutPayoffs(MCRuns - 1) As Double
    Range("MCTimer") = "Running"
    'Loop
    TimeStart = Now
    Count = 0
    While (Count < MCRuns)
        Calculate
        Range("Count") = Count + 1
        VanillaCallPayoffs(Count) = Range("VanillaCallPayoff")
        VanillaPutPayoffs(Count) = Range("VanillaPutPayoff")
        DigitalCallPayoffs(Count) = Range("DigitalCallPayoff")
        DigitalPutPayoffs(Count) = Range("DigitalPutPayoff")
        Count = Count + 1
    Wend
    TimeEnd = Now
    'Outputs
    Range("MCVanillaPriceCall") =_
     Application.WorksheetFunction.Average(VanillaCallPayoffs)
    Range("MCVanillaPricePut") = _
     Application.WorksheetFunction.Average(VanillaPutPayoffs)
    Range("MCDigitalPriceCall") = _
     Application.WorksheetFunction.Average(DigitalCallPayoffs)
    Range("MCDigitalPricePut") = _
     Application.WorksheetFunction.Average(DigitalPutPayoffs)
    Range("MCTimer") = (TimeEnd - TimeStart) * 24 * 60 * 60
End Sub

The Monte Carlo now takes approximately the same time to calculate four option prices as it took to calculate one:

This highlights another key feature of Monte Carlo pricing: Once sample paths have been generated they can be reused to simultaneously price multiple different option payoffs to different expiry dates.

Task D: Pricing Barrier Options

European Barrier Options

The payoff from a European barrier option depends only on spot at maturity so European barrier options can be priced in the same Monte Carlo framework using an adjusted payoff calculation:

Plus the VBA needs to be updated so the Monte Carlo code grabs the European barrier payoff rather than the vanilla payoff.

American Barrier Options

For American knock-out barrier options, if spot ever trades through the barrier level, the product knocks out. Therefore, as a first approximation, at each time step spot can be checked against the barrier levels (0 = no knock/1 = knock):

Then the payoff must be adjusted accordingly:

This is clearly not quite right since the barrier is effectively being discretely monitored once a day rather than being continuously monitored. There are many advanced Monte Carlo techniques for solving this problem. One simple approach would be to use tighter time steps but this also increases the Monte Carlo runtime. Another accessible approach would be to use the Broadie, Glasserman, Kou formula for converting a continuous barrier to the equivalent discrete barrier given in Chapter 26.

Once American knock-out barrier options are being successfully priced, American knock-in barrier options can be priced simply by adjusting the payoff formula or using the vanilla price, which can be effectively calculated for free.

Window Barrier Options

Window barriers options, can be implemented within the framework by adding new date inputs and adjusting the isKnock test within the simulation:

The flexibility of the Monte Carlo approach should now be starting to become apparent. The process of moving between different barrier types is far easier within Monte Carlo than within closed-form pricing.

Task E: Multi-Asset Simulation

In order to introduce multiple assets into the Monte Carlo framework, correlations between the paths must be defined. This can be done by generating sequences of independent normal random numbers and then adjusting the sequences to be correlated.

In the two-asset case, start with two uncorrelated sequences, and . Define . The new sequence Y has a correlation of to the sequence. Plus briefly note that this formula will enter the realms of the imaginary if .

This can be set up in the simulation:

The transformation can be confirmed by plotting against in a scatter plot:

Then plotting against for comparison (80% correlation shown):

The correlated values can be used to drive the spot processes, hence making spot log returns correlated, as required:

Multi-asset option payoffs that depend on both spot levels at maturity can therefore be set up. For example, a dual digital option (introduced in Chapter 30) pays out in the common currency (CCY2 in this case) if both spots are above defined levels:

To move from a two-asset framework into an N-asset framework, an N × N correlation matrix must be defined. For example:

This correlation matrix is then converted into multipliers for converting non-correlated sequences into correlated sequences using either Cholesky factorization or eigenvector decomposition.

Extensions

There are numerous possible extensions to this basic Monte Carlo framework. For example, term structure of volatility and interest rates could be used, the volatility surface could be added, or stochastic interest rates implemented.

The volatility surface would be added using a pricing model. For example, under a stochastic volatility model there would be an additional variance term evolving with spot over time.

In practice, trading desks use either Monte Carlo or partial differential equations (PDEs) to value derivative contracts. In both cases, the challenge for quants is to add the effects of pricing models into the option valuations as efficiently as possible.