CHAPTER 13

COMPUTER SIMULATION WITH RISK SOLVER PLATFORM

SOLUTION TO SOLVED PROBLEMS

13.S1 Saving for Retirement

Patrick  Gordon  is  ten  years  away  from  retirement.  He  has  accumulated  a  $100,000  nest  egg  
that  he  would  like  to  invest  for  his  golden  years.  Furthermore,  he  is  confident  that  he  can  
invest  $10,000  more  each  year  until  retirement.  He  is  curious  about  what  kind  of  nest  egg  he  
can  expect  to  have  accumulated  at  retirement  ten  years  from  now.  
Patrick  plans  to  split  his  investments  evenly  among  four  investments:  a  Money  Market  Fund,  a  
Domestic  Stock  Fund,  a  Global  Stock  Fund,  and  an  Aggressive  Growth  Fund.  Based  on  past  
performance,  Patrick  expects  each  of  these  funds  to  earn  a  return  in  each  of  the  upcoming  ten  
years  according  to  the  distributions  shown  in  the  following  table.    
Fund   Distribution  
Money  Market   Uniform  (Minimum  =  2%,  Maximum  =  5%)  
Domestic  Stock   Normal  (Mean  =  6%,  Standard  Deviation  =  5%)  
Global  Stock   Normal  (Mean  =  8%,  Standard  Deviation  =  10%)  
Aggressive  Growth   Normal  (Mean  =  11%,  Standard  Deviation  =  16%)  
 
Assume  that  the  initial  nest  egg  ($100,000)  and  the  first  year’s  investment  ($10,000)  are  
made  right  now  (year  0)  and  are  split  evenly  among  the  four  funds  (i.e.,  $27,500  in  each  fund).  
The  returns  of  each  fund  are  allowed  to  accumulate  (i.e.,  are  re-­‐invested)  in  the  same  fund  
and  no  redistribution  will  be  done  before  retirement.  Furthermore,  nine  additional  
investments  of  $10,000  will  be  made  and  split  evenly  among  the  four  funds  ($2,500  each)  at  
year  1,  year  2,  ...,  year  9.  
A  financial  advisor  has  told  Patrick  that  he  can  retire  comfortably  if  he  can  accumulate  
$300,000  by  year  10  to  supplement  his  other  sources  of  retirement  income.  Use  a  1000-­‐trial  
RSPE  simulation  to  estimate  each  of  the  following.  
The  uncertain  elements  in  this  problem  are  the  annual  return  of  each  investment  over  the  
next  10  years  (Year  0  through  Year  9).  To  simulate  this,  we  define  an  uncertain  variable  cell  
for  the  annual  return  of  each  investment  in  each  year.  These  cells  are  defined  in  rows  12,  
17,  22,  and  27  of  the  spreadsheet  below.  
To  track  the  investments,  we  calculate  their  balances  in  each  year.  Row  10,  15,  20,  and  25  
show  the  investment  made  by  Patrick  in  each  year.    
Rows  11,  16,  21,  and  26  calculate  the  balance  in  each  fund  at  the  start  of  the  year.  For  Year  
0  in  each  fund,  this  will  simply  be  the  initial  investment  ($25,000)  plus  the  annual  
investment  ($2,500).  For  each  future  year,  it  will  be  the  balance  at  the  end  of  the  preceding  
year  plus  the  annual  investment.  For  example,  for  Year  1  of  the  money  market  fund,  the  
starting  balance  is  D11  =  C13  +  D10.  

