b. Re-design the spreadsheet model to incorporate the random variability of the annual salary growth rate and the annual portfolio growth rate into a simulation model. Assume that Tom is willing to use the annual investment rate that predicted a 20-year $1,000,000 portfolio from your answer to part a above. Run the simulation model 1000 times and determine the mean and standard deviation of the 20-year result.

Answer From Part A)

1. Without considering random variability, extend the current worksheet to 20 years (Hint: To extend current worksheet to 20 years: Select A15 to G15  Drag the selection down to row 30 by clicking on the lower right corner of the selection and dragging down). Confirm that by using the constant annual salary growth rate and the constant annual portfolio growth rate Tom can expect to have a 20-year portfolio of $772,722. What would Tom’s annual investment rate have to be in order for his portfolio to reach a 20-year goal of $1,000,000? (Hint: use Excel’s Goal Seek; How to use Goal Seek: Data ► What-If Analysis, select Goal Seek, select F30 for Set Cell, enter the goal of 1000000 into To Value, and select B6 for By Changing Cell)

Tom’s annual investment rate would have to be 9.13% in order for his portfolio to reach a 20-year goal of $1,000,000.

  

1 Four Corners 2 3 Age 4 Current Salary 5 Current Portfolio 6 Annual Investment Rate 7 Salary Growth Rate 8 Portfolio Growth Rate 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 7 Year Beginning Balance 1 $50,000 2 $60,355 3 4 5 6 8 9 SE25 10 11 12 13 14 15 16 17 18 19 20 21 22 40 $85,000 $50,000 6.00% 23 24 25 5% 10% $72,013 $85,118 $99,829 Salary New Investment $85,000 $89,250 $93,713 $98,398 $103,318 $5,100 $5,355 $5,623 $5,904 $6,199 Salary Growth (Uniform Dist.) Lower Bound Upper Bound Earnings Ending Balance $5,255 $60,355 $6,303 $72,013 $85,118 $99,829 $116,321 $7,482 $8,807 $10,293 Age 41 42 43 44 45 Portfolio Growth (Normal Dist.) Mean Standard Dev.