Simon Benninga. Financial Modelling 3-rd edition

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

498 Chapter 18

The routine that produces randomly distributed standard normal devi-

ates is contained in the eight lines following the word start; this routine

is further explained in Chapter 30.

18.5 Simulating Lognormal Price Paths

We now return to the problem of simulating lognormal price paths that

we started to discuss in section 18.3. We shall try to understand, through

a simulation written in VBA, the meaning of the following sentences:

“The price of a stock today is $25. The price of the stock is distributed

rand2 = 2 * Rnd - 1

S1 = rand1 ^ 2 + rand2 ^ 2

If S1 > 1 Then GoTo start

S2 = Sqr(-2 * Log(S1) / S1)

X1 = rand1 * S2

X2 = rand2 * S2

Return1 = Exp(mean + sigma * X1)

Return2 = Exp(mean + sigma * X2)

Frequency(Int(Return1 / 0.01)) = Frequency _

(Int(Return1 / 0.01)) + 1

Frequency(Int(Return2 / 0.01)) = Frequency _

(Int(Return2 / 0.01)) + 1

Next Index

For Index = 0 To 400

Range(“output”).Cells(Index + 1, 1) =

Frequency(Index) / N

Next Index

Range(“stoptime”) = Time

Range(“elapsed”) = Range(“stoptime”) - _

Range(“starttime”)

Range(“elapsed”).NumberFormat = “hh:mm:ss”

End Sub

499 The Lognormal Distribution

lognormally, with an annual log mean return of 10 percent and an annual

log standard deviation of 20 percent.” We want to know how the price

of the stock might behave on a daily basis throughout the next year.

There are an inﬁ nite number of price paths for the stock. What we will

do is simulate (randomly) one of these paths. If we want another price

path, we can merely rerun the simulation.

There are about 250 business days in a year. Therefore, the daily price

movement of the stock between day t and day t + 1 can be simulation by

setting Δt = 1/250 = 0.004, μ = 10 percent, and σ = 20 percent. If the initial

price of the stock S

= $25, then the price after one day will be

SS tZt Z

tΔ

ΔΔ=∗ + =∗ ∗ + ∗

25 0 15 0 004 0 20 0 004exp[ ] expμσ [. . . . ]

and the price after two days will be

SS Z

0 008 0 004

0 15 0 004 0 20 0 004

[. . . . ]=∗ ∗ +∗exp

and so on. At each step the random normal deviate Z is the uncertain

factor in the price return. Because of this uncertainty, all paths produced

will be different.

Here is a VBA program PricePathSimulation that reproduces a

typical price path:

Sub PricePathSimulation()

Range(“starttime”) = Time

N = Range(“runs”).Value

mean = Range(“mean”)

sigma = Range(“sigma”)

delta_t = 1 / (2 * N)

ReDim price(0 To 2 * N) As Double

price(0) = Range(“initial_price”)

For Index = 1 To N

start:

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

rand1 = 2 * Rnd - 1

500 Chapter 18

The output from this program looks like this on the spreadsheet:

rand2 = 2 * Rnd - 1

S1 = rand1 ^ 2 + rand2 ^ 2

If S1 > 1 Then GoTo start

S2 = Sqr(-2 * Log(S1) / S1)

X1 = rand1 * S2

X2 = rand2 * S2

price(2 * Index - 1) = price(2 * Index - 2) _

* Exp(mean * delta_t + _

sigma * Sqr(delta_t) * X1)

price(2 * Index) = price(2 * Index - 1) * _

Exp(mean * delta_t + _

sigma * Sqr(delta_t) * X2)

Next Index

For Index = 0 To 2 * N

Range(“output”).Cells(Index + 1, 1) = Index

Range(“output”).Cells(Index + 1, 2) =

price(Index)

Next Index

Range(“stoptime”) = Time

Range(“elapsed”) = Range(“stoptime”) - _

Range(“starttime”)

Range(“elapsed”).NumberFormat = “hh:mm:ss”

End Sub

501 The Lognormal Distribution

We can modify the program slightly to produce many lognormal price

paths (see exercise 1). The output from this program looks like this:

ABCDEF G H I

Day

Stock

price

0 30.00 Runs 125

1 29.99 Starttime 2:40:35

2 30.15 Stoptime 2:40:35

3 30.66 Elapsed 00:00:00

4 30.34

5 29.93 Counter 107

6 29.75

7 29.83

8 29.94

9 29.60

10 29.37

11 29.56

12 29.35

13 28.53

14 28.24

15 28.09

16 27.63

17 26.49

18 26.47

19 25.78

20 25.44

Note

: You may have to rescale the y-axis on the graph

21 24.90 for some runs of the simulation.

22 24.84

23 25.51

Simulating Lognormal Price Paths with VBA

Press [Ctrl]+A to operate macro

Lognormal Price Simulation

Parameters: Mean = 20.00%, Sigma = 30.00%

0 50 100 150 200 250

Day

Stock price ($)

Ten Lognormal Simulated Price Paths

0 25 50 75 100 125 150 175 200 225 225

Day

Stock price

