Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 24, Black-Scholes and binomial page 14
other parameters. The spreadsheet also includes a function called PutVolatility which computes
the implied volatility for a put option.
4
Both functions are illustrated below:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
AB C
TWO IMPLIED VOLATILITY FUNCTIONS
Using CallVolatility to compute the implied volatility for a call
S 35 Current stock price
X 35 Exercise price
T 0.50000 Time to maturity of option (in years)
r 6.00% Risk-free rate of interest
Target 5.25 <-- This is the current call price we want to match
Implied volatility 48.71% <-- =CallVolatility(B4,B5,B6,B7,B8)
Using PutVolatility to compute the implied volatility for a call
S 35 Current stock price
X 35 Exercise price
T 1.00000 Time to maturity of option (in years)
r 6.00% Risk-free rate of interest
Target 3.44 <-- This is the current put price we want to match
Implied volatility 32.49% <-- =putVolatility(B13,B14,B15,B16,B17)
24.6. Doing sensitivity analysis
We can use Excel to do a lot of Black-Scholes sensitivity analysis. In this section we
give two examples, leaving other examples for the chapter exercises.
Example 1: The sensitivity of the Black-Scholes call price to the stock price
4
In the spirit of this chapter, we do not explain how these functions work. For details see my book Financial
Modeling.
PFE Chapter 24, Black-Scholes and binomial page 15
For example, the following Data|Table (see Chapter ???) gives—as the stock price S
varies—the Black-Scholes value of the call compared to its intrinsic value (i.e., max(S-X,0) ) .
Note that we have not shown the header of the data table.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
AB C DEFGH I JKLM
BLACK-SCHOLES OPTION FUNCTIONS
The functions in this spreadsheet--
Calloption
and
Putoption
--were
defined by the author; they are part of this spreadsheet.
S 100 Current stock price
X 90 Exercise price
T 0.50000 Time to maturity of option (in years)
r 4.00% Risk-free rate of interest
Sigma 35% Stock volatility
Call price 16.32 <-- =calloption(B5,B6,B7,B8,B9)
Put price 4.53 <-- =putoption(B5,B6,B7,B8,B9)
Stock price
BS call
price
Option
intrinsic
value
16.32 10
65 0.974881 0
70 1.823552 0
75 3.084287 0
80 4.808631 0
85 7.015908 0
90 9.695162 0
95 12.81164 5
100 16.31546 10
105 20.14963 15
110 24.25671 20
115 28.58313 25
120 33.08179 30
125 37.713 35
130 42.4445 40
135 47.25076 45
140 52.11206 50
This cell is part of the
data table header; it
contains the formula
=B11.
This cell is part of the data table
header. It contains a formula
=MAX(B5-B6,0)
which the
option's intrinsic value.
Comparing the BS Option Price (the curved line)
to the Option Intrinsic Value
when the stock price S is varied
-10
0
10
20
30
40
50
60
65 75 85 95 105 115 125 135
Stock price
Example 2: The sensitivity of the Black-Scholes price to different estimates of
σ
Here’s the sensitivity analysis of the Black-Scholes price to the
σ
:
PFE Chapter 24, Black-Scholes and binomial page 16
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
AB C D E FGH I J KLM
BLACK-SCHOLES SENSITIVITY ON SIGMA
S 100 Current stock price
X90Exercise price
T 0.50000 Time to maturity of option (in years)
r 4.00% Risk-free rate of interest
Stock price
BS call
price
Option
intrinsic
value
13.15 19.90771512
10 0.00 0.00
20 0.00 0.00
30 0.00 0.01
40 0.00 0.09
50 0.00 0.53
60 0.01 1.78
70 0.24 4.25
80 1.72 8.14
90 5.96 13.41
100 13.15 19.91
110 22.14 27.38
120 31.86 35.60
130 41.80 44.37
140 51.78 53.53
150 61.78 62.96
160 71.78 72.57
This cell is part of the data table
header; it contains the formula
=calloption(B5,B6,B7,B8,20%).
This cell is part of the data table header.
It contains a formula
=calloption(B5,B6,B7,B8,50%).
Comparing the BS Option Price (the curved line)
for 2 values of Sigma
The top line is the higher sigma
-10
0
10
20
30
40
50
60
70
80
10 30 50 70 90 110 130
Stock price
24.7. Does the Black-Scholes model work? Applying it to Microsoft options
To examine whether and how well the Black-Scholes model works, we do two
experiments in this section. First we compare the Black-Scholes option prices for a set of put
and call options on Microsoft stock to the actual market prices. Then we compare the implied
volatilities for the same options.
Our conclusion: Black and Scholes works “pretty well.” That’s a big complement for a
financial model!
Comparing actual market prices to Black-Scholes prices
The experiment we run here looks at options on Microsoft stock.
•
On 8 February 2002 we look at the call and put options on Microsoft stock which expire
in July 2002.
PFE Chapter 24, Black-Scholes and binomial page 17
• We calculate the Black-Scholes price of these options and compare it to the actual market
price.
As you will see, our conclusion is that the Black-Scholes model works pretty well.
We get our data from Yahoo, which allows us to look up the stock price of Microsoft on
8 February 2002 and also look up the prices of Microsoft options.
The closing stock price of Microsoft stock on 8 February 2002 was $60.65. The stock
was up 1.42% from the previous day’s close, and the total volume of stock traded was
30,642,600 shares.
We now look at the closing prices of options on Microsoft stock which expire in July
2002. Clicking on
options in the above box leads us to the option prices:
PFE Chapter 24, Black-Scholes and binomial page 18
Look carefully at the above box:
•
Not all the options were traded on 8 February. For example—there was no “volume”
(and hence no trading) of either calls or puts with exercise price (“strike price”) of 25.
•
Significant amounts of call options traded on 8 February were only for exercise prices
X=60, 65, 70, 75, 80, 85. Significant amounts of put options traded were only for
exercise prices X = 45, 50, 55, 60, 65, 70.
•
The price of the “last trade” is in bold face black. But where there is no volume for this
day, the price refers to a previous day’s trading.
In the spreadsheet below we look at the Microsoft July options which actually traded on
8 February and compare the Black-Scholes price to the actual market price. We use the 6-month
Treasury bill rate of 1.7% as our risk-free rate.
5
5
We computed MSFT’s volatility by using Goal Seek to find
σ
such that the difference between the market price
and the Black-Scholes price of the at-the-money call is zero.
PFE Chapter 24, Black-Scholes and binomial page 19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
ABC D E F GH I JK
MICROSOFT OPTIONS: Comparing BS to actual prices
This spreadsheet computes the Black-Scholes value of the Microsoft
July 02 options on 8 February 2002 and compares
the prices to the actual market prices. As you can
see, the Black-Scholes formula works pretty well!
Computing the time to maturity
S 60.65 Microsoft stock, closing price 8 Feb 02 Current date 8-Feb-02
T 0.35890 Time to maturity of option (in years) Expiration date 19-Jun-02
r 1.70% Risk-free rate of interest Time (days) 131 <-- =G8-G7
Sigma 35.38% Stock volatility Time (% of year) 0.3589 <-- =G9/365
Exercise
price
BS call
price
Actual call
market price
Market minus BS
in dollars
Market minus BS
in percentage
50 12.04 12.30 0.26 2.14% <-- =(D14-C14)/D14
55 8.43 8.70 0.27 3.11% <-- =(D15-C15)/D15
60 5.60 5.60 0.00 0.00%
65 3.54 3.80 0.26 6.82%
70 2.14 2.15 0.01 0.40%
75 1.25 1.10 -0.15 -13.20%
80 0.70 0.60 -0.10 -16.69%
85 0.38 0.35 -0.03 -9.30%
Exercise
price
BS put
price
Actual put
market price
Market minus BS
in dollars
Market minus BS
in percentage
45 0.37 1.00 0.63 62.83% <-- =(D26-C26)/D26
50 1.08 2.00 0.92 45.88%
55 2.44 3.30 0.86 25.92%
60 4.59 5.40 0.81 15.09%
65 7.50 8.30 0.80 9.69%
70 11.07 12.30 1.23 10.04%
BS Call Option Pricing--
Microsoft July 02 Options
-20%
-15%
-10%
-5%
0%
5%
10%
50 55 60 65 70 75 80 85
Market minus BS
in percentage
BS Put Option Pricing--
Microsoft July 02 Options
0%
10%
20%
30%
40%
50%
60%
70%
45 50 55 60 65 70
Market minus BS
in percentage
The pattern of the prices is interesting:
•
In fact the BS model does a remarkably good job of pricing the calls.
•
There appears to be a much bigger bias in the put prices. Investors appear to price low
exercise puts at more than the Black-Scholes price. This phenomenon is often seen in
markets—it apparently stems from investor demand for puts as insurance. Having said
this, however, the market prices and the Black-Scholes prices show a remarkable
convergence.
Does the Black-Scholes model work? Looking at implied volatilities
PFE Chapter 24, Black-Scholes and binomial page 20
This is our second experiment. We take the Microsoft data above calculate the implied
volatility for each option (using the functions
CallVolatility and PutVolatility discussed in
Section ????). Here’s our spreadsheet:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
ABC D E F GH
MICROSOFT OPTIONS: Computing the implied volatilities
This spreadsheet computes the implied volatility of the Microsoft
July 02 options on 8 February 2002 and compares
The average volatility of the calls appears to be lower
than the average implied volatility of the puts
Computing the time to maturity
S 60.65 Microsoft stock, closing price 8 Feb 02 Current date 8-Feb-02
T 0.35890 Time to maturity of option (in years) Expiration date 19-Jun-02
r 1.70% Risk-free rate of interest Time (days) 131 <-- =G8-G7
Time (% of year) 0.3589 <-- =G9/365
Exercise
price
Actual call
market price
Implied
volatility
50 12.30 38.42% <-- =CallVolatility($B$7,B14,$B$8,$B$9,C14)
55 8.70 37.60% <-- =CallVolatility($B$7,B15,$B$8,$B$9,C15)
60 5.60 35.38%
65 3.80 37.20%
70 2.15 35.45%
75 1.10 33.89%
80 0.60 33.96%
85 0.35 34.72%
Exercise
price
Actual put
market price
Implied
volatility
45 1.00 46.46% <-- =putVolatility($B$7,B26,$B$8,$B$9,C26)
50 2.00 45.32% <-- =putVolatility($B$7,B27,$B$8,$B$9,C27)
55 3.30 42.30% <-- =putVolatility($B$7,B28,$B$8,$B$9,C28)
60 5.40 41.10%
65 8.30 41.01%
70 12.30 44.83%
Microsoft Jul 2002 Calls--Calcuting the
Implied Volatility
34%
34%
35%
35%
36%
36%
37%
37%
38%
38%
39%
39%
45 50 55 60 65 70 75 80 85
Exercise price
Implied volatility
Microsoft July 2002 Puts--Calculating the
Implied Volatility
40%
41%
42%
43%
44%
45%
46%
47%
45 50 55 60 65 70
Exercise price
Implied volatility
The results are both encouraging and discouraging:
•
The implied volatilities for the calls are pretty close together, as are the implied
volatilities for the puts. This is good news.
PFE Chapter 24, Black-Scholes and binomial page 21
• On the other hand the implied volatilities for the puts are uniformly larger than the
implied volatilities for the calls. This is strange, since in the Black-Scholes formulation,
the implied volatility refers to the volatility of the stock’s return and hence has nothing to
do with whether we’re discussing a put or a call option.
•
On the third hand,
6
the actual difference between the implied volatilities for the calls and
the puts is not that great (only about 6%).
This is not the place to summarize the vast finance literature on implied volatilities. For
our purposes, the Black-Scholes model works pretty well. That’s enough!
Summary
This chapter has given you a quick and hopefully practical insight into how to use the
Black-Scholes model. Of all the financial models developed in the past 50 years, this model
works best. It is remarkably good at pricing options and is widely used. It is also easy to use,
provided you don’t get too hung up on the details of where the formula comes from (in this
chapter we’ve left these hang-ups behind us, and concentrated exclusively on implementational
details).
6
Harry Truman is reported to have gotten so sick of hearing economists say “On the hand, ... . But on the other
hand, ... ” that he asked his chief of staff to get him a “one-handed economist.” History does not record if he
succeeded. The economist in this section’s bullets has at least 3 hands. Harry Truman would not have liked him.
PFE Chapter 24, Black-Scholes and binomial page 22
Exercises
1. Use the Black-Scholes model to price the following:
•
A call option on a stock whose current price is 50, with exercise price X = 50, T =
0.5, r = 10%,
σ
= 25%.
•
A put option with the same parameters.
2. Use the data from exercise 1 and
Data|Table to produce graphs that show:
•
The sensitivity of the Black-Scholes call price to changes in the initial stock price S.
•
The sensitivity of the Black-Scholes put price to changes in
σ
.
•
The sensitivity of the Black-Scholes call price to changes in the time to maturity T.
•
The sensitivity of the Black-Scholes call price to changes in the interest rate r.
•
The sensitivity of the put price to changes in the exercise price X.
3. Produce a graph comparing a call’s intrinsic value (defined as Max(S-X,0) ) and its Black-
Scholes price. From this graph you should be able to deduce that it is never optimal to exercise
early a call priced by the Black-Scholes.
4. Produce a graph comparing a put’s intrinsic value (= Max(X-S,0) ) and its Black-Scholes
price. From this graph you should be able to deduce that it is may be optimal to exercise early a
put priced by the Black-Scholes formula.
PFE Chapter 24, Black-Scholes and binomial page 23
6. Use the Excel Solver to find the stock price for which there is the maximum difference
between the Black-Scholes call option price and the option’s intrinsic value. Use the following
values: S = 45, X = 45, T = 1,
σ
= 40%, r = 8%.
Repeat the MSFT exercise in the text for the March 2002 options:
9. Note that you can use the Black-Scholes formula to calculate the call option premium as a
percentage of the exercise price in terms of S/X:
() () () ()
()
12 12
2
1
21
ln( / ) 2
rT rT
CS
C SNd Xe Nd Nd e Nd
XX
where
SX r T
d
T
dd T
σ
σ
σ
−−
=− ⇒= −
++
=
=−
Implement this in a spreadsheet.