The Black-Scholes option-pricing model is a very useful and easy to implement tool for options trading. Finance professionals use the Black Scholes model to price European style call and put options. To implement the Black Scholes option pricing formula, the following inputs are required: the exercise price of the option, the time to maturity, the risk-free rate of interest, the current price of the underlying asset and the price of the option.
Since the option price is an input, what is the value of a formula that calculates option prices? Option prices are set in markets through supply and demand. The Black Scholes option-pricing model is useful because it allows you to calculate the implied volatility of the underlying asset. This is the only parameter of the model that is unobserved in the market. To create your Black Scholes Excel model, follow these steps:
1. Learn to interpret the Black Scholes option-pricing model.
2. Find a source for the necessary data.
3. Create the formula in Excel.
Action Steps
The best contacts and resources to help you get it done
Understand the Black Scholes option formula
There are numerous ways to use the Black Scholes model. First, you can use it to calculate the implied volatility of an option. For trading purposes, you can compare the volatility of the underlying asset.
I recommend: First, make sure you are calculating the formula correctly. The formula for option pricing is complex. See it at
The Options Guide. There are several metrics that are partial derivatives of the Black Scholes formula, which you can implement in Excel. These formulas are on
Wikipedia. Each partial derivative has a specific meaning in options trading.
Find a source for the data so you can create an Excel sheet that updates automatically
Only some of the Black Scholes Excel model information needs to be updated using external data. You will need a source for the stock price and the call price. The time to expiry reduces by one every day, and the exercise price is fixed for European options.
I recommend: Real time quotes are available for a fee from various providers, including the
Chicago Board Options Exchange (CBOE). Delayed quotes are available free. The website allows you to download daily option quotes as a text file. Become familiar with the format of this text file so you can quickly import it into your Black Scholes option pricing model spreadsheet. From there, target your formulas so they update automatically when you input the new data.
Create the Black Scholes Option Pricing Model spreadsheet in your workbook
After you have settled on a constant format for your Black Scholes option pricing model information, you need to create the formula. You can use Excel's built-in statistical formulas to calculate two variables. Then you will need to calculate the implied volatility of the options you are examining. You can automate this by using the "Solver" function in Excel.
I recommend: Find some pre-made spreadsheets that you can adapt for your specific purposes.
Option Trading Tips has a workbook for European options. There are also several books written about using Excel and Visual Basic (built into Excel) for pricing more complex financial derivatives.
Tips & Tactics
Helpful advice for making the most of this Guide
- Remember, the Black Scholes option-pricing model is for European calls and puts. Most other options require a different approach, but you can use the Black Scholes model effectively for American call options as well as American put options on non-dividend paying stock.
The official source of Black Scholes Option Pricing Model is
the Black Scholes Option Pricing Model page at Business.com
Subscribe to
Try our free weekly WhatWorks newsletter, with business how-to advice
& resources from Work.com.
Related Resources from Business.com
Comments
Is any content on this page inappropriate? To let us know, please 
