Excel Monte Carlo Simulation for Investments

In this video, we will discuss how you can use Monte Carlo simulations to produce a future prediction on investments. We start by simulating a potential outcome for the investments.

0:00 Excel Monte Carlo Simulations for Investments Intro
0:27 Computing the Potential Investments’ Outcomes
1:11 Producing the Monte Carlo Simulations
2:38 Verifying the Objective

To do this, we assume the underlying distribution for the investments is a normal one and hence we would like to simulate a random draw from a normal distribution. We type NORM.INV of RAND(), and next we insert the parameters, first the average and then the standard deviation.

The second step is to produce several simulations of the potential investment outcomes. We will produce 10.000 simulations as this is a common number of simulations to obtain a reliable result. We insert the series 1 to 10.000 on the worksheet.

his can be done by typing 1, selecting that cell and navigating to the Fill button on the Home tab. Here, you select Series and the Series box opens.

Next, you choose to put the series in Columns, you want a Linear series with step value 1 and Stop value 10.000. Then press Ok and the numbers 1 to 10.000 appear on the screen.

Next to number one you refer to the investment outcome of the investments as these are the values that you would like to produce simulations of.

To create the actual simulations now, you select all 10.000 numbers and the investment outcomes. Next, you navigate to the Data tab, to What-If Analysis and Data Table.

Here the Row input cell can be left blank and for the Column input cell, you select whatever empty cell on the worksheet. Excel will use this cell to make its calculations for the simulations.

The third and final step is to verify your objective. To do this, we count the number of times that the simulated outcomes satisfy the objective and then divide this by 10.000. To do the counting, we use the COUNTIF function.

Here we need to insert a range and criteria. The range is the simulated numbers and the criteria is whatever your objective is, e.g. higher than or equal to 0.05 which you need to put in between quotes. Then we divide by 10.000 and press Enter.

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

#Excel #Tutorials #Statistics