S

Hello

I am trying to model future growth of an investment using only past performance data.

I have historical investment values (actually a particular stock market index) for 556 consecutive months. The index starts at $100 in month 1 and by the end of the 556th month has grown to $5,731.439. After computing the % growth for each of the 555 months of growth, In Excel I calculate the following for Geomtric Mean and Standard Deviation of the growth each month:

geoMean = (5731.439/100)^(1/555) = 1.007321359

geoSD = 1.059142531

I am pretty certain that these are accurate.

The problem comes when i use these variables to try and forecast future growth. In Excel VBA i run 5000 simulations, each simulation generating 555 random monthly growth amounts. Each growth amount is generated using the Worksheet Function "LogNorm_Inv" for a log-normal distribution as follows:

monthGrowth = WorksheetFunction.LogNorm_Inv(Rnd, LN(geoMean), LN(geoSD))

Where Rnd generates a random number between 0 and 1, and LN takes the ln of the argument passed. The result is numbers such as

1.027414854

0.975869474

1.014909714

0.891361354

0.922309149

...and so on. 555 of them for each simulation, 5000 simulations in total.

The problem is when i multiply all 555 growth amounts together then multiply by the base amount of $100 in each simulation, the resulting growth over 555 months is HUGELY variable. In the original data i used to generate the geometric mean and standard deviation, the ending value is $5,731.439 after 555 months of growth. But my simulation results are all over the place. In 5000 simulations, the average ending value was $13,774.788, with ending values as high as $682,230.800 and as low as $22.855. Strangely, the median of these 5000 results is very close to spot on each time i run the simulations, very close to the original value of $5,731.439

What am i doing wrong? I want to be able to use the Geometric mean and Standard deviation to forecast future growth. If i can run 5000 simulations each time, i can then look at the probability of each scenario (thats called Monte Carlo Analysis right?)

Thanks in advance, G

I am trying to model future growth of an investment using only past performance data.

I have historical investment values (actually a particular stock market index) for 556 consecutive months. The index starts at $100 in month 1 and by the end of the 556th month has grown to $5,731.439. After computing the % growth for each of the 555 months of growth, In Excel I calculate the following for Geomtric Mean and Standard Deviation of the growth each month:

geoMean = (5731.439/100)^(1/555) = 1.007321359

geoSD = 1.059142531

I am pretty certain that these are accurate.

The problem comes when i use these variables to try and forecast future growth. In Excel VBA i run 5000 simulations, each simulation generating 555 random monthly growth amounts. Each growth amount is generated using the Worksheet Function "LogNorm_Inv" for a log-normal distribution as follows:

monthGrowth = WorksheetFunction.LogNorm_Inv(Rnd, LN(geoMean), LN(geoSD))

Where Rnd generates a random number between 0 and 1, and LN takes the ln of the argument passed. The result is numbers such as

1.027414854

0.975869474

1.014909714

0.891361354

0.922309149

...and so on. 555 of them for each simulation, 5000 simulations in total.

The problem is when i multiply all 555 growth amounts together then multiply by the base amount of $100 in each simulation, the resulting growth over 555 months is HUGELY variable. In the original data i used to generate the geometric mean and standard deviation, the ending value is $5,731.439 after 555 months of growth. But my simulation results are all over the place. In 5000 simulations, the average ending value was $13,774.788, with ending values as high as $682,230.800 and as low as $22.855. Strangely, the median of these 5000 results is very close to spot on each time i run the simulations, very close to the original value of $5,731.439

What am i doing wrong? I want to be able to use the Geometric mean and Standard deviation to forecast future growth. If i can run 5000 simulations each time, i can then look at the probability of each scenario (thats called Monte Carlo Analysis right?)

Thanks in advance, G

Last edited by a moderator: