I am trying to simulate a stock's price with a Monte Carlo simulation. I am using this formula in excel: $S_{t+1}=S_t\cdot exp(d\Delta{t}+s\varepsilon \sqrt{\Delta{t}})$, where $d=\bar{x}-\frac{s^2}{2}$, $\Delta{t}=1$ , $\varepsilon=NORMSINV(RAND())$ I understand that that epsilon random value is not related to the rest of the data, but if I use $NORMINV(RAND(),\bar{x},s)$ it gives me a very straight looking line when I graph the simulation.

I have about 472 historical data inputs (472 days), whose last input is the price it was at several months ago so that I can compare extrapolation results with reality. And then I extrapolate that using that formula, the next 5 months, and run it 1000 times and take medians. One of the things that I tabulate is the median Max price that the stock reaches in that 5 months.

I have noticed that whenever I do this simulation, the max price that is predicted, is always a lot higher than the actual max price of the stock in the next 5 months, and along with that, the standard deviation of the simulation is always close to double that of the actual one. I have run this simulation on several stocks.

How do I manipulate that formula for the stock price so that I can reduce the median standard deviation enough so it is not double what it should be? And hopefully, as a consequence of this, the expected max price is not so off.

Note: the mean, standard deviation, etc. are of the lognormal differences of the prices. Eg: $2000, $4000. -> $ln(4000/2000) \approx 0.693$


1 Answer 1


I believe you're running the standard GBM motion formula. Your formula is correct. You can't use NORMINV(RAND(),x¯,s) because it's a Gaussian noise with mean of 0 and SD of 1.

Can you please clarify why you think a plain log-normal assumption should fit the actual market prices well? It shouldn't. Are you trying to validate the market prices against the GBM process? If so, it's meaningless because the market prices are not governed by GBM at all. Whatever data you used to calibrate your GBM wouldn't work.

What about lower the SD of your GBM process?

  • $\begingroup$ I mainly want the standard deviation to fit, so then I can actually work with the formula. The price at this point in time doesn't bother me. Also, how would I go about lowering the SD, if it isn't equal to the SD of the historical data? $\endgroup$
    – Simplex1
    Sep 27, 2018 at 6:00
  • $\begingroup$ @Simplex1 What about lower SD, does that make sense? $\endgroup$
    – SmallChess
    Sep 27, 2018 at 6:01
  • $\begingroup$ Would this be a good idea: multiply the standard deviation in the formula by some 'random' factor $\lambda$ in a specified range, maybe 0<$\lambda$<1, and then when the projection's R^2 value with the real value is sufficient enough, add the random factor to a list. $\endgroup$
    – Simplex1
    Sep 27, 2018 at 6:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.