1
Rows  13,  18,  23,  and  28  calculate  the  year-­‐end  balance  for  each  fund.  This  will  be  the  
starting  balance  times  the  net  return.  For  example,  for  the  money  market  fund  in  Year  0  
this  will  be  C13  =  C11*(1+C12).  
Finally,  the  Year  10  totals  are  added  up  in  M30  to  calculate  Patrick’s  final  nest  egg.  This  cell  
is  defined  as  a  results  cell  in  RSPE.  Two  statistic  cells  are  defined  in  M31  and  M32  to  
estimate  the  mean  and  standard  deviation  of  the  final  nest  egg.  
B C D E F G H I J K L M N O P Q
3 Investments Initial Annual
4 Money Market Fund $25,000 $2,500
5 Income Fund $25,000 $2,500
6 Growth & Income $25,000 $2,500
7 Aggressive Growth Fund $25,000 $2,500
8
9 Year 0 Year 1 Year
Year
2Year
3Year
4Year
5Year
6 7 Year 8 Year 9 Year 10
10 Money Market Investment $27,500 $2,500 # # # # # # $2,500 $2,500
11 Money Market Start $27,500 $31,223 # # # # # # $58,553 $63,214 $64,938
12 Money Market Return (%) 4.4% 3.2% # # # # # # 3.7% 2.7% Uniform 2% 5% (Min, Max)
13 Money Market End $28,723 $32,238 # # # # # # $60,714 $64,938
14
15 Income Fund Investment $27,500 $2,500 # # # # # # $2,500 $2,500
16 Income Fund Start $27,500 $32,118 # # # # # # $72,806 $88,134 $93,878
17 Income Fund Return (%) 7.7% 11.9% # # # # # # 17.6% 6.5% Normal 6% 5% (Mean, St. Dev.)
18 Income Fund End $29,618 $35,943 # # # # # # $85,634 $93,878
19
20 Growth & Income Investment $27,500 $2,500 # # # # # # $2,500 $2,500
21 Growth & Income Start $27,500 $27,154 # # # # # # $51,769 $55,277 $56,501
22 Growth & Income Return (%) -10.3% 7.1% # # # # # # 1.9% 2.2% Normal 8% 10% (Mean, St. Dev.)
23 Growth & Income End $24,654 $29,093 # # # # # # $52,777 $56,501
24
25 Aggressive Growth Investment $27,500 $2,500 # # # # # # $2,500 $2,500
26 Aggressive Growth Start $27,500 $31,674 # # # # # # $50,575 $58,760 $75,006
27 Aggressive Growth Return (%) 6.1% 9.1% # # # # # # 11.2% 27.6% Normal 11% 16% (Mean, St. Dev.)
28 Aggressive Growth End $29,174 $34,553 # # # # # # $56,260 $75,006
29
30 Total Nest Egg $290,321
31 Mean(Nest Egg) $355,819
32 Std. Dev. (Nest Egg) $53,855  
 
