Finance Modeling

Implied Variance for the Black Scholes Model

One of the difficulties with the Black-Scholes model is determining the variance for the security. One approach is to compute it directly by observing price behavior over time (historical). Another approach is to use the Black Scholes model to get the implied volatility. With this, we can use actual values for all variables but the variance and then use the model to solve for the variance.
When we examine the Black Scholes model, we see that it would be impossible to manipulate the equation so that V ends up on the left-hand side all by itself. This forces us to use an iterative approach to solve for the volatility. The custom Black Scholes function that we constructed in the previous project makes it easy for us to solve for call prices. Therefore, we can set things up so that we can try different variances until we find the one that gives us the actual (known) call price that is observed in the market.

Where S is the current stock price, X is the current exercise price of the call option, R is the risk free rate, V is the variance of the return, T is the time measured as a fraction of a year.
What to Do:
Go to a listing of traded option prices and select a firm that has options with at least 3 different exercise prices. Construct a table that contains the name of the firm, its stock price, and the expiration date of the options being considered. (To keep our analysis simple, we consider options only with a common expiration date.) Record the exercise price and the market price of the call for each of the three options being used. Try to have one option whose exercise price is close to the stock price, one below and one above.
Use a risk-free rate of 2%. Compute the fraction of years remaining on the option. Options expire on the 3rd Friday of the listed month (make sure to account for this when calculating time to maturity).
Create a table comparing volatility levels and the three exercise prices. Put trial values for volatility in the left- most column of the table. Then along the top put the exercise prices of the options you are considering. (Notice that we are keeping everything else constant.)
The cells within the table should return the call price for the given volatility and exercise prices. Use the Data Table command to do this. Circle or highlight that call price that is closest to the actual call price. If there is nothing close to the actual price, change the values you are using in the left-most column. This format enables you to work with all three options at the same time in a relatively easy manner.

Make sure that the file is saved as a Macro Enabled Workbook!
Answer the following questions:
1)What source did you use to get your data??2)Discuss your results. What do the numbers mean? Are you surprised you didn’t get exactly the same result for each option (or maybe you did)?