# Solved: Age 20 Current Salary \$ 10,000 Current Portfolio \$ 5,000 Annual Investment Rate 41% Salary Growth Rate

 Age 20 Current Salary \$ 10,000 Current Portfolio \$ 5,000 Annual Investment Rate 41% Salary Growth Rate 35% Portfolio Growth Rate 10% Year Beginning Balance Salary New Investment Earnings Ending Balance Age 1 \$ 5,000 \$ 10,000 \$ 4,093 \$ 1,932 \$ 11,025 21 2 \$ 11,025 \$ 13,500 \$ 5,525 \$ 3,036 \$ 19,586 22 3 \$ 19,586 \$ 18,225 \$ 7,459 \$ 4,569 \$ 31,614 23 4 \$ 31,614 \$ 24,604 \$ 10,069 \$ 6,686 \$ 48,369 24 5 \$ 48,369 \$ 33,215 \$ 13,594 \$ 9,594.62 \$ 71,557 25 6 \$ 71,557 \$ 44,840 \$ 18,351 \$ 13,578.64 \$ 103,487 26 7 \$ 103,487 \$ 60,534 \$ 24,774 \$ 19,019.66 \$ 147,281 27 8 \$ 147,281 \$ 81,722 \$ 33,445 \$ 26,433.87 \$ 207,160 28 9 \$ 207,160 \$ 110,324 \$ 45,151 \$ 36,518.80 \$ 288,830 29 10 \$ 288,830 \$ 148,937 \$ 60,954 \$ 50,216.76 \$ 400,000 30

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. 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.