Solution to European Options[1]

A European call option on a stock earns the owner an amount equal to the price at expiration minus the exercise price, if the price of the stock on which the call is written exceeds the exercise price. Otherwise, the call pays nothing. A European put option earns the owner an amount equal to the exercise price minus the price at expiration, if the price at expiration is less than the exercise price. Otherwise the put pays nothing. The file 02a-01-euro-0.xls contains a template that computes (based on the well-known Black-Scholes formula) the price of a European call and put based on the following inputs: today's stock price, the duration of the option (in years), the option's exercise price, the risk-free rate of interest (per year), and the annual volatility in stock price. For example, a 40% volatility means approximately that the standard deviation of annual percentage changes in the stock price are 40%.

  1. Consider a 6-month European call option with exercise price $40. Assume a current stock price of $35, a risk-free rate of 5%, and an annual volatility of 40%. Determine the price of the call option.

The Black-Scholes model:

where:

S / = current stock price
E / = exercise price
r / = risk-free rate of return
σ2 / = variance of the stock’s return
t / = time to expiration
d1 / =
d2 / =
N(d) / = probability that z < d

This formula can be set up in a spreadsheet as shown here:

See the end of this document for details regarding the Excel functions used.

Notice the use of “if” statements in cells E10:E11 and B13, so that the same model can be used for both puts and calls.

From this basic model, we can infer that a fair price for a 6-month European call option with exercise price $40. Assume a current stock price of $35, a risk-free rate of 5%, and an annual volatility of 40% is $2.456.

  1. Use a data table to show how a change in volatility changes the value of the option. Give an intuitive explanation for your results.

The data table feature allow us to perform sensitivity analysis — studying how outputs change in response to changes in inputs.

i)First, set up two columns; one for the input to be varied (volatility) and one for the output (price).

ii)Then, in the second row of the data table, directly under the column heading, use the equals sign to make a link to the output cell in the model.

iii)In the input (volatility) column, enter values for the input variable. (Leave a blank row at the top of this column.)

iv)Select the range of cells you want, with the blank input cell in the upper left (in this case, we select A16:B37).

v)Select Data — Table, and you should see this small dialog box. This model is using a column of input values for volatility (cell B7 in the model), so we enter B7 as the Column Input Cell.

vi)Click OK, and Excel will do the rest.

As is frequently the case, our results can be communicated best using a graph:

As the stock’s returns become increasingly volatile, the option becomes more expensive. This is intuitive, because as volatility increases, the seller of the option is assuming an increasing risk of a large payout upon maturity.

  1. Use a data table to show how a change in today's stock price changes the option's value. Give an intuitive explanation for your results.

The more expensive the stock is currently (with respect to the strike price), the more valuable the option is. This is intuitive, because the stock is more likely to be above the strike price upon maturity.

  1. Use a data table to show how a change in the option's duration changes the option's value. Give an intuitive explanation for your results.

As the option grows in duration, the price of the option increases. This makes sense, because the seller is assuming a greater risk that the stock will be selling at a price higher than the strike price upon maturity.

Excel functions used:

LN / Returns the natural logarithm of a number. Natural logarithms are based on the constant e (2.71828182845904).
Syntax: LN(number)
Number is the positive real number for which you want the natural logarithm.
LN is the inverse of the EXP function.
LN(86) equals 4.454347
LN(2.7182818) equals 1
LN(EXP(3)) equals 3
EXP(LN(4)) equals 4
SQRT / Returns a positive square root.
Syntax: SQRT(number)
Number is the number for which you want the square root. If number is negative, SQRT returns the #NUM! error value.
SQRT(16) equals 4
SQRT(-16) equals #NUM!
SQRT(ABS(-16)) equals 4
NORMSDIST / Returns the standard normal cumulative distribution function. The distribution has a mean of 0 (zero) and a standard deviation of one. Use this function in place of a table of standard normal curve areas.
Syntax: NORMSDIST(z)
Z is the value for which you want the distribution.
If z is nonnumeric, NORMSDIST returns the #VALUE! error value.
NORMSDIST(1.333333) equals 0.908789
EXP / Returns e raised to the power of number. The constant e equals 2.71828182845904, the base of the natural logarithm.
Syntax: EXP(number)
Number is the exponent applied to the base e.
To calculate powers of other bases, use the exponentiation operator (^).
EXP is the inverse of LN, the natural logarithm of number.
EXP(1) equals 2.718282 (the approximate value of e)
EXP(2) equals e2, or 7.389056
EXP(LN(3)) equals 3
IF / The IF worksheet function checks a condition that must be either true or false. If the condition is true, the function returns one value; if the condition is false, the function returns another value. The function has three arguments: the condition you want to check, the value to return if the condition is true, and the value to return if the condition is false.
=IF(logical_test,value_if_true,value_if_false)

B60.23501Prof. Juran

[1] Based on 2-5 and 2-6 (p. 59) in Practical Management Science (2nd ed., Winston and Albright, 2001 Duxbury Press). Solution by David Juran, 2001.