FNAN 491 Computational Finance Using Excel and Mathematica
Problem Set 2: Black-Scholes Options Pricing Formula and Computation of Implied
... [Show More] Volatility
This problem set is to allow students to use the Black-Scholes (B-S) Options Pricing
Formula to compute implied volatility for various strike prices for 3 stocks from the portfolio
used in the first problem set computing the eficient fromtier for group of stocks. The spreadsheet
template is found on Blackboard for the course in file:
Black_ScholesOption Pricing Imp_Vol_AAPL (1).xlsx. The template sets up the use of
SOLVER to find the volatility implied by current call and put options prices.
Choose 3 stocks from your Problem Set 1. You have already computed the historical
variance for each stock which you will use to compute the B-S options price for each of the 3
stocks. Use finance.yahoo.com to find the call and out options prices for exercise prices for at the
money calls and puts. Choose an expiration date of July 19, 2019. Record the ask options price
and its implied volatility. Do the same for an immediately lower and higher exercise price for
each call and put.
Use the template to store your data for each stock. Compute the B-S call and put options
price using your previously found historical volatility and the OptionPricing.ppt PowerPoint for
reference of B-S option pricing computation.
To compute implied volatility, run SOLVER setting the Objective to either target (for call
options) or target2 for put options. Note where these are located in the spreadsheet. You can
change the names of the Objective location to whatever you want, but note the formula in each
target or target2 cell. The SOLVER operation will find the sigmav and change the value in the
cell. Record each sigmav that you compute for each exercise price for each stock.
The pattern of the relationship between the exercise prices and the implied volatility for
each stock is then graphed comparing call and put options. Plot the exercise price on the
horizontal axis and the implied volatility on the vertical axis. See the example below.
Write up your analysis of these comparisons. [Show Less]