How to Create an Options Calculator With Microsoft Excelby James Highland
Microsoft Excel is a versatile tool that functions in a variety of environments. Financial applications are particularly well-suited to Excel. Its robust mathematics and spreadsheet format allow the easy manipulation of financial scenarios. Stock options are complicated investment vehicles that are priced according to many variables. The price of an underlying stock is only part of the complex relationship between volatility measurements, time decay and other factors that weight strongly on an options price. Brokerage firms often provide options calculators to their clients to better understand an option trading strategy, but Excel is also capable of handling this task, with some customization.
Download an options pricing model add-in for Excel. The program does not offer native support of the complex formulas necessary to price options. An add-in extends the Excel library so anyone can create an options calculator from the necessary functions. A good free option for this add-in is the Ray Steele Black-Scholes Options Pricing Model available from exinfm.com/free_spreadsheets.html.
Install the add-in. Some Excel add-ins contain their own installation program to automate this process. Others, such as the Ray Steele variation, offer a raw add-in file that needs to be copied to the Excel library directory in Windows. Unzip the Ray Steele Excel add-in and move the options.xla file to the Microsoft Office Library folder appropriate for your version of Excel. This will often resemble the C:\Program Files\Microsoft Office\OFFICE11\LIBRARY directory address.
Create a new spreadsheet and list the key variables for an option price on the sheet. For a standard call option, for example, the spreadsheet should contain individual cells which hold values for the stock price, the exercise strike price of the option contract, and the number of days until expiration. These cells may be placed anywhere and labeled as desired.
Build a cell formula in another cell on the same worksheet. This will calculate the option price based on the specified parameters. This formula will use option pricing functions included in the Excel add-in. The BS_CALL function from Ray Steele's add-in, for example, is used to calculate the call option price. Use the cell references for the pricing variables in the formula.
Change the option pricing variables listed on the spreadsheet and watch the calculator adjust the option price based on these edits.
Items you will need
- photo_camera Siri Stafford/Digital Vision/Getty Images