Spreadsheet Exercises 19-1 There are various ways to calculate the price of a call option using the Black-Scholes model Below is a spreadsheet that breaks the required formulas into pieces to make it easy to work with Column (1) shows the various inputs. The first five cells are the required inputs for a non-dividend paying stock. The remainder of the cells are the formula parts Column (2) shows a solved problem for a stock selling for $50, with an exercise price of $45, an interest rate of 6 percent, 90 days (one-quarter of a year), and a standard deviation of 235 Column (3) shows how the cell values in Column (2) were calculated. Once you have this set up in the spreadsheet, you can calculate the price of any call option by substituting the correct values in the first five cells of column (2) Spreadsheet begins in row 2 Calculating a Call Price Using the Black-Scholes Model SO 45 R 0.06 T 0.25 5 0.235 0.105361 LN(12/03 0.08760 14+(5)*(B6) 2 0.1175 10" (15/05) di 1083095 (87+(B8*85)389 0965595 B10:29 N(1) 0860617 NORMSDIST(BIO) N(42) 0832877 NORMSDIST(BIT) SN(dl) 4303084 82812 27183 0985112 BISA - (84-85) Cal Price 6.109398 B14-(63*316*3!?) Given a stock price of $42, an exercise price of $40, an interest rate of 6 percent, a time to expiration of 90 days, and a standard deviation of 65, solve for the call price +05