Principles of Finance with Excel (Основы финансов c Excel)

Подождите немного. Документ загружается.

PFE Chapter 24, Black-Scholes and binomial page 4

Excel functions used

• Exp

• Ln

• Stdevp

• Varp

• Data Table

24.1. The Black-Scholes Model

In a famous paper published in 1973, Fisher Black and Myron Scholes proved a formula

for pricing European call and put options on non-dividend-paying stocks. Their model is

probably the most famous model of modern finance. The Black-Scholes model uses the

following formula to price calls on the stock:

(

)

(

)

()

CSNd XeNd

where

SX r T

dd T

=−

−

ln( / )

Here C denotes the price of a call, S is the current price of the underlying stock, X is the

exercise price of the call, T is the call’s time to exercise, r is the interest rate, and

is the

standard deviation of the logarithm of the stock’s return. N( ) denotes a value of the standard

normal distribution. It is assumed that the stock will pay no dividends before date T.

The spreadsheet below prices an option on a stock whose current price is S=100. The

option’s exercise price is X=90 and its time to maturity is T=0.5 (one-half year). The interest

PFE Chapter 24, Black-Scholes and binomial page 5

rate is r=4%, and sigma (

, the stock’s volatility—a measure of the stock’s riskiness; more

about this later) is

= 35%.

AB C

The Black-Scholes Option-Pricing Formula

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

0.6303 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))

0.3828

<-- d

-sigma*SQRT(T)

N(d

)

0.7357

<-- Uses formula NormSDist(d

)

N(d

)

0.6491

<-- Uses formula NormSDist(d

)

Call price 16.32

<-- S*N(d

)-X*exp(-r*T)*N(d

)

Put price 4.53 <-- call price - S + X*Exp(-r*T): by Put-Call parity

4.53

<-- X*exp(-r*T)*N(-d

) - S*N(-d

): direct formula

By the put-call parity theorem (see Chapter 22), a put with the same exercise date T and

exercise price X written on the same stock will have price

−

. We’ve used this

formula in cell B16. Cell B17 includes another version of put pricing—a direct formula which

follows from the Black-Scholes formula.

24.2. What do the Black-Scholes parameters mean? How to calculate them?

The Black-Scholes option pricing model depends on 5 parameters:

•

S, the current price of the stock. By this we always mean the stock price on the date

we’re calculating the option price.

•

X, the exercise price of the option (this is also called the “strike price”).

PFE Chapter 24, Black-Scholes and binomial page 6

• T, the time to the option’s expiration. In the Black-Scholes formula, this is always given

in annual terms—meaning: an option with 3 months to expiration has T = 0.25, an

option with 51 days until expiration has

0.1397

365

T == ).

•

r, the risk-free interest rate. This is also given in annual terms. Meaning: If the interest

rate is 6% per year and if an option has T = 0.25, then we write r=6% in the Black-

Scholes formula. The Black-Scholes formula assumes that there is only one risk-free

rate, whereas in reality there are many rates. In actual calculations we usually use the

Treasury bill rate for a maturity which is closest to the option maturity.

Here’s an example: The table below (from Yahoo) shows the annualized U.S.

Treasury bill rates on 8 February 2002. If we were valuing an option with T = 0.125

(i.e., with maturity of 1 month), we would take r to be the 3-month Treasury bill rate

(1.66%). If we were valuing an option with maturity T= 1, then we would take some rate

intermediate between the 6-month yield of 1.76% and the 2-year yield of 2.99%.

Two notes: It turns out that the Black-Scholes price is not that sensitive to variations in interest rates (see exercise

???). 2) The Yahoo table is very minimal! In actual practice we would try to find the rate on a zero-coupon bond

of maturity similar to T.

PFE Chapter 24, Black-Scholes and binomial page 7

•

(“sigma”) is a measure of the riskiness of the stock. This is not simple to calculate

(we discuss this in Sections 3 and 4 below). But before we discuss this, here are some

facts to help you get your bearings:

o If the stock is riskless, then

= 0% . A stock is riskless if its future price is

completely predictable.

o An “average” U.S. stock has

of between 10% and 25%

o A risky stock may have a

of as much as 80% or 100 %.

A remarkable fact is that the Black-Scholes option price depends only on the sigma of the

stock and not on the stock’s expected return

24.3. Computing

from stock prices

There are two main ways to compute the sigma: We can either calculate the sigma by

looking at the series of past stock prices. Alternatively, we can calculate the implied sigma by

looking at options prices; this calculation is often called the implied volatility. This section

describes the first method, and the next section describes how to compute the implied volatility.

Below we show the annual prices for Microsoft for the decade from 1991 - 2001.

Column C shows the continuously compounded return for the prices:

continuous

−







. As

you can see, the

computed from this date is

= 36.90%:

PFE Chapter 24, Black-Scholes and binomial page 8

ABC D

MICROSOFT STOCK PRICES--ANNUAL DATA

Date

Closing

stock

price

Return

31-Dec-90 2.7257