B C D EFGHIJK L M N O P Q
3 Investments Initial Annual
4 Money Market Fund 25000 2500
5 Income Fund 25000 2500
6 Growth & Income 25000 2500
7 Aggressive Growth Fund 25000 2500
8
9 Year 0 Year 1 Year
Year
Year
Year
Year
2
Year
3
Year
45678 Year 9 Year 10
10 Money Market Investment =C4+D4 =$D$4 =$D$4
=$D$4
=$D$4
=$D$4
=$D$4
=$D$4
=$D$4
=$D$4
11 Money Market Start =C10 =C13+D10 =D13+E10
=E13+F10
=F13+G10
=G13+H10
=H13+I10
=I13+J10
=J13+K10
=K13+L10 =L13+M10
12 Money Market Return (%) =PsiUniform($O$12,$P$12) =PsiUniform($O$12,$P$12) =PsiUniform($O$12,$P$12)
=PsiUniform($O$12,$P$12)
=PsiUniform($O$12,$P$12)
=PsiUniform($O$12,$P$12)
=PsiUniform($O$12,$P$12)
=PsiUniform($O$12,$P$12)
=PsiUniform($O$12,$P$12)
=PsiUniform($O$12,$P$12) Uniform 0.02 0.05 (Min, Max)
13 Money Market End =C11*(1+C12) =D11*(1+D12) =E11*(1+E12)
=F11*(1+F12)
=G11*(1+G12)
=H11*(1+H12)
=I11*(1+I12)
=J11*(1+J12)
=K11*(1+K12)
=L11*(1+L12)
14
15 Income Fund Investment =C5+D5 =$D$5 =$D$5
=$D$5
=$D$5
=$D$5
=$D$5
=$D$5
=$D$5
=$D$5
16 Income Fund Start =C15 =C18+D15 =D18+E15
=E18+F15
=F18+G15
=G18+H15
=H18+I15
=I18+J15
=J18+K15
=K18+L15 =L18+M15
17 Income Fund Return (%) =PsiNormal($O$17,$P$17) =PsiNormal($O$17,$P$17) =PsiNormal($O$17,$P$17)
=PsiNormal($O$17,$P$17)
=PsiNormal($O$17,$P$17)
=PsiNormal($O$17,$P$17)
=PsiNormal($O$17,$P$17)
=PsiNormal($O$17,$P$17)
=PsiNormal($O$17,$P$17)
=PsiNormal($O$17,$P$17) Normal 0.06 0.05 (Mean, St. Dev.)
18 Income Fund End =C16*(1+C17) =D16*(1+D17) =E16*(1+E17)
=F16*(1+F17)
=G16*(1+G17)
=H16*(1+H17)
=I16*(1+I17)
=J16*(1+J17)
=K16*(1+K17)
=L16*(1+L17)
19
20 Growth & Income Investment =C6+D6 =$D$6 =$D$6
=$D$6
=$D$6
=$D$6
=$D$6
=$D$6
=$D$6
=$D$6
21 Growth & Income Start =C20 =C23+D20 =D23+E20
=E23+F20
=F23+G20
=G23+H20
=H23+I20
=I23+J20
=J23+K20
=K23+L20 =L23+M20
22 Growth & Income Return (%) =PsiNormal($O$22,$P$22) =PsiNormal($O$22,$P$22) =PsiNormal($O$22,$P$22)
=PsiNormal($O$22,$P$22)
=PsiNormal($O$22,$P$22)
=PsiNormal($O$22,$P$22)
=PsiNormal($O$22,$P$22)
=PsiNormal($O$22,$P$22)
=PsiNormal($O$22,$P$22)
=PsiNormal($O$22,$P$22) Normal 0.08 0.1 (Mean, St. Dev.)
23 Growth & Income End =C21*(1+C22) =D21*(1+D22) =E21*(1+E22)
=F21*(1+F22)
=G21*(1+G22)
=H21*(1+H22)
=I21*(1+I22)
=J21*(1+J22)
=K21*(1+K22)
=L21*(1+L22)
24
25 Aggressive Growth Investment =C7+D7 =$D$7 =$D$7
=$D$7
=$D$7
=$D$7
=$D$7
=$D$7
=$D$7
=$D$7
26 Aggressive Growth Start =C25 =C28+D25 =D28+E25
=E28+F25
=F28+G25
=G28+H25
=H28+I25
=I28+J25
=J28+K25
=K28+L25 =L28+M25
27 Aggressive Growth Return (%) =PsiNormal($O$27,$P$27) =PsiNormal($O$27,$P$27) =PsiNormal($O$27,$P$27)
=PsiNormal($O$27,$P$27)
=PsiNormal($O$27,$P$27)
=PsiNormal($O$27,$P$27)
=PsiNormal($O$27,$P$27)
=PsiNormal($O$27,$P$27)
=PsiNormal($O$27,$P$27)
=PsiNormal($O$27,$P$27) Normal 0.11 0.16 (Mean, St. Dev.)
28 Aggressive Growth End =C26*(1+C27) =D26*(1+D27) =E26*(1+E27)
=F26*(1+F27)
=G26*(1+G27)
=H26*(1+H27)
=I26*(1+I27)
=J26*(1+J27)
=K26*(1+K27)
=L26*(1+L27)
29
30 Total Nest Egg =M11+M16+M21+M26 + PsiOutput()
31 Mean(Nest Egg) =PsiMean(M30)
32 Std. Dev. (Nest Egg) =PsiStdDev(M30)
 

2
 The  results  of  a  1000-­‐trial  simulation  run  are  shown  below.  
 

 
 
a.   What  will  be  the  expected  value  (mean)  of  Patrick’s  nest  egg  at  year  10?  
The  mean  of  the  nest  egg  at  year  10  is  nearly  $356  thousand.  
b.   What  will  be  the  standard  deviation  of  Patrick’s  nest  egg  at  year  10?  
The  standard  deviation  of  Patrick’s  nest  egg  at  year  10  is  nearly  $54  thousand.  
c.   What  is  the  probability  that  the  total  nest  egg  at  year  10  will  be  at  least  $300,000?  
There  is  nearly  an  88%  chance  that  the  total  nest  egg  at  year  10  will  be  at  least  $300,000.