Excel Problems

Black-Scholes Option Valuation - Click here to download the spreadsheet (83.0K)

The accompanying spreadsheet can be used to determine option values using the Black-Scholes Model. The required inputs include stock price, standard deviation, maturity of the option, exercise price and the risk-free rate. The model values the call option using equation 18.1 in the text. The value for the put option is derived using equation 18.2. In addition to option values, the model calculates the intrinsic and time value for both puts and calls.

The spreadsheet also presents sensitivity analysis using the one-way data table. For most of the variables that are used in the Black-Scholes Model, ranges of the variables are simulated so that sensitivity of option values and time premiums can be evaluated. The sample option has a current stock price of $100, an exercise price of $105, six months to maturity and standard deviation of returns of .2783. The current risk-free rate is 5%. The results in the tables can be used to strengthen your understanding of the relationship of each of the variables on option values.

The first workbook presents the analysis of calls while the second workbook presents similar analysis of puts.

Questions

1. Complete an analysis similar to the above spreadsheet for the following call option.

   Stock Price = $150	Exercise Price = $140	Standard Deviation = .35
   Maturity = 9 months	Risk-free Rate = 7%	Dividend Yield = 0

   For the sensitivity analysis allow the standard deviation of returns to vary from 0.1 to 0.8 in increments of 0.05, allow the stock price to vary from $115 to $190 in increments of $5, allow the maturity to range from 0.15 years to 1.1 years in increments of 0.05, and allow the risk-free rate to range from 3% to 12.5% in increments of 0.5%. For each of the above variable ranges calculate the call option value and the time value.
2. Complete an analysis similar to the above spreadsheet for the following put option.

   Stock Price = $140	Exercise Price = $150	Standard Deviation = .35
   Maturity = 9 months	Risk-free Rate = 7%	Dividend Yield = 0

   For the sensitivity analysis allow the standard deviation of returns to vary from 0.1 to 0.8 in increments of 0.05, allow the stock price to vary from $115 to $190 in increments of $5, allow the maturity to range from 0.15 years to 1.1 years in increments of 0.05, and allow the risk-free rate to range from 3% to 12.5% in increments of 0.5%. For each of the above variable calculate the option value and the time value.