Normal distribution of stock's rate of return resulting in a log-normal distribution of stock prices

When we aggregate the returns of the stocks of a market, we can compute its mean return and the corresp7onding standard deviation. Our goal, in this post, is to model the behavior of a set of stocks on a theoretical stock market that has the same statistical indicators. We will use continuous compounding, while calculating stock market returns. As it means that P1 = P0*e^r, where P0 and P1 are stock prices in periods 0 and 1 and r is the rate of return, it follows that r = ln(P1/P0). In the case of stock markets we can assume, that r is normally distributed(see the excel file). This in turn means that stock prices, P1/P0, have a log-normal distribution(see the excel file). If the mean growth of our market is 15% with the standard deviation of 30% - over the simulation's next 250 business days i.e. a year; one day being deltaT = 1/250 = 0.004 - we are going to calculate the stock price for the next day using the following formula. P1 = P0*e^(15%*deltaT+30%*deltaT^0.5*Z), where Z is a variable simulated by us in Excel, with a normal distribution, a mean of 0 and a standard deviation of 1; and deltaT^0.5 is a square root of deltaT, used because price is assumed to be linearly dependent upon the mean and the variance (stdev=var^0.5).

Thus, in this file, we are at first going to simulate 32000 normal distribution numbers with Random Number generator, and calculate the corresponding log-normal distribution values from it. We will analyze the distributions and graph out the corresponding results, affirming the presence of functions expected. Next we are going to simulate 40 theoretical stocks on a market, all having the initial price of 30, the mean growth at 20%, and 30% as the standard deviation - over a two year period. We will graph out the dynamics of stocks, which will result in a diffusion of stock prices.

The idea of this Excel file is from  Simon Benninga's book Financial Modeling.

One further note on this topic would be, that it has been proven that as time of such simulation approaches infinity, the average stock price also approaches infinity, but the probability of default approaches 1. :-)