Simon Benninga. Financial Modelling 3-rd edition

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

488 Chapter 18

If we use the Excel function Frequency (see Chapter 35) to do a frequency

distribution of these returns, we see that they look normally distributed:

An Excel Note

In cell G9 we used Sumproduct to count the number of daily returns between

−1 percent and +1 percent. The Boolean formula (C4:C14097>−1%) * (C4:

C14097<1%) produces a column of ones for cells in the interval (−1%, +1%)

and zeros elsewhere. Using Sumproduct to multiply and add the product of

two such columns produces the result we want.

Why didn’t we use Excel’s CountIf function (see Chapter 34) for this

purpose? CountIf can count a “one-directional” sort of the data, but not the

data in an interval. Thus CountIf (C4:C14097,“<1%”) will count the number

of data points less than 1%, but there is no way to use CountIf to count the

data points between −1 percent and +1 percent.

ABC DE F G H IJ K

Date Price Return

3-Jan-50 16.66

Statistics

-5.00% 12

4-Jan-50 16.85 1.13% <-- =LN(B4/B3) Count 14,094 <-- =COUNT(C4:C14097) -4.88% 1

5-Jan-50 16.93 0.47% -4.76% 0

6-Jan-50 16.98 0.29% Maximum daily return 8.71% <-- =MAX(C4:C14097) -4.64% 0

9-Jan-50 17.08 0.59% Minimum daily return -22.90% <-- =MIN(C4:C14097) -4.52% 0

10-Jan-50 17.03 -0.29% -4.40% 2

11-Jan-50 17.09 0.35% Daily returns between -1% and +1% 11436 -4.28% 0

12-Jan-50 16.76 -1.95% 0 -4.16% 2

13-Jan-50 16.67 -0.54% -4.04% 1

16-Jan-50 16.72 0.30% -3.92% 1

17-Jan-50 16.86 0.83% Daily mean return 0.0309% <-- =AVERAGE(C4:C14097) -3.80% 3

18-Jan-50 16.85 -0.06% Daily standard deviation 0.00899 <-- =STDEV(C4:C14097) -3.68% 4

19-Jan-50 16.87 0.12% -3.56% 4

20-Jan-50 16.9 0.18% Annual return 7.78% <-- =G13*252 -3.44% 3

23-Jan-50 16.92 0.12% Annual standard deviation 14.27% <-- =G14*SQRT(252) -3.32% 2

24-Jan-50 16.86 -0.36% -3.20% 1

25-Jan-50 16.74 -0.71% -3.08% 10

26-Jan-50 16.73 -0.06% -2.96% 12

27-Jan-50 16.82 0.54% -2.84% 10

30-Jan-50 17.02 1.18% -2.72% 13

31-Jan-50 17.05 0.18% -2.60% 16

1-Feb-50 17.05 0.00% -2.48% 22

2-Feb-50 17.23 1.05% -2.36% 21

3-Feb-50 17.29 0.35% -2.24% 32

6-Feb-50 17.32 0.17% -2.12% 26

7-Feb-50 17.23 -0.52% -2.00% 34

8-Feb-50 17.21 -0.12% -1.88% 49

9-Feb-50 17.28 0.41% -1.76% 70

10-Feb-50 17.24 -0.23% -1.64% 83

14-Feb-50 17.06 -1.05% -1.52% 111

15-Feb-50 17.06 0.00% -1.40% 133

16-Feb-50 16.99 -0.41% -1.28% 141

17-Feb-50 17.15 0.94% -1.16% 155

20-Feb-50 17.2 0.29% -1.04% 244

21-Feb-50 17.17 -0.17% -0.92% 263

23-Feb-50 17.21 0.23% -0.80% 343

24-Feb-50 17.28 0.41% -0.68% 426

27-Feb-50 17.28 0.00% -0.56% 503

28-Feb-50 17.22 -0.35% -0.44% 597

1-Mar-50 17.24 0.12% -0.32% 702

2-Mar-50 17.23 -0.06% -0.20% 882

3-Mar-50 17.29 0.35% -0.08% 971

