Question

1 Approved Answer

Posted on Sep 07, 2024

Build a Google Sheets file capable of simulating annual stock+bond returns to generate estimates of retirement savings. Use a 40-year time horizon Simulation count ,

Build a Google Sheets file capable of simulating annual stock+bond returns to generate estimates of retirement savings.
Use a 40-year time horizon
Simulation count , n >= 100
Features:
- Ability to modify initial balance and monthly contribution.
- Ability to modify stock/bond allocation, e.g., 60%/40%.
- Ability to set financial target, e.g., $1,000,000.
- Menu item or start button to launch simulation
- At end of simulation, probability of reaching financial target is displayed.
- At end of simulation, distribution of end balances is displayed on a histogram.

Here is the example google sheets:

image text in transcribed

= Stock Marke = = Year = Stock Return Mean Return 11.88 1 Enorminv(rand().$B$2,5B53) Standard Deviati 19.76 =C2+1 =norminv(rand().$B$2,$B$3) Beginning Balani $10,000 =C3+1 =norminv(rand() $B$2.SB$3) =C4+1 =norminv(rand(),SB$2,5B$3) Bond Market =C5+1 Enorminv(rand() SB$2$B$3) Mean 7.88 =C6+1 =norminv(rand(), SB$2.SB$3) Std Deviation 6.86 =C7+1 Enorminv(rand() $B$2,SB$3) =C8+1 =norminv(rand() SB$2,SB$3) =C9+1 Enorminv(rand().SB$2,SB53) =C10+1 =norminv(rand() $B$2,SB$3) =C11+1 =norminv(rand(), SB$2,SB$3) =C12+1 Enorminv(rand() $B$2,$B$3) =C13+1 =norminv(rand().$B$2.$B$3) =C14+1 =norminv(rand().$B$2.SB$3) =C15+1 Enorminv(rand().$B$2,$B$3) =C16+1 Enorminv(rand().SB$2,$B$3) =C17+1 Enorminv(rand() $B$2,$B$3) =C18+1 Enorminv(rand() $B$2,$B$3) =C19+1 =norminv(rand().$B$2.$B$3) =C20+1 =norminv(rand(), SB$2.SBS3) =C21+1 Enorminv(rand().SB$2,SB53) =C22+1 =norminv(rand().SB$2,SB53) =C23+1 =norminv(rand(), SB$2,5B$3) =C24+1 =norminv(rand().$B$2.$B$3) =C25+1 =norminv(rand(), SB$2,$B$3) =C26+1 =norminv(rand() $B$2,SB$3) =C27+1 =norminv(rand().$B$2.$B$3) =C28+1 =norminv(rand() $B$2,$B$3) =C29+1 Enorminv(rand() $B$2,$B$3) =C30+1 =norminv(rand() $B$2,SB$3) = Stock Balance = Random Bonds = Bond Balance = Sum =$B$4*0.6*(1+D2/100) Enorminv(rand(), $B$7, $B$8) =$B$4+0.4*(1+F2/100) =E2+G2 =H2*0.6*(1+D3/100) Enorminv (rand(), $B$7, $B$8) =H2*0.4*(1+F3/100) =E3+G3 =H3*0.6*(1+D4/100) Enorminv(rand(), $B$7, $B$8) =H3*0.4+(1+F4/100) =E4+G4 =H4*0.6*(1+D5/100) =norminv(rand(), $B$7, $B$8) =H4*0.4+(1+F5/100) =E5+G5 =H5*0.6*(1+06/100) Enorminv (rand(), $B$7, $B$8) =H5*0.4*(1+F6/100) =E6+G6 =H6*0.6*(1+D7/100) =norminv(rand(), $B$7, $B$8) =H6*0.4*(1+F7/100) =E7+G7 =H7*0.6*(1+D8/100) Enorminv(rand(), $B$7, $B$8) =H7*0.4*(1+F8/100) =E8+G8 =H8*0.6*(1+D9/100) =norminv(rand(), $B$7, $B$8) =H8*0.4*(1+F9/100) =E9+G9 =H9*0.6*(1+D10/100) Enorminv (rand(), $B$7, $B$8) =H9*0.4*(1+F10/100) =E10+G10 =H10*0.6*(1 +D11/100) =norminv(rand(), $B$7, $B$8) =H10*0.4*(1+F11/100) =E11+G11 =H11*0.6*(1+D12/100) Enorminv(rand(), $B$7, $B$8) =H11*0.4*(1+F12/100) =E12+G12 =H12*0.6*(1+D13/100) Enorminv (rand(), $B$7, $B$8) =H12*0.4*(1+F13/100) =E13+G13 =H13*0.6*(1+D14/100) =norminv (rand(), $B$7, $B$8) =H13*0.4*(1+F14/100) =E14+G14 =H14*0.6*(1+D15/100) Enorminv (rand(, $B$7, $B$8) =H14*0.4*(1+F15/100) =E15+G15 =H15*0.6*(1+D16/100) Enorminv(rand(), $B$7, $B$8) =H15*0.4*(1+F16/100) =E16+G16 =H16*0.6*(1+D17/100) Enorminv(rand(), $B$7, $B$8) -H16*0.4*(1+F17/100) =E17+G17 =H17*0.6*(1+D18/100) Enorminv (rand(), $B$7, $B$8) =H17*0.4*(1+F18/100) =E18+G18 =H18*0.6*(1+D19/100) Enorminv (rand(), $B$7, $B$8) =H18*0.4*(1+F19/100) =E19+G19 =H19*0.6*(1+D20/100) Enorminv (rand(), $B$7, $B$8) =H19*0.4*(1+F20/100) =E20+G20 =H20*0.6*(1+D21/100) =norminv(rand(), $B$7, $B$8) =H20*0.4*(1+F21/100) =E21+G21 =H21*0.6*(1+D22/100) Enorminv (rand(), $B$7, $B$8) =H21*0.4*(1+F22/100) =E22+G22 =H22*0.6*(1+D23/100) =norminv (rando, $B$7, $B$8) =H22*0.4*(1+F23/100) =E23+G23 =H23*0.6*(1+D24/100) Enorminv(rand(), $B$7, $B$8) =H23*0.4*(1+F24/100) =E24+G24 =H24*0.6" (1+D25/100) Enorminv (rand(), $B$7, $B$8) =H24*0.4*(1+F25/100) =E25+G25 =H25*0.6*(1+D26/100) Enorminv (rand(), $B$7, $B$8) -H25*0.4*(1+F26/100) =E26+G26 =H26*0.6*(1 +D27/100) =norminv(rand(), $B$7, $B$8) =H26*0.4*(1+F27/100) =E27+G27 =H27*0.6*(1+D28/100) =norminv(rand(), $B$7, $B$8) =H27*0.4*(1+F28/100) =E28+G28 =H28*0.6*(1+D29/100) Enorminv (rand(), $B$7, $B$8) =H28*0.4*(1+F29/100) =E29+G29 =H29*0.6*(1+D30/100) Enorminv (rand(), $B$7, $B$8) =H29*0.4*(1+F30/100) =E30+G30 =E30*(1 +D31/100) Enorminv(rand(), $B$7, $B$8) -H30*0.4*(1+F31/100) =E31+G31 End Balance $124,312.01 $122,302.47 $137,683.21 $179,000.17 $65,154.81 $121,086.86 $131,387.14 $58, 110.52 $204,255.74 $45,224.87 $146,450.47 $175,304.30 $183,221.69 $216,069.77 $277,991.92 $122,013.36 $226,227.25 $281.719.77 $67,823.33 $492,379.67 $94,885.02 $36,789.24 $201,590.21 $204,422.66 $233,741.95 $145,702.08 $67,631.08 $87,139.15 $117,714.02 $124,338.93 = Stock Marke = = Year = Stock Return Mean Return 11.88 1 Enorminv(rand().$B$2,5B53) Standard Deviati 19.76 =C2+1 =norminv(rand().$B$2,$B$3) Beginning Balani $10,000 =C3+1 =norminv(rand() $B$2.SB$3) =C4+1 =norminv(rand(),SB$2,5B$3) Bond Market =C5+1 Enorminv(rand() SB$2$B$3) Mean 7.88 =C6+1 =norminv(rand(), SB$2.SB$3) Std Deviation 6.86 =C7+1 Enorminv(rand() $B$2,SB$3) =C8+1 =norminv(rand() SB$2,SB$3) =C9+1 Enorminv(rand().SB$2,SB53) =C10+1 =norminv(rand() $B$2,SB$3) =C11+1 =norminv(rand(), SB$2,SB$3) =C12+1 Enorminv(rand() $B$2,$B$3) =C13+1 =norminv(rand().$B$2.$B$3) =C14+1 =norminv(rand().$B$2.SB$3) =C15+1 Enorminv(rand().$B$2,$B$3) =C16+1 Enorminv(rand().SB$2,$B$3) =C17+1 Enorminv(rand() $B$2,$B$3) =C18+1 Enorminv(rand() $B$2,$B$3) =C19+1 =norminv(rand().$B$2.$B$3) =C20+1 =norminv(rand(), SB$2.SBS3) =C21+1 Enorminv(rand().SB$2,SB53) =C22+1 =norminv(rand().SB$2,SB53) =C23+1 =norminv(rand(), SB$2,5B$3) =C24+1 =norminv(rand().$B$2.$B$3) =C25+1 =norminv(rand(), SB$2,$B$3) =C26+1 =norminv(rand() $B$2,SB$3) =C27+1 =norminv(rand().$B$2.$B$3) =C28+1 =norminv(rand() $B$2,$B$3) =C29+1 Enorminv(rand() $B$2,$B$3) =C30+1 =norminv(rand() $B$2,SB$3) = Stock Balance = Random Bonds = Bond Balance = Sum =$B$4*0.6*(1+D2/100) Enorminv(rand(), $B$7, $B$8) =$B$4+0.4*(1+F2/100) =E2+G2 =H2*0.6*(1+D3/100) Enorminv (rand(), $B$7, $B$8) =H2*0.4*(1+F3/100) =E3+G3 =H3*0.6*(1+D4/100) Enorminv(rand(), $B$7, $B$8) =H3*0.4+(1+F4/100) =E4+G4 =H4*0.6*(1+D5/100) =norminv(rand(), $B$7, $B$8) =H4*0.4+(1+F5/100) =E5+G5 =H5*0.6*(1+06/100) Enorminv (rand(), $B$7, $B$8) =H5*0.4*(1+F6/100) =E6+G6 =H6*0.6*(1+D7/100) =norminv(rand(), $B$7, $B$8) =H6*0.4*(1+F7/100) =E7+G7 =H7*0.6*(1+D8/100) Enorminv(rand(), $B$7, $B$8) =H7*0.4*(1+F8/100) =E8+G8 =H8*0.6*(1+D9/100) =norminv(rand(), $B$7, $B$8) =H8*0.4*(1+F9/100) =E9+G9 =H9*0.6*(1+D10/100) Enorminv (rand(), $B$7, $B$8) =H9*0.4*(1+F10/100) =E10+G10 =H10*0.6*(1 +D11/100) =norminv(rand(), $B$7, $B$8) =H10*0.4*(1+F11/100) =E11+G11 =H11*0.6*(1+D12/100) Enorminv(rand(), $B$7, $B$8) =H11*0.4*(1+F12/100) =E12+G12 =H12*0.6*(1+D13/100) Enorminv (rand(), $B$7, $B$8) =H12*0.4*(1+F13/100) =E13+G13 =H13*0.6*(1+D14/100) =norminv (rand(), $B$7, $B$8) =H13*0.4*(1+F14/100) =E14+G14 =H14*0.6*(1+D15/100) Enorminv (rand(, $B$7, $B$8) =H14*0.4*(1+F15/100) =E15+G15 =H15*0.6*(1+D16/100) Enorminv(rand(), $B$7, $B$8) =H15*0.4*(1+F16/100) =E16+G16 =H16*0.6*(1+D17/100) Enorminv(rand(), $B$7, $B$8) -H16*0.4*(1+F17/100) =E17+G17 =H17*0.6*(1+D18/100) Enorminv (rand(), $B$7, $B$8) =H17*0.4*(1+F18/100) =E18+G18 =H18*0.6*(1+D19/100) Enorminv (rand(), $B$7, $B$8) =H18*0.4*(1+F19/100) =E19+G19 =H19*0.6*(1+D20/100) Enorminv (rand(), $B$7, $B$8) =H19*0.4*(1+F20/100) =E20+G20 =H20*0.6*(1+D21/100) =norminv(rand(), $B$7, $B$8) =H20*0.4*(1+F21/100) =E21+G21 =H21*0.6*(1+D22/100) Enorminv (rand(), $B$7, $B$8) =H21*0.4*(1+F22/100) =E22+G22 =H22*0.6*(1+D23/100) =norminv (rando, $B$7, $B$8) =H22*0.4*(1+F23/100) =E23+G23 =H23*0.6*(1+D24/100) Enorminv(rand(), $B$7, $B$8) =H23*0.4*(1+F24/100) =E24+G24 =H24*0.6" (1+D25/100) Enorminv (rand(), $B$7, $B$8) =H24*0.4*(1+F25/100) =E25+G25 =H25*0.6*(1+D26/100) Enorminv (rand(), $B$7, $B$8) -H25*0.4*(1+F26/100) =E26+G26 =H26*0.6*(1 +D27/100) =norminv(rand(), $B$7, $B$8) =H26*0.4*(1+F27/100) =E27+G27 =H27*0.6*(1+D28/100) =norminv(rand(), $B$7, $B$8) =H27*0.4*(1+F28/100) =E28+G28 =H28*0.6*(1+D29/100) Enorminv (rand(), $B$7, $B$8) =H28*0.4*(1+F29/100) =E29+G29 =H29*0.6*(1+D30/100) Enorminv (rand(), $B$7, $B$8) =H29*0.4*(1+F30/100) =E30+G30 =E30*(1 +D31/100) Enorminv(rand(), $B$7, $B$8) -H30*0.4*(1+F31/100) =E31+G31 End Balance $124,312.01 $122,302.47 $137,683.21 $179,000.17 $65,154.81 $121,086.86 $131,387.14 $58, 110.52 $204,255.74 $45,224.87 $146,450.47 $175,304.30 $183,221.69 $216,069.77 $277,991.92 $122,013.36 $226,227.25 $281.719.77 $67,823.33 $492,379.67 $94,885.02 $36,789.24 $201,590.21 $204,422.66 $233,741.95 $145,702.08 $67,631.08 $87,139.15 $117,714.02 $124,338.93