Instead of finding the expected return at different percentiles, we can turn the analysis around and find the probability of reaching a particular target return with the SimulationInterval function: This kind of analysis can be useful in determining the real levels of risk associated with an investment portfolio. Or, there is a 75% chance that our model will have a total return of 16.61% or higher.Д«y changing the percentile values, we can determine the expected return of the portfolio with different probabilities. This tells us that, over the simulation, 75% of the time the value of F11 is higher than or equal to 16.61%. In Figure E, cell I11 shows the 25th Percentile result for cell F11. Another way to say it is, there is a 25% chance that any value in the simulation will be lower to or equal to this amount and at the same time, there is a 75% chance that any value in the simulation will be higher than or equal to this amount. The 25th Percentile, then, represents a value that is equal to or higher than 25% of the results seen during the simulation. Therefore the maximum value is the 100th Percentile. The last value would be the maximum 100% of the values in the results are equal to, or lower than, the last value. The first value would be the minimum, as seen above no values in the results are lower than the minimum value. To understand what the percentiles mean, imagine that we take every result seen in cell F11 over the Monte Carlo simulation, and place them in order (lowest to highest). In Figure E, cell I11 contains the SimulationPercentile function as seen in the function bar. To begin with, we can look at the minimum and maximum values identified during the simulation using the SimulationMin and SimulationMax functions: However, we can get much more useful information from the Monte Carlo simulation by looking at ranges and percentiles. If that were the only thing we could learn from the simulation, it wouldnвЂ™t have much use. Step 3: Analyzing DataДЄs noted above, the average return given by the Monte Carlo simulation is close to the original, fixed model. This is as expected, because the random data weвЂ™re using for returns has an average of 5.4%, which was the fixed value in the original model. YouвЂ™ll see that the average value, returned in cell H11, is close to the original fixed value of 30.08% (see cell F11 in Figure A). Once the simulation is complete, the average value can be calculated from this set of stored values. The Monte Carlo simulation runs hundreds or thousands of times, and at each iteration the RiskAMP Add-in stores and remembers the value of cell F11. This results in a different value in cell F11. When you run a Monte Carlo simulation, at each iteration new random values are placed in column D and the spreadsheet is recalculated. In this example, cell H11 calculates the average value of cell F11 over all the trials, or iterations, of the Monte Carlo simulation. In Figure C, weвЂ™ve added average simulation results in column H using the function seen in the function bar. Once you run a simulation, this error will go away. This is because the simulation hasn't collected data for the cell yet. Note: the first time you enter these functions in a spreadsheet, you'll see an #N/A error. To start, weвЂ™ll look at the average results of the simulation using the SimulationMean function. The RiskAMP Add-in includes a number of functions to analyze the results of a Monte Carlo simulation. (In Excel, use the вЂњRun SimulationвЂќ button on the Monte Carlo toolbar). To run a Monte Carlo simulation, click the вЂњPlayвЂќ button next to the spreadsheet. Step 2: Running a Monte Carlo SimulationДЄ Monte Carlo simulation calculates the same model many many times, and tries to generate useful information from the results. The key to using Monte Carlo simulation is to take many random values, recalculating the model each time, and then analyze the results. Randomly-distributed returns seem like a better approximation of the real world, but taking a single random return isnвЂ™t useful. The total return ( F11) can also differ significantly from the original value (30.08%). If you recalculate the model at this step, you will see each return change. The returns in each period are randomly generated. In figure B, the return in each period has been changed from a fixed 5.4% to a randomly distributed return, using the function seen in the function bar.

Monte carlo simulation excel