An example of a Monte Carlo simulation risk analysis model for fund modeling

Technical difficulty: 2

Techniques used: Monte Carlo simulation in Excel

Model description

When we first start thinking about risk, it is quite natural to want to convert the impact of a risk to a single number. For example, we might consider that there is a 20% chance of losing a contract, which would result in a loss of income of $100,000. Put together, a person might reason that to be a risk of some $20,000 (i.e. 20% * $100,000). This $20,000 figure is known as the 'expected value' of the variable. It is the probability weighted average of all possible outcomes. So, the two outcomes are $100,000 with 20% probability and $0 with 80% probability:

Mean risk (expected value) = 0.2*$100,000 + 0.8*$0 = 20,000

The graph below shows the probability distribution for this risk and the position of the expected value.


Distribution for risk with 20% probability and $100,000 impact.

Calculating the expected values of risks might also seem a reasonable and simple method to compare risks. For example, in the following table, risks A to J are ranked in descending order of expected cost: If a loss of $500,000 or more would ruin your company, you may well rank the risks differently: risks C, D, I and, to a lesser extent, J pose a survival threat on your company. Note also that you may value risk C as no more severe than risk D because if either of them occur your company has gone bust.

On the other hand, if risk A occurs, giving you a loss of $400k, you are precariously close to ruin: it would just take any of the risks except F and H to occur (unless they both occurred) and you've gone bust.

Looking at the sum of the expected values gives you no appreciation of how close you are to ruin. How would you calculate the probability of ruin? The model solution can be found in this example model: