Monte Carlo Simulation in Excel

Monte Carlo simulation in MS Excel

We can evaluate possible scenarios in MS Excel using the help of simulation.

To understand this we need to understand what is simulation & how can this be implemented in Excel. If we want to model a process, a process is typically represented as:

Y = f(x1, x2…)

Now in a simulation exercise, we try to estimate the ranges of the input variables and vary the input variables in this range for a sufficient number of iterations and then try to understand the distribution of the output Y.

For example if we try to calculate the corpus accumulated during a period of 20 years wherein the investment amount varies between 1000 to 3500 per month and the growth expected in the corpus lies between -5% and 12%.

We use the Excel formula RANDBETWEEN(1000,3500) to generate random investment amount  per month and then multiply by 12 to calculate yearly investments.

The interest rate per year is also generated using RANDBETWEEN(-5,12)/100 for the 20 years period.

The lumpsum at end of year is calculated using:

Lumpsum (t+1) = Lumpsum(t)*(1+interest rate for the year) + Investment.

Here we assume that interest on investment is accrued on annual basis for simplicity of calculation.

Screen shots as below:

The top left corner cell where the text "Iteration" is present is cell A1. 

Results:

For the 1^st simulation 1: the lumpsum at end of 20 year period is 623537.

If we rerun this simulation 500 times, we plot a histogram of the final value and the average value. 

This is possible by using the data table feature of MS Excel. (screen shots as below)

Select the entire table from simulation 1 to 500 and the right hand column as well alogwith the header as shown below.

Then:

Subsequent to this expand the box and press ok.

The results will as below and then we can use AVERAGE, MIN & MAX to find out the averages as well range of results coming from the simulation.