Excel
02 Dec 2008
Jun
25
Lognormal Distribution. Part 3: Future Stock Price
by David Harper, CFA, FRM, CIPM
Previously I introduced the lognormal distribution and showed how it describes the price level of a zero-coupon bond. Now I'll use it to characterize the future price level of a stock. We start with the restrictive assumption that periodic stock returns are normally distributed. If that is true, then future stock price levels are log-normally distributed (By definition, a random variable is log-normally distributed if its natural log is normally distributed).
If we are given a stock with expected return (mu) and variance (sigma), the LN(future price/strike price) is approximately a normal distribution:
The normal, as usual, has two parts: mean and variance/standard distribution. Notice the mean in this case is the expected return minus one-half the variance. You might expect the mean to simply be the expected return. However, volatility erodes the expected return. The n-period standard deviation is the familiar square root rule: volatility scales by the square root of time.
Because we have the two parameters required for a normal distribution, we can describe a standard normal random variable. To standardize is merely to convert the normal random variable to a "standard" normal distribution which has a mean of 0 and a standard deviation of 1. So, for example, if the mean is 100 and the standard deviation is 15, we "standardize" a value of 130 by calculating (130-100)/15 = +2. That converts it to standard units: the value of 130 is two standard deviations (15 x 2) above the mean (100).
So, we do the same thing to our distribution above with this formula (see how the mean is being subtracted in the numerator, and the difference in the numerator is divided by the standard deviation?):
Since that is a standard normal variable, we can use the standard normal cumulative distribution function to check for the probability that our future stock price will be less than the strike price. In other words, the P(Price < Strike) = N(z, the standard normal variable). In MS Excel, that is =NORMSDIST(z = standard normal variable).
To see this in action, you can review the EditGrid spreadsheet below. The chart plots the relevant area under the density function. Under all of these (restrictive) assumptions, the probability that the future stock price will exceed the strike price is a function of the current price, the stock's volatility, the stock's expected return, and the time period (term). You can open your own read/write copy here.
Comments
I was looking at your spreadsheet above and was wondering if i could adapt it to the following problem. Lets say the stock currently trades at 40. Its expected return and volatility are 12% and 30% respectively. What is the probability that the stock price will be greater than 80 in two years? Im not sure if you can adapt the strike price to be the same as the expected return price in this situation. But i tried to adapt as such and came out with a Standardized LN(S) of .33959 and the P(future S < X)=63% and P(future S > X)=37%. Im thinking the answer im looking for would be the P(future S > X)=37%. Am i correct in assuming this?
Very helpful
Leave a Comment