# How to lower the standard deviation in a Monte Carlo Simulation [closed]

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, SmallChessOct 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.
• 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. – Simplex1 Sep 27 '18 at 6:10