Monte Carlo Simulation (Geometric Brownian Motion) In Excel

Click to Download Workbook: Monte Carlo Simulator (Brownian Motion)

This workbook utilizes a Geometric Brownian Motion in order to conduct a Monte Carlo Simulation in order to stochastically model stock prices for a given asset. Essentially all we need in order to carry out this simulation is the daily volatility for the asset and the daily drift. The drift component is the deterministic component in our stochastic model, meaning it is a direct function of the expected return. We are expecting the stock price to drift a certain way either up or down depending if the expected return was positive or negative.

In order to conduct this simulation, we will need to gather stock prices for an asset utilizing the Google Finance API. From here, we can calculate the daily returns for the stock (log or continuous) over a given time frame. This is nice as we won’t need any annualized expected return or annualized volatility inputs since we already have the data and necessary calculations. Once we have our volatility (daily) and average daily return, we can compute the daily drift. This is simply the average return subtracted by one half of the variance.

In the simulation, we are assuming that the next continuously compounded price will be equal to the drift (deterministic component) + the volatility multiplied by a random variable (nondeterministic component). This will allow us to model the prices of the asset stochastically.

1.) To compute future stock prices, we will need to use some sort of a random function in excel. In this case, we will use NORMSINV(RAND()).

2.) Next, we compute the future log return by adding the drift and volatility together and multiply it by our random number. This will add a shock to our constant drift due to the fact that the volatility is being randomized.

3.) After we can multiply the last price by the exponent of log return we calculated prior.

The equation used within Excel can be seen below:

After this is done, we can autofill to the desired number of days selected by the user and graph the entire series. Hitting the F9 key on your keyboard will continuously update the chart as well.

This workbook is fully automated. Simply fill out the cells highlighted in orange and let VBA do the rest.

About the author

programmingforfinance

Hi, I'm Frank. I have a passion for coding and extend it primarily within the realm of Finance.

View all posts

2 Comments

  • Dear Frank,
    Let me first congratulate you on your great site and contents. I cannot thank you enough for sharing this invaluable resource with us; I’ve learned a whole lot from your site and I am greatful for that.

    In testing “Monte Carlo Simulator (Brownian Motion)” file, I repeatedly get the error message “Invalid Entry” even though the ticker I am using is still the original Apple ticker (“AAPL”) and also the date ranges are the original!? I was merely testing the VBA routine but unfortunately to no avail.

    Would greatly appreciate if you could shed some light on what the problem may be.

    Many thanks in advance.

    Respectfully,
    Michael

Leave a Reply

Your email address will not be published. Required fields are marked *