Lesson 16: Retirement Savings Portfolio Simulation
April 17, 2024
Review:
- Stock market simulation
- Combined with bonds
Project Assignment:
- Build upon your 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?
- Scenario 1
- Prepare a Spreadsheet file
- You’ll use a Google Sheets file to document and explain your simulation
- Make it easy to understand and navigate
- Be ready to demonstrate to me privately in class
- Optional Extra Credit Opportunity
- Present your Simulation spreadsheet to the entire class
- ~3 minute presentation
- During class on Wed 24 Apr
- Earn up to +2% bonus, applied to overall grade
