Using the Lognormal Random Variable to Model Stock Prices

Using The Lognormal Random Variable to Model Stock Prices

In reality, stock prices can assume any non-negative value. In a binomial tree, of course, only a finite number of stock prices are possible. The Lognormal or Geometric Brownian Motion random variable is often used to model the evolution of stock prices.

The Lognormal model for asset value (or stock price) assumes that in a small time t the stock price changes by an amount that is normally distributed with

Mean = St

Here S = current stock price.

 may be thought of as the instantaneous rate of return on the stock. By the way, this model leads to really "jumpy" changes in stock prices (like real life). This is because during a small period of time the standard deviation of the stock's movement will greatly exceed the mean of a stock's movement. This follows because for small t, will be much larger than t. This is consistent with reality.

For example, per day Microsoft has in the recent past had mean growth per day = .40/252 = .0016 and  per day =(note .03>.016!). σ is roughly speaking, a measure of the percentage volatility in the annual return of a stock. As we will see later, Microsoft has a volatility of 47%, AOL 65%, and Amazon.COM in 1999 had a volatility of 120%! The volatility of a stock or project is crucial to a real options calculation.

We use the following equation to simulate future stock prices in EXCEL.

. (1)

Here S0 = Today's stock price, μ = instantaneous rate of return on stock, and σ = Annual volatility of stock (standard deviation of stock price changes over a small unit of time on an annualized basis). The =NORMSINV(RAND()) generates a sample from a standard normal (mean 0 and sigma 1) random variable.

Remarks

Taking the logarithm of both sides of (1) shows us that we could simulate

Ln( St )using the fact that

(2)

This shows than Ln (St) is normally distributed. This explains the name lognormal distribution.

If our stock is growing instantaneously at rate , then why do (1) and (2) seem to indicate that we are growing at a rate of  - .52? The reason for this is that over time increased volatility reduces the mean growth rate of a stock. To see this consider two stocks:
Stock 1 always yields 10% a year.
Stock 2 doubles during half of all years and loses 50% of its value during half of all years.

On average Stock 1 grows by 10% per year and Stock 2 grows by 25% per year. Yet over time Stock 2 will certainly end up lower than Stock 1 due to its large volatility or variability.

Simulating the Lognormal Random Variable

In file lognormal.xls we simulate for 5 years quarterly Microsoft stock prices under the assumption that  = .30 and  = .38 and that today’s stock price is $100. See Figure 1.

Figure 1

Step 1: In F7:F26 we generate random numbers used in (1) to create each period’s stosk price. We copy from F7 to F8:F26 the formula

=RAND().

Step 2: In E7:E26 we generate the Microsoft stock price for each of the next 20 quarters using (1). We copy from E7 to E8:E26 the formula

=E6*EXP((mu-0.5*sigma^2)*0.25+sigma*SQRT(0.25)*NORMSINV(F7)).

Step 3: To generate, say, 10000 realizations of Microsoft’s stock price in 5 years we use a data table. Enter the numbers 1-10000 in J10:J10009. In cell K9 record the simulated stock price in 5 years with the formula

=E26.

Now select the table range J9:K10009 and select the DATA Table option. Select any blank cell as your Column Input cell and you will see 1000 different realizations of Microsoft’s stock price. You may have to hit F9 to get the table to recalculate. Also checking TOOLS OPTIONS CALCULATIONS AUTOMATIC EXCEPT FOR TABLES will ensure that data table will only recalculate when you hit F9.

Step 4: After entering a .05 and .95 in cells L2 and L3 we can obtain the 5th percentile and 95th percentile of Microsoft’s stock prices in 5 years.. Just copy from K2 to K2:K3 the formula

= PERCENTILE($K$10:$K$10009,L2).

As shown in Figure 2, there is (approximately) a 5% chance MSFT stock in 5 years will be worth $71.07 or less and a 5% chance that the MSFT price in five years is $1252.92 or more. Thus there is a lot of variability in where MSFT will end up. Don’t forget that we actually do not know , so there is actually more volatility than our calculations indicate.

In cells L4 and L5 we compute the mean and standard deviation of the MSFT 5 year price with the formulas

= AVERAGE(K10:K10009) and

=STDEV(K10:K10009).

Thus we find our best estimate of the average MSFT price in 5 years to equal $459 and our best estimate of the standard deviation is $459.