I have a few mini case questions from Corporate Finance 10th ed Ch 22. I have posted the questions on clissold industries options and what I think is the workbook needed for q2 using solver. I cannot get the 3rd and forth option prices to work on solver. any help on all of the question would be appreciated.

Clissold Industries Options* You are currently working for Clissold Industries. The company, which went public five years ago, engages in the design, production, and distribution of lighting equipment and specialty prod ucts worldwide. Because of recent events, Mal Clissold, the company president, is concerned about the company's risk, so he asks for your input. In your discussion with Mal, you explain that the CAPM proposes that the market risk of the company's stock is the determinant of its expected return. Even though Mal agrees with this, he argues that his portfolio consists entirely of Clissold Industry stock and options, so he is concerned with the total risk, or standard deviation, of the company's stock. Furthermore, even though he has calculated the standard deviation of the company's stock for the past five years, he would like an estimate of the stock's volatility moving forward. Mal states that you can find the estimated volatility of the stock for future periods by calculating the implied standard deviation of option contracts on the company stock. When you examine the factors that affect the price of an option, all of the factors except the standard deviation of the stock are directly observable in the market. Mal states that because you can observe all of the option factors except the standard deviation, you can simply solve the BlackScholes model and find the implied standard deviation. To help you find the implied standard deviation of the company's stock, Mal has provided you with the following option prices on four call options that expire in six months. The riskfree rate is 4 percent, and the current stock price is $68. 1. What number of different volatilities would you expect to see for the stock? 2. Unfortunately, solving for the implied standard deviation is not as easy as Mal suggests. In fact, there is no direct solution for the standard deviation of the stock even if we have all the other variables for the BlackScholes model. Mal would still like you to estimate the implied standard deviation of the stock. To do this, set up a spreadsheet using the Solver function in Excel to calculate the implied volatilities for each of the options. Strick Price ($) Option Price ($) 65 18.73 70 15.69 75 11.06 80 7.36 Table 1: Clissold Industries Options 3. Are all of the implied volatilities for the options the same? (Hint: No.) What are the possible reasons that can cause different volatilities for these options? 4. After you discuss the importance of volatility on option prices, your boss mentions that he has heard of the VIX. What is the VIX and what does it represent? You might need to visit the Chicago Board Options Exchange (CBOE) at www.cboe.com to help with your answer. 5. When you are on the CBOE website, look for the option quotes for the VIX. What does the implied volatility of a VIX option represent? rRF = Risk-free interest rate t= Time until the option expires E= Exercise price S= Current price of the underlying stock s = Variance of the stock's rate of return 2 rRF = t= E= S= s2 = 4% 4% 0.5000 ### 65 70 68 68 0.8162951702 0.70961676 N(d2)=NORMDIST(D2) 0.4213637085 0.28274055 -0.2175005846 -0.31291739 0.6632552422 0.61131213 0.4139091257 0.37717171 V = S[ N (d1) ] - Xe-rRF t [ N (d2) ] 18.7300000365 d1 = { ln (S/E) + rRF + s2 /2) ] t } / (t1/2 ) d2 = d1 - s (t 1 / 2) N(d1)= NORMDIST(D1) 15.69 4% 0.5000 75 68 4% 0.5000 80 68 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0