Code School for Kids

Using Excel to Estimate the Value of Options with Monte Carlo Simulation

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.

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, and then discount these ending values back to present day to value the option with Monte Carlo simulation. This value can then be compared to actual option price quotes to determine if options are expensive or cheap.

You can download the file used in the video here