I need help redesigning the spreadsheet model below to incorporate the random variability of the annual salary growth rate and the annual portfolio growth rate into a simulation model in excel. I need to show how to simulate for the 10 years of data below. The results of the model must show me the uncertainty associated with reaching my $400,000 dollar goal in 10 years. It would be best if you used the Excel add-in Analytic Solver Platform for this Monte Carl Simulation.
For simulation model, distribution of variables is required, which is not given. So I have created the simulation considering three different distributions for Annual investment rate, Salary growth rate and Portfolio growth rate as below
|Annual Investment Rate||Normal Distribution, with mean 40% and SD 5%|
|Salary Growth Rate||Uniform Distribution, between 30% and 40%|
|Portfolio Growth Rate||Discrete Distribution|
|Portfolio Growth Rate||Prob.|
Simulation model is as follows
In the next step, to know the probability (uncertainty) associated with reaching $ 400,000 goal in 10 years, we use Monte Carlo simulation (using Excel Add-in MCSim)
Select cell F22 and click on Add-in tab, click MCSim , Click Proceed
Results of the Monte Carlo Simulation are following
According to the results, probability of reaching $400,000 goal in 10 years = 1-NORMDIST(400000,391322.72,29809.55,1) = 38.55%
Therefore, there is a probability of 38.55% that goal of $ 400,000 will be reached in 10 years.
Note that results will vary depending depending upon the actual distribution of the variables and for each simulation.