USA: +1 (725) 712-1730 
Sign in
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. |
Expert Answer
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. |
| 6% | 15% |
| 8% | 20% |
| 10% | 30% |
| 12% | 20% |
| 14% | 15% |
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
Results
| Summary Statistics | ||
| Average | 391,322.72 | |
| SD | 29,809.55 | |
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.
