# 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$$

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. Sep 27, 2018 at 6:10