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.

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

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

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

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

Results of the Monte Carlo Simulation are following

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

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.

Still stressed from student homework?
Get quality assistance from academic writers!