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$


closed as unclear what you're asking by Xi'an, mdewey, kjetil b halvorsen, Michael Chernick, SmallChess Oct 3 '18 at 3:12

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.


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 '18 at 6:00
  • $\begingroup$ @Simplex1 What about lower SD, does that make sense? $\endgroup$ – SmallChess Sep 27 '18 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 '18 at 6:10

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