6-Mar-50 17.32 0.17% 0.04% 1101

Frequencies: Uses

Excel

Frequency

function

S&P 500 DAILY PRICES AND RETURNS, 1950-2005

Frequencies of S&P 500 Returns, 1950-2005

200

400

600

800

1000

1200

-4.5 4.5-4.0 4.0-3.5 3.5-3.0 3.0-2.5 2.5-2.0 2.0-1.5 1.5-1.0 1.0-0.5 0.50.0

Daily return (%)

489 The Lognormal Distribution

18.2.2 Computing Returns and Their Distribution for a Continuous-Return-

Generating Process

The return statistics in cells G9:G13 assume that the return-generating

process has a normal distribution. Given this assumption, we can compute

the return over a period by taking the natural logarithm of the price

relatives, deﬁ ned as

Price

t−

⎛

⎝

⎜

⎞

⎠

⎟

. Furthermore, if {r

, r

, . . . , r

} is a series

of periodic returns, we can compute the mean, variance, and standard

deviation of the returns as

Periodic mean

Periodic

Use Excel

function

↑

∑

Average



Periodic variance

Periodic

Use

== −

()

↑

∑

σμ

EExcel

functionStdev

 

Assuming that there are n periods per year, we can compute the annual-

ized returns by

Annualized mean

Periodic

= n

Annualized variance

Periodic

= n

Annualized standard deviation =

We can do another interesting exercise on these data. In the follow-

ing graph we plot the mean and standard deviation of each year’s

returns:

2. This subsection anticipates section 18.7.

490 Chapter 18

Over the time period from 1950 to 2005, both the means and standard

deviations vary widely. However, the variation of the standard deviation

is much less than that of the means. Almost 60 percent of the standard

deviations are between 10 and 20 percent:

S&P 500 ONE-YEAR MEAN AND. STANDARD DEVIATION, 1950-2005

Overall mean: 7.72%; average sigma: 11.33%

Ranges — mean: (-35.13%,37.17%); sigma: (5.25%,33.67% )

Standard deviation of mean: 15.69%; of sigma: 5.16%

-40

-30

-20

-10

Mean return (%)

1950

1952

1953

1955

1957

1959

1961

1963

1965

1967

1969

1971

1973

1975

1977

1979

1981

1983

1985

1987

1989

1991

1993

1995

1997

1999

2001

2003

2005

Mean return

Standard deviation

Lower bound Upper bound

10% 20% 58.93%

8% 22% 85.71%

6% 10% 28.57%

20% 34% 10.71%

13% 26% 41.07%

Number of sigmas between

491 The Lognormal Distribution

Compare this graph to the distribution of the means, which is more

spread out:

Distribution of S&P Sigmas (%), 1950–2005

10%

12%

14%

16%

5–6

6–7

7–8

8–9

9–10

11–12

10–11

12–13

13–14

14–15

15–16

16–17

17–18

18–19

19–21

21–22

22–25

25–28

28–34

Distribution of S&P 500 Means (%), 1950–2005

–36–30

–30–24

–24–19

–19–15

–15–14

–14–12

–12–10

–10–6

–6–3

–3–0

0–1

1–2

2–3

3–5

5–7

7–9

9–12

12–14

14–16

16–18

18–20

20–22

22–23

23–24

24–28

28–30

30–33

33–39

10%

492 Chapter 18

As we will see in the next section, the assumption that stock returns

are normally distributed underlies the lognormal distribution.

18.3 Lognormal Price Distributions and Geometric Diffusions

In this section we get a bit more formal and describe what we mean by

a lognormal price distribution. We then relate the lognormal price process

to a geometric diffusion.

Suppose we denote by S

the price at time t of a share of stock. The

lognormal distribution assumes that the natural logarithm of 1 plus the

return from holding a share of stock between time t and time t + Δt is

normally distributed with mean μ and standard deviation σ. Denote the

(uncertain) rate of return over an interval Δt by r˜

Δt

. Then we can write

t + Δt

= S

exp[r˜

Δt

Δt]. In the lognormal distribution, we assume that the rate

