Question: Note: You will need the Analytic Solver Platform add-in to complete the assignment. Assume you recently graduated and accepted a job with a consulting firm.
Note: You will need the Analytic Solver Platform add-in to complete the assignment.
Assume you recently graduated and accepted a job with a consulting firm. Although you like your job, you are also looking forward to retiring at age 65. To ensure your retirement is comfortable, you intend to invest portion of your salary into a retirement fund at the end of each year. Your annual contribution is uncertain: you expect to contribute between a minimum of $1,000 and a maximum of $10,000 each year, but most likely you will contribute around $5,000 annually. While the rates of return are also uncertain, the rates could be modeled as normally distributed random variables with a mean of 7.0% and a standard deviation of 10.0%. Complete the following tasks: Use the spreadsheet found on the Excel file and the Analytic Solver Platform add-in to set up a simulation model. Assign a triangular probability distribution to the cell containing the annual contribution and a normal distribution to the cells containing the return rates. For example, the annual contribution cell should display a statement similar to: =PsiTriangular(M9,N9,O9). Identify the cell representing the ending balance at age 65 (G43) and the cell representing the cumulative contribution at age 65 (H43) as the simulation output cells. Set the number of trials to 1,000 and run the simulation.
1) If you contributed 4,000 each year, would any of the return rates used in the data table result in an ending balance greater than $1,000,000?
O yes O no
2) Enter the value in cell A10.
3) Use Goal Seek to estimate how much money you would need to contribute each year in order to have an ending balance of 2,000,000 dollars at age 65 (Use a return rate of 6.1% in your calculations, When entering your answer, do not use the $ sign).
4) Use the data table to answer the following question: If the return rate is 7,0%, how much do you need to contribute each year in order to have an ending balance of $1,037,845.08? (When entering your answer, do not use the $ sign).
5) Use the data table to answer the following question: How much money would you expect to have when you retire if your annual contribution is 3,000 and the return rate is 5.5%? (When entering your answer, do not use the $ sign).
6) Enter the value in cell S3.
7) If you start contributing 5,000 at age 24, how much money would you expect to have in your retirement fund at age 65? (Use a return rape of 6,1% in your calculations. When entering your answer, do not use the $ sign).
Step by Step Solution
There are 3 Steps involved in it
Get step-by-step solutions from verified subject matter experts
Study Help