502 Chapter 18

As you can see, on average the price of the asset increases over time,

as does the variance of the returns. These results accord with properties

4 and 5 of stock prices in section 18.2.1—we expect both the return on

an asset and the uncertainty associated with this return to increase over

time.

18.6 Technical Analysis

Security analysts are divided into “fundamentalists” and “technicians.”

This division has nothing to do with their outlook on the Creator of the

Universe, but rather with the way they regard stock prices. Fundamental-

ists believe that the value of a stock is ultimately determined by underly-

ing economic variables. Thus, when a fundamentalist analyzes a company,

she will look at its earnings, its debt/equity ratio, its markets, and so

forth.

Technicians, in contrast, think that stock prices are determined by pat-

terns. They believe that, by examining the pattern of past prices of a

stock, they can predict (or at least make sensible statements about) the

stock’s future prices. A technician may tell you that “we’re currently in

a head-and-shoulders pattern,” by which he means that a graph of the

stock price looks like the following ﬁ gure:

Lognormal Price Simulation

Technical Analysis

0 50 100 150 200 250 300

Day

Stock price ($)

Head

Shoulders

Floor

503 The Lognormal Distribution

Other terms used by technicians include “ﬂ oors” (there’s one in the

graph), “rebound levels,” and “pennants.”

The orthodox (some would say ivory-tower) view of technical analysis

is that it is worthless. A basic theory of ﬁ nance says that markets efﬁ -

ciently incorporate the information known about the securities traded

on them. There are several versions of this theory; one of them, the weak

efﬁ cient markets hypothesis, says that at the very least all information

about past prices is incorporated into the current price. The weak efﬁ -

cient markets hypothesis means that technical analysis cannot make

predictions about futures prices, since technical analysis is based solely

on past price information.

Nevertheless, a lot of people believe in technical analysis. (This belief

in itself may give technical analysis some validity.) The simulations we

are running in this chapter will allow us to generate a myriad of patterns

which, when analyzed, will yield “good” predictions of future prices. For

example, in the preceding ﬁ gure it appears that $24 is a ﬂ oor for the stock

price, since it never goes any lower. A perspicacious analyst can detect

a clear head-and-shoulders pattern between days 40 and 100. There

appears to be a ceiling of $35. Thus a technician might predict that the

stock price will stay below $35 unless it rises above that level. (If you are

going to be a technician, you have to learn to say these things with a

straight face.)

18.7 Calculating the Parameters of the Lognormal Distribution from Stock

Prices

The main purpose of this section is to show you how stock price data

can be used to compute the μ and σ needed in the lognormal simulations

(and—in the next chapter—the σ needed as an input to the Black-

Scholes formula). Before doing so, note that the mean and variance of

the logarithm of the stock return over an interval Δt are

EtZt t

⎛

⎝

⎜

⎞

⎠

⎟

⎡

⎣

⎢

⎤

⎦

⎥

=+ =

ΔΔΔ[]μσ μ

7. A good compendium of technical analysis nomenclature can be found at http://www.

sstfutures.com/futures_chart_patterns.htm.

8. For a discussion of this point, see Chapter 13 of Brealey, Myers, Allen (2005); for a

more advanced treatment, see Chapters 10–11 of Copeland, Weston, Shastri (2003).

504 Chapter 18

Var ln Var

tZt t

⎛

⎝

⎜

⎞

⎠

⎟

⎡

⎣

⎢

⎤

⎦

⎥

=+=

ΔΔΔ[]μσ σ

These expressions indicate that both the expected log return and the

variance of the log return are linear in time.

Now suppose we want to estimate the lognormal μ and σ from data

on historical prices. It follows that

μσ

(

)

⎡

⎣

⎤

⎦

(

)

⎡

⎣

⎤

⎦

Mean ln

Var ln

ΔΔ

To make things speciﬁ c, the following spreadsheet gives monthly prices

for a particular stock. From these prices we calculate the log returns and

the annualized mean and standard deviation. Note that we have used the

function Stdevp to calculate σ; it is assumed that the data represent the

actual distribution.

ABC D

Month Price

Monthly

return

31-Aug-05 30.74

1-Sep-05 33.97 9.99% <-- =LN(B4/B3)

3-Oct-05 29.3 -14.79% <-- =LN(B5/B4)

1-Nov-05 31.62 7.62% <-- =LN(B6/B5)

1-Dec-05 30.78 -2.69%

3-Jan-06 39.55 25.07%

1-Feb-06 33.86 -15.53%

1-Mar-06 36.35 7.10%

3-Apr-06 38.91 6.81%

1-May-06 37.21 -4.47%

1-Jun-06 37.02 -0.51%

3-Jul-06 33.29 -10.62%

1-Aug-06 32.62 -2.03%

1-Sep-06 28.45 -13.68%

2-Oct-06 28.78 1.15%

Monthly average -0.47% <-- =AVERAGE(C4:C17)

Monthly standard deviation 10.93% <-- =STDEVP(C4:C17)

Annual average -5.65% <-- =C19*12

Annual standard deviation 37.87% <-- =C20*SQRT(12)

