Introduction
Monte Carlo simulations are currently the most common used techniques to estimate the risks that a Bank or firm might be facing at a given time. A Monte Carlo simulation is no more than a series of predictions for possible future events. Once the simulation has run, (virtually) millions of its results will produce a distribution of outcomes that can be analysed by the Risk Managers and displayed in a distribution chart. There are a multitude of models available that can be used which may vary by industry type.
Older methods included, among others, the famous Risk Heatmaps, which allowed risks to be plotted in a visual Red-Amber-Green (RAG) manner, giving the audience a false sense of control over the potential risks. Heatmaps have inherent flaws and promote cognitive bias, with several deficiencies having been recognised among the experts in the Risk community. Nevertheless, heatmaps are still widely used and I’ve even written an article about how to build a How to Create a Heatmap in Excel (part 1 and part 2).
Note: this doesn’t mean that I actively use Risk heatmaps nor I recommend that you should use these tools, especially if you need to rely on them to make any (informed) decisions. I simply built a tool and published it for public use, by popular demand. After designing this Excel solution, I’ve written a post questioning the Risk community if “Is Anyone Still Using Risk Heatmaps?“.
Stock Portfolio Monte Carlo Simulation in Excel
This video and solution was created by Matt Macarty and includes a straight to the point simple Monte Carlo simulation technique (or Monte Carlo method) that uses the Excel Data Table feature to replicate iterations. This tutorial models what would be a set of annual investments performed in the S&P 500 Market Index.
This solution is 100% based on Excel formulas and therefore there is no requirement of paid add-ins, like, for example, the Palisades Decision Suite or Oracle’s Crystal Ball.
Download this Excel file from https://alphabench.com/data/monte-carlo-simulation-tutorial.html.
