Code. Models. Analysis. Decisions.
Option Pricing with Monte Carlo Simulation in Excel
For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007
Stock Options
Stock options confer the right, but not the requirement to buy or sell a security at a specified price for a specified amount of time. Options contracts are standardized securities such that each contract controls 100 shares of the underlying security. Further, options contracts may be good for a range of time increments from several days to several years.
In general options allow the purchaser to control a specific number of shares a cost far below that of outright purchasing or shorting the underlying security.
Monte Carlo Simulation Excel for Valuing Options
There are a number of methods used to value options. One common method is the Black-Scholes-Merton formula whereby a static value of either a call or put is generated based on several inputs including, volatility, time, value of the underlying and risk-free rate. This tutorial uses a derivation of that formula to estimate thousands of potential ending prices for the underlying security, a technique named Monte Carlo Simulation, using Excel. We then discount these ending values back to present day to value the option. This value can then be compared to actual option price quotes to determine if options are expensive or cheap.
Option Pricing Using Monte Carlo Simulation Excel
The following video is a short tutorial demonstrating option pricing using Monte Carlo simulation in Excel.
You can download the file used in the video here