Principles of Finance with Excel (Основы финансов c Excel)
Подождите немного. Документ загружается.
PFE Chapter 25, The Black-Scholes formula page 10
(The use of 260 in calculating the annualized
σ
from weekly data may be a bit confusing: Since
there are 52 weeks per year and 5 business days per week, many traders assume that there are
260 business days per year. However, others use 250 and 365.)
Continuous versus discrete returns—a reminder
The Black-Scholes formula uses continuously compounded returns, whereas in most of
this book we use discretely compounded returns. We discussed the difference between these two
concepts in Chapter 6. Suppose you have an investment which is worth P
t
at time t and worth
P
t+1
one period later. There are two ways to define the return on the investment. The discrete
return is
1
1
discrete
t
t
t
P
r
P
+
=−, and the continuously compounded return is
1
ln
continuous
t
t
t
P
r
P
+
⎛⎞
=
⎜⎟
⎝⎠
. The
example below shows the difference:
1
2
3
4
5
6
7
ABC
Computing the returns from prices
P
t
100
P
t+1
120
Discrete return 20.00% <-- =B4/B3-1
Continously-compounded return 18.23% <-- =LN(B4/B3)
DISCRETE VERSUS CONTINUOUS RETURNS
25.4. Implied volatility: Calculating
σ
from option prices
In the previous section we computed the annualized standard deviation of returns
σ
from
historical stock prices. In this section we compute
σ
from option prices.
When we calculate the implied volatility from option prices, we use the Black-Scholes
formula to find the
σ
which gives a specific options price. Suppose, for example, that a share of
PFE Chapter 25, The Black-Scholes formula page 11
ABC Corp. is currently selling for $35, and that a 6-month at-the-money call option on ABC
Corp. is selling for $12. Suppose the interest rate is 6%. The spreadsheet below shows that
σ
must be greater than 35% (since the call prices increases with
σ
, and since
σ
= 35% gives a call
price of $3.94, we’ll have to make
σ
larger to get a call price of $5.25):
1
2
3
4
5
6
7
8
9
10
11
12
13
14
AB C
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
Sigma 35.00% Stock volatility
d
1
0.2450 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))
d
2
-0.0025
<-- d
1
-sigma*SQRT(T)
N(d
1
)
0.5968
<-- Uses formula NormSDist(d
1
)
N(d
2
)
0.4990
<-- Uses formula NormSDist(d
2
)
Call price 3.94
<-- S*N(d
1
)-X*exp(-r*T)*N(d
2
)
The Black-Scholes Option-Pricing Formula
Using
Goal Seek, we can compute the
σ
which gives the market price; it turns out to be
σ
= 48.71%. Here’s the Goal Seek dialog box:
PFE Chapter 25, The Black-Scholes formula page 12
And here’s the final result:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
AB C
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
Sigma 48.71% Stock volatility
d
1
0.2593 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))
d
2
-0.0851
<-- d
1
-sigma*SQRT(T)
N(d
1
)
0.6023
<-- Uses formula NormSDist(d
1
)
N(d
2
)
0.4661
<-- Uses formula NormSDist(d
2
)
Call price 5.25
<-- S*N(d
1
)-X*exp(-r*T)*N(d
2
)
The Black-Scholes Option-Pricing Formula
What’s used in practice—implied
σ
or
σ
from historical prices?
The answer is a bit of both. Smart traders compare the implied volatility with the
historical volatility and try to form estimates of what the stock volatility actually is. There are
PFE Chapter 25, The Black-Scholes formula page 13
whole websites devoted to this subject, and lots of proprietary software. Our own favorite (and,
as of the writing of this book, still free) website is Option Metrics (http://www.impliedvol.com/
).
25.5. An Excel Black-Scholes function
The spreadsheet pfe_chap25.xls which accompanies this chapter includes two Excel
functions which compute the Black-Scholes call and put prices. These functions are not part of
the original Excel package; they have been defined by the author. Here’s an example of how to
use them:
1
2
3
4
5
6
7
8
9
10
AB C
BLACK-SCHOLES OPTION FUNCTIONS
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(B3,B4,B5,B6,B7)
Put price 4.53 <-- =putoption(B3,B4,B5,B6,B7)
The functions in this spreadsheet--
Calloption
and
Putoption
--were defined by the author.
The function
Calloption(stock price, exercise price, time to maturity, interest, sigma)
is a defined macro which is attached to the spreadsheet.
3
When you first open the spreadsheet
Excel will display the following message, which asks if you really want to open this macro. In
this case the correct answer is
Enable macros.
3
As you can see in the spreadsheet, putoption has the same format for the variables.
PFE Chapter 25, The Black-Scholes formula page 14
An implied volatility function
The spreadsheet also comes with two functions which compute the implied volatility for
a call and a put option. The function
CallVolatility(stock price, exercise price, option
maturity, interest rate, target) calculates the
σ
which gives the Black-Scholes price given the
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
AB C
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(B3,B4,B5,B6,B7)
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(B12,B13,B14,B15,B16)
TWO IMPLIED VOLATILITY FUNCTIONS
4
In the spirit of this chapter, we do not explain how these functions work. For details see my book Financial
Modeling.
PFE Chapter 25, The Black-Scholes formula page 15
25.6. Doing sensitivity analysis on the Black-Scholes formula
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 S
0
The following Data|Table (see Chapter 000) shows the sensitivity of the Black-Scholes
call value to the current stock price S
0
. It compares the Black-Scholes call value to the call’s
intrinsic value max(S
0
- X,0) .
PFE Chapter 25, The Black-Scholes formula 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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
AB C DEFG
S
0
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.3155 <-- =calloption(B2,B3,B4,B5,B6)
Put price 4.5333 <-- =putoption(B2,B3,B4,B5,B6)
Stock price at time 0, S
0
Black-
Scholes
price
Intrinsic
value
16.32 10.00
65 0.97 0.00
70 1.82 0.00
75 3.08 0.00
80 4.81 0.00
85 7.02 0.00
90 9.70 0.00
95 12.81 5.00
100 16.32 10.00
105 20.15 15.00
110 24.26 20.00
115 28.58 25.00
120 33.08 30.00
125 37.71 35.00
130 42.44 40.00
135 47.25 45.00
140 52.11 50.00
Black-Scholes Price Sensitivity to S
0
Comparing the Black-Scholes Option Price (curved
line) to the Option Intrinsic Value when the Stock
Price S
0
is Varied
-
10
20
30
40
50
60
65 75 85 95 105 115 125 135
Stock price at date 0, S
0
Black-Scholes
price
Intrinsic
value
This cell is part of the
data table header. It
contains the formula =B8.
This cell is part of the data table header. It
contains the formula =Max(B2-B3,0); this
is the option's intrinsic value.
The option’s intrinsic value
(
)
0
,0Max S X− shows what it would be worth if exercised
immediately. The option’s Black-Scholes price shows what the option would be worth on the
PFE Chapter 25, The Black-Scholes formula page 17
open market. Notice that the Black-Scholes price for the call option is always greater than the
intrinsic value—it is not worthwhile early-exercising the call option.
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 25, The Black-Scholes formula page 18
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
AB C D E FG
S
0
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
Stock price
BS price,
sigma =
20%
BS price,
sigma = 50%
13.15 19.91
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
BLACK-SCHOLES SENSITIVITY ON SIGM
A
This cell is part of the data table
header; it contains the formula
=calloption(B2,B3,B4,B5,20%).
This cell is part of the data table header.
It contains the formula
=calloption(B2,B3,B4,B5,50%).
Black-Scholes Options Price for Two Sigmas
Higher Sigma gives a Higher BS Option Price
0
10
20
30
40
50
60
70
80
0 102030405060708090100110120130140150160
Stock price, S
0
BS price,
sigma = 20%
BS price,
sigma = 50%
The higher the stock’s sigma
σ
, the higher the Black-Scholes option price.
PFE Chapter 25, The Black-Scholes formula page 19
25.7. Does the Black-Scholes model work? Applying it to Microsoft options
In this section we do two experiments to examine whether and how well the Black-
Scholes model works. 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
on 19 July 2002.
•
We calculate the Black-Scholes price of these options and compare it to the actual market
price.
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.