Browse By

Option Pricing using Monte Carlo Simulation – Pricing Exotic & Vanilla Options in Excel – Introduction

Option pricing using Monte Carlo Simulation

In our post on Option Pricing using Monte Carlo Simulation, we walk through a simple modeling framework used for pricing vanilla as well as exotic options in Excel. After the framework is introduced we drop a few hints on how to price Asian, Barrier, Ladder & Chooser options using Monte Carlo Simulation in Excel spreadsheets.

Let’s start with a look at a desired end state snap shot when it comes to building option pricing model in a spreadsheet.

Option Pricing using Monte Carlo Simulation

Our Excel Option pricing model (shown below) has the following key pieces (highlighted and marked up)

  1. A section for Model inputs driven by market factors and term sheet variables (Section 1)
  2. A simulator that simulates the underlying variable and uses simulation results to produce a range of intermediate values (Section 2 and 3)
  3. A Pricing and Monte Carlo Simulation results store where we store the results from our desired number of simulations (Section 4)
  4. A Model Output section where we summarize and present our model output. (Section 5)

Option Pricing using Monte Carlo Simulation

Option Pricing using Monte Carlo Simulation – Model Focus

Of the above components in general model input, the underlying price simulator, model output and Monte Carlo simulation data store remain the same (structurally speaking) from one option pricing exercise to the next. While model values and parameters would certainly change, there is not a lot of tweaking or remodeling required. The only thing that does change from one pricing exercise to the next are the intermediate values that we use to price the product in question.

Sometimes model parameters and input values also change based on the option product family being priced. For example for barriers and ladder options you would need to add a variable to define barrier and ladder thresholds.

Since we have already covered the basics of both Monte Carlo Simulation and Option pricing earlier we won’t spend a lot of time on basics. (If you need to get a quick Monte Carlo Simulation refresher, please look through our Monte Carlo Simulation How To Reference.)

Our primary focus will remain on Section 3, the intermediate values, that we change within our models as we move from one pricing exercise to the next. Section 3, however is also broken down into four steps and within that set of steps, our focus will remain on step 2 that calculates Option Pay for a given simulation run.

For example pricing a vanilla Call option, option payout will be Max(St – X,0). For a Put Option this will change to Max(X – St,0). For both instances, St is the terminal value of the underlying being simulated, while X is the strike price. The value of the option for that iteration will be simply the discounted present value (PV) of the Payout. We will store PV of Payout in our simulation results warehouse and move on to the next iteration. When the desired number of iterations (simulation runs) are completed we will take the average of the stored results and use that as an estimate of the actual option price.

For an Asian Option, the payout will take the average of the path used to reach the terminal value. For a look back option we will use the maximum value since that is the only option available to find the average maximum payout we are likely to hit and price the option accordingly.

Barriers and ladders are a little more involved because we have to break the pricing and payout estimation process into two steps. In the first step we determine if the barrier has been hit or breached then calculate the payout. The option price is a product of a barrier being breached (yes or no, 1 or 0) and the present value of payout. Now think how you would price a ladder as a possible collection of multiple barriers.

Finally chooser and compound options not just require multiple steps, they also require multiple models, but more on that later in the next part of our post.