Quantitative Business Analysis
On Sheet 1 of your homework (the first tab), you must put your identification as shown in the syllabus and several emails. You must show your work and all formulas must be present in cells where needed. You should answer all questions on the attached tabs.
Using the historical audit method, determine the probabilities of success for your personal retirement goals. Use your age and estimated retirement spending levels calculate what you will need. You can use your independence calculations from earlier this semester as a template or you can build the model from scratch. You need to answer these questions: 1) How much do you need to have saved at retirement, 2) What is the average max and min ending portfolio values, 3) What is the probability of success, 4) What is the average expected return for your portfolio, and 5) what is the average length the portfolio lasts in retirement? Extra credit: calculate how much you need to save annually in order to reach your target portfolio value at retirement. Use your portfolios average return as the return value on your savings. You only need to use the returns for the Large Cap stocks Hint: Use the spreadsheets I used in class and your original independence caluculation homework as a template for setting up the calculations.
Using the Monte Carlo method, determine the probabilities of success for your personal retirement goals. Run 1000 trials. Use your age and estimated retirement spending levels calculate what you will need. You can use your independence calculations from earlier this semester as a template or you can build the model from scratch.
You need to answer these questions:
1) How much do you need to save,
2) What is the average max and min ending portfolio values,
3) What is the probability of success,
4) What is the average expected return for your portfolio, and
5) what is the average length the portfolio lasts in retirement?
6) using the vlookup function, create a blended portfolio using 20% Large Cap, 35% Small Cap and 45% International.
7) Use solver to find the optimal asset class mix that produces the highest expected return (use all the possible asset classes).
Hint: For EC 2, create a separate index that shows the growth of $1. Maximize the ending value.