Monte Carlo simulation and Random Walk (I)

I have made an implementation in Excel of the simulation of Gaussian Random Walks with Monte Carlo methods and its applications to the forecasting of the stocks price.

File: Stock price simulation -chart

The model follows the equation: $S(t+1)=S(t)(1+r)$, where r, the return has a mean and a standard deviation. The model implements three different types of modelling the stock returns (normal, uniform and lognormal distribution). 

Application 1: Experiment with the values of the standard deviation and see how it affects

Mean= 1%

Standard deviation = 1%

Mean= 1%

Standard deviation = 5%

 

Mean= 1%

Standard deviation = 10%

Mean= 1%

Standard deviation = 30%

As the standard deviation increses, the volatility and the chaotic movement also increases. Also increses the difference between using one type of random walk or another.

Application 2: testing forecasted values with real data (short term)

I downloaded from Yahoo Finance  the historical data of the SP500 from Jan 25, 2013 until April 28, 2014 . I chose to use the weekly close price.

From the weekly close price of all the weeks in 2013 I calculated the weekly mean return (0.413%) and the standard deviation of those returns (1.309%). With that values, I forecasted what could have happened from Jan 3, 2014 until April 28, 2014, plotting agains the real data from that date.

Here you can see two different simulations:

Simulation 1: 

 Simulation 2:

Application 3: testing forecasted values with real data (long term)

Again, I downloaded from Yahoo Finance  historical data of the SP500, but this time I chose to use monthly close price. From January 3, 1989 until December 1, 2008 (19 years) the mean return was 1.24% and the standard deviation 4.28%.  From Jan 2, 2009 until May 1, 2014 I tested the forecasted values against the real data.

Here you can see the result:

Simulation 1:

Simulation 2:

In this case, we can see that there are less forecasting errors. The uniform distribution seems also to be the distribution that fits better to the real data.