Creating a Monte Carlo Simulation Using Excel

A Monte Carlo simulation can be developed using Microsoft Excel and a game of dice. A Monte Carlo simulation is a method for modeling probabilities by using random numbers to approximate and simulate possible outcomes. Today, it is widely used as an analysis tool. It plays a key part in various fields such as finance, physics, chemistry, and economics.

Key Takeaways

• The Monte Carlo method seeks to improve the analysis of data using random data sets and probability calculations.
• A Monte Carlo simulation can be developed using Microsoft Excel and a game of dice.
• A data table can be used to generate the results—a minimum of 5,000 results are needed to prepare the Monte Carlo simulation. 

Monte Carlo Simulation

The Monte Carlo method was invented by John von Neumann and Stanislaw Ulam in the 1940s and seeks to solve complex problems using random and probabilistic methods. The term Monte Carlo refers the administrative area of Monaco popularly known as a place where European elites gamble. 

The Monte Carlo simulation method computes the probabilities for integrals and solves partial differential equations, thereby introducing a statistical approach to risk in a probabilistic decision. Although many advanced statistical tools exist to create Monte Carlo simulations, it is easier to simulate the normal law and the uniform law using Microsoft Excel and bypass the mathematical underpinnings.

When to Use the Monte Carlo Simulation

We use the Monte Carlo method when a problem is too complex and difficult to do by direct calculation. Using the simulation can help provide solutions for situations that prove uncertain. A large number of iterations allows a simulation of the normal distribution. It can also be used to understand how risk works, and to comprehend the uncertainty in forecasting models.

As noted above, the simulation is often used in many different disciplines including finance, science, engineering, and supply chain management—especially in cases where there are far too many random variables in play. For example, analysts may use Monte Carlo simulations in order to evaluate derivatives including options or to determine risks including the likelihood that a company may default on its debts.

Game of Dice

For the Monte Carlo simulation, we isolate a number of key variables that control and describe the outcome of the experiment, then assign a probability distribution after a large number of random samples is performed. In order to demonstrate, let’s take a game of dice as a model. Here’s how the dice game rolls:

• The player throws three dice that have six sides three times.

• If the total of the three throws is seven or 11, the player wins.

• If the total of the three throws is: three, four, five, 16, 17, or 18, the player loses.

• If the total is any other outcome, the player plays again and re-rolls the dice.

• When the player throws the dice again, the game continues in the same way, except that the player wins when the total is equal to the sum determined in the first round.

It is also recommended to use a data table to generate the results. Moreover, a minimum of 5,000 results are needed to prepare the Monte Carlo simulation.

Important

To prepare the Monte Carlo simulation, you need a minimum 5,000 results.

Step 1: Dice Rolling Events

First, we develop a range of data with the results of each of the three dice for 50 rolls. To do this, it is proposed to use the “RANDBETWEEN(1,6)” function. Thus, each time we click F9, we generate a new set of roll results. The “Outcome” cell is the sum total of the results from the three rolls.

Step 2: Range of Outcomes

Then, we need to develop a range of data to identify the possible outcomes for the first round and subsequent rounds. There is a three-column data range. In the first column, we have the numbers one to 18. These figures represent the possible outcomes following rolling the dice three times: The maximum being 3 x 6 = 18. You will note that for cells one and two, the findings are N/A since it is impossible to get a one or a two using three dice. The minimum is three.

In the second column, the possible conclusions after the first round are included. As stated in the initial statement, either the player wins (Win) or loses (Lose), or they replay (Re-roll), depending on the result (the total of three dice rolls).

In the third column, the possible conclusions to subsequent rounds are registered. We can achieve these results using the “IF” function. This ensures that if the result obtained is equivalent to the result obtained in the first round, we win, otherwise we follow the initial rules of the original play to determine whether we re-roll the dice.

Step 3: Conclusions

In this step, we identify the outcome of the 50 dice rolls. The first conclusion can be obtained with an index function. This function searches the possible results of the first round, the conclusion corresponding to the result obtained. For example, when we roll a six, we play again.

One can get the findings of other dice rolls, using an “OR” function and an index function nested in an “IF” function. This function tells Excel, “If the previous result is Win or Lose,” stop rolling the dice because once we have won or lost we are done. Otherwise, we go to the column of the following possible conclusions and we identify the conclusion of the result.

Step 4: Number of Dice Rolls

Now, we determine the number of dice rolls required before losing or winning. To do this, we can use a “COUNTIF” function, which requires Excel to count the results of “Re-roll” and add the number one to it. It adds one because we have one extra round, and we get a final result (win or lose).

Step 5: Simulation

We develop a range to track the results of different simulations. To do this, we will create three columns. In the first column, one of the figures included is 5,000. In the second column, we will look for the result after 50 dice rolls. In the third column, the title of the column, we will look for the number of dice rolls before obtaining the final status (win or lose).

Then, we will create a sensitivity analysis table by using the feature data or Table Data table (this sensitivity will be inserted in the second table and third columns). In this sensitivity analysis, the numbers of events of one to 5,000 must be inserted into cell A1 of the file. In fact, one could choose any empty cell. The idea is simply to force a recalculation each time and thus get new dice rolls (results of new simulations) without damaging the formulas in place.

Step 6: Probability

We can finally calculate the probabilities of winning and losing. We do this using the “COUNTIF” function. The formula counts the number of “win” and “lose” then divides by the total number of events, 5,000, to obtain the respective proportion of one and the other. We finally see that the probability of getting a Win outcome is 73.2% and getting a Lose outcome is therefore 26.8%. 

Read the original article on Investopedia.