of return r˜

Δt

over a short period Δt is normally distributed with mean

μΔt and variance σ

Δt.

Another way of writing this relation is to write the stock price S

t+Δt

time t + Δt in the following way:

tZt

ΔΔexp[ ]μσ

where Z is a standard normal variable (mean = 0, standard deviation =

1).

To see what this assumption means, suppose ﬁ rst that σ = 0. In this

case we have

SS t

tt t+

Δexp[ ]μ

which simply says that the stock price grows at an exponential rate with

certainty. In this case the stock is like a riskless bond that bears interest

rate μ, continuously compounded.

3. The counting of returns and standard deviations in speciﬁ c intervals uses the same trick

employing Sumproduct that was discussed in the Excel box on page 488. Details can

be found in the ﬁ les accompanying this book.

4. If you know about diffusion processes, then the lognormal price process is a geometric

diffusion:

dt dB=+μσ

, where dB is a Wiener process (“white noise”):

dB Z dt=

where Z is a standard random variable.

493 The Lognormal Distribution

Now suppose that σ > 0. In this case, the lognormal assumption

says that, although the tendency is for the stock price to increase, there

is an uncertain element (normally distributed) that must be taken into

account. The best way to think about this process is in terms of a

simulation.

Suppose, for example, that we’re trying to simulate a lognormal price

process in which μ = 15 percent, σ = 30 percent, and Δt = 0.004. Suppose

the price at time 0 is S

= 35. To simulate the possible stock prices at time

Δt we ﬁ rst have to pick (at random) a number Z from a standard normal

distribution.

Suppose that this number is 0.1165. Then the stock price

Δt

at time Δt will be

SS tZt

tΔ

ΔΔ=∗ + =∗ ∗ + ∗ ∗

35 0 15 0 004 0 3 0 1165 0 004

exp exp[][.....]μσ

55 0985.

Of course we could have drawn a different random number. If, for

example, our random number Z had been −0.9102, then we would

have

SS tZt

tΔ

ΔΔ=∗ +

=∗ ∗ −∗− ∗

35 0 15 0 004 0 3 0 9102 0 004

exp

[]

[. . . ( . ) .

μσ

]].= 34 4214

This process is illustrated in the following spreadsheet picture, where we

generated a list of 250 numbers picked from a standard-normal distribu-

tion (the technical nomenclature is “standard normal deviates”).

Each

is an equally likely potential candidate to be Z. Having picked Z for a

particular time interval Δt, the price S

t+Δt

follows.

5. See Chapter 30 for some techniques (using both Excel and VBA) for generating

random numbers.

6. The number of business days in a year is approximately 250. Thus when we deﬁ ne

Δt = 1/250 = 0.004, we are simulating the stock price on a daily basis over the course

of a year.

494 Chapter 18

The spreadsheet uses Tools|Data Analysis|Random Number Genera-

tion to generate a list of 250 standard-normal deviates. The command

looks like this:

495 The Lognormal Distribution

To summarize: In order to simulate the growth of the stock price, when

the price follows a lognormal price distribution,

•

Multiply Δt (the elapsed time interval) by μ (the average rate of

growth). This gives the certain portion of the return.

•

Take a draw Z from a random variable that is standard normal, and

multiply this draw by

Δt

. This step gives the uncertain portion of the

return. (The square root implies that the variance of the stock’s return

is linear in time. See the next section.)

•

Add the two results and exponentiate. The daily return is

exp[ ]μσΔΔtZt+

. If the price on date t is S

, then the price on date t +

1 is

SS tZt

tt+

exp[ ]μσΔΔ

18.4 What Does the Lognormal Distribution Look Like?

We know that the normal distribution produces a bell curve. What about

the lognormal distribution? In the following experiment we simulate

1,000 random end-of-year stock prices. The experiment is a continuation

of the experiment performed in the previous section; since we are simu-

lating end-of-year prices, we set Δt = 1. To perform this experiment,

•

We produce a list of 1,000 normal deviates.

•

We use each normal deviate to produce an end-of-period stock price

SS t Zt S Z t

10 0

1=∗ + =∗ + =exp exp , since[][]μσ μσΔΔ Δ

•

We put the stock prices into bins and produce a histogram.

496 Chapter 18

JIHGFEDCBA

List of 1000 normally

distributed numbers

Lognormal

exp(

⎠ &

√Δ

Bins Frequency

Mean 15% 0.726111011 1.4446

<--

=EXP($B$6*$B$8+$B

$7*D6*SQRT($B$8))

0.00 0

Sigma 30% -0.75691446 0.9258 0.15 0

⎠ t

1 0.054451448 1.1810 0.30 0

1.616244845 1.8868 0.45 0

-0.009147243 1.1587 0.60 8

1.130981673 1.6312 0.75 70

2.550623321 2.4972 0.90 132

-0.230678552 1.0842 1.05 162

1.092166713 1.6123 1.20 181

-2.794113243 0.5025 1.35 139

0.724598976 1.4439 1.50 113

0.433137757 1.3231 1.65 74

-1.047612841 0.8485 1.80 53

1.33371941 1.7335 1.95 25

-1.690195859 0.6997 2.10 16

-2.084398147 0.6217 2.25 10

0.022914719 1.1698 2.40 5

-0.045977231 1.1459

0.344841737 1.2885

-0.001750777 1.1612

1.038474693 1.5865

0.609766175 1.3950

0.356371856 1.2929

2.100914571 2.1821

-0.989200544 0.8635

-0.960726538 0.8709

Table created with the array formula

{=FREQUENCY(E3:E1002,H3:H19)}

The Lognormal Histogram

We used

Tools|DataAnalysis|

RandomNumberGeneration

produce the list of 1,000 normally

distributed random numbers (with

mean =0 and standard deviation =

1) on the right.

Lognormal Frequency Distribution

100

120

140

160

180

200

0.75

0.90

1.05

1.20

1.35

1.50

1.65

1.80

1.95

2.10

2.25

2.40

Frequency

Having produced 1,000 lognormal price relatives

exp[ ]

μσ

ΔΔtZt+

, we

can use the array function Frequency( ) (this function is discussed in

Chapter 35) to put them into bins (cells H3:I19).

When we do this simulation for a large number of points, the resulting

density curve becomes smooth. Here, for example, is the frequency

distribution of 100,000 trials with μ = 10 percent, σ = 20 percent, and

Δt = 1:

Here’s what the spreadsheet for this experiment looks like:

497 The Lognormal Distribution

The VBA program that produced this output follows:

ABCDE F

Runs 1,000,000

Mean 10% 0.01 0

Sigma 30% 0.02 0

0.03 0

Starttime 10:10:19 0.04 0

Stoptime 10:10:23 0.05 0

Elapsed 00:00:04 0.06 0

0.07 0

0.08 0

0.09 0

0.1 0

0.11 0

0.12 0

0.13 0

0.14 0

0.15 0

0.16 0

0.17 0

0.18 0

0.19 0

0.2 0

0.21 0

0.22 0

0.23 0

0.24 0

0.25 0.000001

0.26 0.000002

0.27 0.000001

0.28 0.000003

0.29 0.000008

0.3 0.000013

0.31 0.000008

0.32 0.000023

0.33 0.000027

SIMULATION OF LOGNORMAL RETURNS

To run the macro, press [Ctrl]+a

Simulation of Lognormal Returns

0.005

0.01

0.015

0.02

0.025

0.03

0.2

0.4

0.6

0.8

1.2

1.4

1.6

1.8

2.2

2.4

2.6

2.8

3.2

3.4

Frequency

‘Simulating the lognormal distribution

‘Note that I take delta = 1!

Sub RandomNumberSimulation()

Application.ScreenUpdating = False

Range(“starttime”) = Time

N = Range(“runs”).Value

mean = Range(“mean”)

sigma = Range(“sigma”)

ReDim Frequency(0 To 1000) As Integer

For Index = 1 To N

start:

Static rand1, rand2, S1, S2, X1, X2

rand1 = 2 * Rnd - 1