31-Dec-91 5.0104 60.88% <-- =LN(B5/B4)

31-Dec-92 5.4062 7.60%

31-Dec-93 5.3203 -1.60%

31-Dec-94 7.4219 33.29%

31-Dec-95 11.5625 44.33%

31-Dec-96 25.5000 79.09%

31-Dec-97 37.2969 38.02%

31-Dec-98 87.5000 85.27%

31-Dec-99 97.8750 11.21%

31-Dec-00 61.0625 -47.18%

31-Dec-01 66.2500 8.15%

Average return 29.01% <-- =AVERAGE(C5:C15)

Return variance 13.61% <-- =VARP(C5:C15)

Return standard deviation 36.90% <-- =STDEVP(C5:C15)

In the world of option pricing it is not usual to compute

from annual data. Most traders

prefer daily, weekly, or monthly data. The use of non-annual data requires some adjustment to

the calculations. We show these adjustments in the example below, where we calculate

Microsoft’s

from monthly data; a discussion of what we did follows the spreadsheet:

PFE Chapter 24, Black-Scholes and binomial page 9

ABCD

Date Close

29-Dec-00 43.3750

31-Jan-01 61.0630 34.20% <-- =LN(B5/B4)

28-Feb-01 59.0000 -3.44% <-- =LN(B6/B5)

30-Mar-01 54.6880 -7.59% <-- =LN(B7/B6)

30-Apr-01 67.7500 21.42%

31-May-01 69.1800 2.09%

29-Jun-01 73.0000 5.37%

31-Jul-01 66.1900 -9.79%

31-Aug-01 57.0500 -14.86%

28-Sep-01 51.1700 -10.88%

31-Oct-01 58.1500 12.79%

30-Nov-01 64.2100 9.91%

31-Dec-01 66.2500 3.13%

Monthly return statistics

Average return 3.53% <-- =AVERAGE(C5:C16)

Return variance 1.91% <-- =VARP(C5:C16)

Return standard deviation 13.81% <-- =STDEVP(C5:C16)

Annualized return statistics

Average return 42.36% <-- =12*C19

Return variance 22.88% <-- =12*C20

Return standard deviation 47.84% <-- =SQRT(C25)

MICROSOFT STOCK PRICES

MONTHLY DATA FOR 2001

The standard deviation required for the Black-Scholes formula is 47.84%--the annualized

standard deviation. Notice that since

annual variance = 12* monthly variance

annual standard deviation = 12* monthly variance

12 * monthly standard deviation=

In general, if we’re calculating from non-annual data:

260

annual standard deviation

12 * monthly standard deviation

* weekly standard deviation

* daily standard deviation

PFE Chapter 24, Black-Scholes and binomial page 10

(The last calculation 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 2. Suppose you have an investment which is worth P

at time t and worth

t+1

one period later. There are two ways to define the return on the investment. The discrete

return is

discrete

=−, and the continuously compounded return is

continuous







. The

example below shows the difference:

ABC

DISCRETE VERSUS CONTINUOUS RETURNS

Computing the returns from prices

100

t+1

120

Discrete return 20.00% <-- =B5/B4-1

Continously-compounded return 18.23% <-- =LN(B5/B4)

Computing the future price from the returns

Annual return, r

12%

Period over which you get the return (in years) 0.25

Initial investment P

100

Future value P

t+1

If r is the annual discrete return

102.8737 <-- =B14*(1+B11)^B12

If r is the annual continuous return

103.0455 <-- =B14*EXP(B11*B12)

PFE Chapter 24, Black-Scholes and binomial page 11

24.4. Calculating the implied volatility 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

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):

ABCDEFG

The Black-Scholes Option-Pricing Formula

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

0.2450 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))

-0.0025

<-- d

-sigma*SQRT(T)

N(d

)

0.5968

<-- Uses formula NormSDist(d

)

N(d

)

0.4990

<-- Uses formula NormSDist(d

)

Call price 3.94

<-- S*N(d

)-X*exp(-r*T)*N(d

)

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 24, Black-Scholes and binomial page 12

And here’s the final result:

ABCDEFG

The Black-Scholes Option-Pricing Formula

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

0.2593 <-- (LN(S/X)+(r+0.5*sigma^2)*T)/(sigma*SQRT(T))

-0.0851

<-- d

-sigma*SQRT(T)

N(d

)

0.6023

<-- Uses formula NormSDist(d

)

N(d

)

0.4661

<-- Uses formula NormSDist(d

)

Call price 5.25

<-- S*N(d

)-X*exp(-r*T)*N(d

)

What’s used in practice—implied

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

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/

24.5. An Excel Black-Scholes function

The spreadsheet which comes with 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:

PFE Chapter 24, Black-Scholes and binomial page 13

AB C D

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)

The function Calloption(stock price, exercise price, time to maturity, interest, sigma)

is a defined macro which is attached to the spreadsheet.

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.

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

As you can see in the spreadsheet, putoption has the same syntax.