Calculating Lognormal Mean and Sigma from Stock

Price Data for Halliburton Corporation

505 The Lognormal Distribution

Note that the annual average log return is 12 times the monthly average

log return, whereas the annual standard deviation is

the monthly

standard deviation. In general, if the return data are generated for n

periods per year, then

Mean Mean ,

Annual return Periodic return Annual return Per

=⋅ = ⋅nnσσ

iiodic return

Of course, this is not the only way to calculate the parameters of the

lognormal distribution. We should mention at least two other methods:

•

We can use some other procedure to extrapolate the mean and stan-

dard deviation of future returns from the past history of returns. One

example of this would be to use a moving average.

•

We can use the Black-Scholes formula to ﬁ nd the implied volatility: the

σ of the stock’s log returns that ﬁ ts the price of an option on the stock.

This method is illustrated in section 19.4.

18.8 Summary

The lognormal distribution is one of the foundations of the Black-Scholes

formula for option pricing discussed in the next chapter. In this chapter

we have explored the meaning of lognormality for stock prices. We have

shown how lognormality—the assumption that the returns on an asset

are normally distributed—can be justiﬁ ed visually for the S&P 500 port-

folio. We have also shown how to simulate price paths that are lognor-

mally distributed. Finally, we have shown how to compute the mean and

the standard deviation of a lognormal distribution from the historic

returns of an asset.

Exercises

1. Use NormSInv(Rand()) to produce a simulation of monthly stock prices, as in the

following illustration.

9. The use of NormSInv for this purpose is discussed in Chapter 30, section 3.

506 Chapter 18

EDCBA

Initial stock price 12

Mean return, m 12%

Return sigma, s 35%

21/1=--<%8tatleD

Month

Random

number

Stock

price

210

1 0.3021 12.4963 <-- =C8*EXP($B$3*$B$5+$B$4*SQRT($B$5)*B9)

2 0.9810 13.9371

3 0.1774 14.3318

4 -1.1420 12.8982

5 0.5932 13.8325

6 0.7335 15.0463

7 -1.7615 12.7198

8 0.6826 13.7649

9 0.4070 14.4869

10 -0.7262 13.5974

11 -0.5846 12.9464

12 0.9055 14.3293

SIMULATING A LOGNORMAL PRICE PROCESS

Cell B20 contains formula

=NORMSINV(RAND())

0123456789101112

2. Expand the previous exercise and use NormSInv(Rand()) to produce a simulation

of daily stock prices.

3. Re-create the following spreadsheet. Play with the spreadsheet (each press of F9

will recompute the numbers) to convince yourself that higher σ means a more vola-

tile price path for the stock.

IHGFEDCBA

Initial stock price 20

Mean return, m 12%

Delta t 0.0833 <-- =1/12

Month

Random

number

20% 40% 80%

0000.020000.020000.020

1 0.839571 21.2043 22.2575 24.5233 <-- =E8*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B9)

2 -0.746826 20.5136 20.6237 20.8458 <-- =E9*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B10)

3 -1.875147 18.5937 16.7755 13.6550 <-- =E10*EXP($B$3*$B$4+$E$7*SQRT($B$4)*$B11)

4 -0.302458 18.4555 16.3625 12.8617

5 -0.04641 18.5911 16.4386 12.8525

6 -0.8825 17.8452 14.9952 10.5881

7 0.088868 18.1172 15.3022 10.9163

8 0.847277 19.2167 17.0445 13.4090

9 0.563191 20.0513 18.3726 15.4250

10 -0.130399 20.1010 18.2799 15.1178

11 -1.011589 19.1512 16.4281 12.0885

12 1.352467 20.9146 19.3979 16.6864

Stock price with sigma =

SIMULATING A LOGNORMAL PRICE PROCESS

Cell B20 contains formula

=NORMSINV(RAND())

Effect of Sigma on Lognormal Price Paths

024681012

Sigma = 20%

Sigma = 40%

Sigma = 80%

507 The Lognormal Distribution

4. Write a VBA program that reproduces the lognormal frequency distribution for an

arbitrary number of runs. That is, this program should do the following:

•

Produce N normal random deviates.

•

Produce a lognormal price relative exp[ ]μσΔΔtZt+ for each deviate.

•

Classify each price relative into a set of bins running from 0, 0.1, . . . , 3.

•

Put the frequencies on the spreadsheet and produce a frequency graph such as

the one in section 18.5.

5. Run a few of the lognormal price path simulations. Examine the price pattern for

trends. Find one or more of the following technical patterns:

Support area

Resistance area

Uptrend/downtrend

Head and shoulders

Inverted head and shoulders

Double top/bottom

Rounded top/bottom

Triangle (ascending, symmetrical, descending)

Flag

6. The Excel notebook fm3_problems18.xls contains daily price data for the S&P 500

index and for Abbott Laboratories for the three months April–June 2007. Use this

data to compute the annual average, variance, and standard deviation of the loga-

rithmic returns for the S&P and for Abbott. What is the correlation between the

returns of the S&P 500 and Abbott?