May 09

Monte Carlo simulations. Part 3: Demo

by David Harper, CFA, FRM, CIPM

FRM | Risk |

Previously (MCS, part 1 and MCS, part 2), I explained that a Monte Carlo simulation requires an mathematical assumption about the behavior of something like a stock (or constituent stocks within a portfolio). The simulation conducts many trials using this behavioral model. In the case of the popular Geometric Brownian motion (GBM), our model assumes that stocks "drift" forward plus or minus a random "shock:"

The best way to really understand MCS is to see an example. The EditGrid spreadsheet below is read-only, but you can open your own version here.

The elements are:

  • A set of input assumptions: starting stock price, expected annual return (mu), annual volatility (sigma) and an interval.
  • Forty rows of forty trials (one for each row)
  • Ten columns for each interval (one for each day, or whatever is the interval)
  • The final day price outcomes (day 10) are ranked from highest (#1) to lowest (#40)
  • The 95th-percentile and 99th-percentile values at risk (VaRs) are calculated
  • A chart plots these forty ten-step trial runs

If you recalculate the spreadsheet, a new set of trials are run. You can see that each trial is just a hypothetical 10-day trip into the future. At the end of the 10th day, the outcomes are ranked. In the case of the 99th percentile VaR, we are simply solving for the 5th percentile in that ranking. For forty trials, that is the 38th-ranked outcome. Please let me know if this is helpful in learning MCS or how it might be improved, thanks.

A read/write copy can be opened here. The chart plots the trials, but it can be moved.

EditGrid Spreadsheet by turtle/turtleadmin.

Comments

  1. Be the first to leave a comment!

Leave a Comment