Discuss the below:
Create an Excel optimization file with good modeling techniques.
Portfolio Optimization Problem
Q: The annual returns for three companies over the last 12 years are given below, where the return for year n is defined as:
(closing price,n) - (closing price,n-1) + (dividends,n) / (closing price,n-1)
Year IBC NMC NBS
1 11.2% 8.0% 10.9%
2 10.8% 9.2% 22.0%
3 11.6% 6.6% 37.9%
4 -1.6% 18.5% -11.8%
5 -4.1% 7.4% 12.9%
6 8.6% 13.0% -7.5%
7 6.8% 22.0% 9.3%
8 11.9% 14.0% 48.7%
9 12.0% 20.5% -1.9%
10 8.3% 14.0% 19.1%
11 6.0% 19.0% -3.4%
12 10.2% 9.0% 43.0%
Part a: Using Excel, determine for each company the estimated mean return and standard deviation. Also, calculate the estimated correlations and covariances between the companies' returns.
Hint: Use the Excel functions AVERAGE, STDEV, and CORREL to determine the means, standard deviations and correlations. The covariances can then be calculated from the correlations (as in the class example). Click on fx in the Formula Bar for help with using these Excel functions.
Part b: Using the three stocks, a financial planner would like to create the least risky portfolio with minimum of a 12% expected return.
Implement a spreadsheet model to determine the desired portfolio. Discuss the solution and highlight exactly how risk is minimized in the optimal portfolio, while achieving the desired return.
Part c: Either by repeatedly re-running the model, or by using SolverTable, create the chart for the efficient frontier for this portfolio optimization problem. Explain the usefulness of such a chart.