3339
FNAN 491 Computational Finance Using Excel and Mathematica
Problem Set 2: Black-Scholes Options Pricing Formula and Computation of Implied 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.
Attachments:
FNAN-491-Prob….docx
Problem-set-2….xlsx
