How to solve this in Excel Choose a stock that you would like to study (Apple) that has a current set of liquid put and call options. You can check...

How to solve this in Excel

Choose a stock that you would like to study (Apple) that has a current set of liquid put and call options. You can check whether the stock has listed options at https://finance.yahoo.com. Select a single option maturity date between 12/15/2017 and 6/15/2018, and gather data on the ten call options expiring on that date that have strike prices closest to the current stock price. The data items you'll want to gather include the following: (1) strike price; (2) last trade price for each of the ten call options; (3) last trade price for the stock; (4) maturity, in years, for the ten contracts; and (5) implied volatility as reported by Yahoo Finance. (Note: if Yahoo Finance reports zero trading volume for an option contract, use the average of the contract's bid and ask prices to measure the last trade price.)

A.  Write a formula in Excel for the Black-Scholes option value of a call, and then use either Solver or Goal Seek to calculate the implied volatility for each of the ten call options. In your calculations, you can assume the following:

·      Dividend yield is zero

 ·    Annualized, continuously-compounded risk-free rate is equal to the 1-year Treasury rate (see www.treasury.gov for data).

B. Compare the implied volatilities you calculated against the ones reported in Yahoo Finance. Are they the same? If not, what might explain the discrepancy?

C. Graph the implied volatilities for the 10 options against the moneyness of the contract (measured as the ratio of stock price to option strike price). Describe the shape of your graph. Based on the results, what can you say about market demand for the different options and about the accuracy of your Black-Scholes calculation?