Project 3: Retirement Portfolio Simulation

March 14, 2022

Due Fri, Mar 18 at 5pm

Instructions:

• Build a Stock + Bond Market Simulation

• Simulation Requirements
  • 40-year time horizon
  • Simulation count, n=100
  • Ability to set/modify simulation parameters by changing cell values (not by editing formula)
    • initial balance
    • annual contribution
    • stock/bond % allocation
    • financial target, e.g., $1,000,000
  • Functional start button to launch automation of simulation
  • Display at end of simulation
    • probability (%) of reaching financial target
    • distribution of end balances on a histogram
    • calculation of Max, Min, Mean, Median and Standard Deviation of end balances

• Run 4 simulation scenarios
  • Scenario 1
    • Beginning balance = 10,000
    • Annual contribution = 1,000
    • Stock/Bond allocation = 60%/40%
    • Target = $1,000,000
  • Scenario 2
    • Beginning balance = 10,000
    • Annual contribution = 1,000
    • Stock/Bond allocation = 80%/20%
    • Target = $1,000,000
  • Scenario 3
    • Beginning balance = 3,000
    • Annual contribution = 3,000
    • Stock/Bond allocation = 60%/40%
    • Target = $2,000,000
  • Scenario 4
    • Beginning balance = 3,000
    • Annual contribution = 3,000
    • Stock/Bond allocation = 80%/20%
    • Target = $2,000,000
  • For each scenario, answer the following questions:
    • What is the Max, Min, Mean, Median and Standard Deviation of end balances
    • What is the probability of reaching the target?

• Prepare a Spreadsheet file
  • You’ll use a Google Sheets file to document and explain your simulation
  • Make it easy to understand and navigate
• Create a Video
  • You will provide a brief tour of your spreadsheet and explain your simulation and the 4 scenario runs.
  • Your video should be approximately 3-4 minutes in length. It’s okay if it’s a little longer or a little shorter.
  • It doesn’t need to be fancy. Just you speaking clearly and explaining your simulation + results.
  • You can use Zoom or some other screen recording setup OR you can simply use your phone’s video recorder.
  • Recently, some students in other classes have used PowerPoint’s video recording with very good results.
  • You need to appear on camera, at least for a few seconds.
• Submit links to your Spreadsheet and your Video
  • Upload your video to YouTube or Google Drive or some other hosting location and save the link.
  • Share your Google Sheets spreadsheet and save the link.
  • Submit both the video link and the Google Sheets link via Google Forms