Suppose an employee starts working after completing her MBA at age 30 at a starting salary of $50,000. She expects an annual salary increase to be at minimum 1%, at maximum 5%, with a uniform
distribution. Her retirement plan requires that she contribute 8% of her salary, and her employer matches that by adding an additional 35% of her contribution. She anticipates an annual return on her retirement portfolio (i.e., return on investment) to be a normal distribution with a mean of 4% and standard deviation of 3.5%. She plans to retire at age 60. Create a spreadsheet model to forecast her average return on investment (i.e., retirement account balance) when she retires at age 60 based on 5,000 simulation trials.
1) What is the expected average balance of her retirement account when she retires at age 60? 2) What is the probability that her ending retirement balance at age 60 will be over $450k?
Generate a histogram or density chart that shows this.
To create a spreadsheet model to forecast the average return on investment for the employee's retirement account, we will need to simulate the scenario multiple times to calculate the average balance. Follow the steps below:
Step 1: Set up the spreadsheet
Open a new spreadsheet and create the following columns:
- Trial Number: Number each trial from 1 to 5000.
- Annual Salary: Start with the initial salary of $50,000 and calculate the salary for each year until retirement (age 60) using the random uniform distribution between 1% and 5%.
- Employee Contribution: Calculate the employee's contribution each year as 8% of the annual salary.
- Employer Contribution: Calculate the employer's contribution each year as 35% of the employee's contribution.
- Total Contribution: Sum the employee and employer contributions.
- Investment Return: Generate a random sample from a normal distribution with a mean of 4% and a standard deviation of 3.5%.
- Investment Earnings: Calculate the investment earnings for each year as the total contribution multiplied by the investment return.
- Account Balance: Calculate the account balance for each year by adding the investment earnings to the previous year's balance.
- Age: Track the employee's age each year.
Step 2: Fill in the spreadsheet
For each trial, fill in the randomly generated values for the annual salary increase, investment return, and calculate the corresponding values for contributions, earnings, and account balance accordingly.
Step 3: Calculate the average balance
When the employee reaches age 60, calculate the average account balance across all 5000 trials by taking the average of the final balance for each trial.
Step 4: Determine the probability of ending balance over $450k
Count the number of trials where the ending account balance exceeds $450k and divide it by the total number of trials (5000) to calculate the probability.
Step 5: Generate histogram or density chart
Create a histogram or density chart using the ending account balances from all 5000 trials to visualize the distribution of retirement account balances.
Please note that the specifics of step 2 and step 3 might slightly vary based on the exact spreadsheet software you are using.