Excel Monte Carlo Simulation

Excel Monte Carlo Simulation

An Excel Monte Carlo simulation creates future predictions by using probabilistic and random methods. Usually, around 10.000 simulations are run to achieve a reliable outcome.

Introduction to Monte Carlo simulation 0:00
Random dice rolls 0:33
Monte Carlo Simulation 1:15
Exact probability 3:41

Feel free to read the article in full:

As an example, we are going to create a sim like this with random dice rolls. We are going to roll three dice and examine the probability that the sum of the dice is 17 or higher and we are going to do this 10.000 times.

The second step in this Excel Monte Carlo simulation example is to produce numerous simulations of these three rolls.

We are going to change the value from rows to columns, you want a Linear series so we can leave this option like it is The step value is 1 and for Stop value we are going to enter 10.000.

The third and last step in this Excel monte carlo simulation is to verify the number of times the sum is above or equal to 17 and divide this by 10.000 to find the probability.

The range are the simulated numbers and the criterion is higher than or equal to 17 which we need to put in between quotes. We need to complete the calculation so I’m going to add divide by 10.000 after the brackets If we now hit enter, we receive a simulated value of 10.000 tries. This part has shown us how to use monte carlo simulation in Excel.

The probability we calculated earlier can be determined with simple math or trial and error.

To do so, we divide the number of satisfying combinations by the number of possible combinations. The number of satisfying combinations is 4 as listed in the left-hand table.

The number of possible outcomes for the sum of three dice is computed by 6 to the power 3 as we have 6 possible outcomes for each dice.

If we then divide the first number by the second, we see that the probability of having a sum above 17 is 1.85%, which is close to the outcome based on our monte carlo simulation.

This concludes our Excel Monte Carlo simulation tutorial. I'm inspired by content creators as Leila Gharani and Teacher's Tech.

#Excel #Tutorials #Statistics

[HarshitSharma-lbul]

Well explained! thanks, will give this a try. Subscribed.

[prabhusrinivasan]

Using a mathematical model, I have estimated some system parameters using Kalman filter. Now I have to verify whether the proposed system is robust against uncertainties. I have estimated data and true sensor data. How to do Monte Carlo analysis with the above mentioned information?

[nityachaudhary]

Hey, the explaination was amazing. I just had a doubt, is MBS prepayment valuation by Monte Carlo done in the same way? Please make a tutorial of it if possible :)

[sleepinggears]

Clear explanation. I’ll be trying this one out

[heyykenn]

why is it the satisfying number of combinations is 4?