Reference no: EM132397248
FIN 3150 - Personal Financial Planning, Metropolitan State College of Denver USA
Assignment - constrained optimization of a portfolio
You may do this assignment individually or with one other person. If you work with another, be sure that both know how to set up the analysis and calculate optimal portfolios.
1. Prices
Obtain monthly closing prices for five stocks from different industries for the last twelve years. Each stock must have traded publicly for the last twelve (so no recent IPO's and no stocks with a bankruptcy filing during that period).
2. Returns
Calculate monthly returns for each stock. (Be sure to account for dividends, e.g., by using the "adjusted closing price" from yahoo finance.)
3. Average return and risk
Determine:
(a) the average monthly return for each stock,
(b) the standard deviation of monthly returns for each stock, and
(c) calculate the correlation coefficient for returns on each pair of stocks.
4. Equally weighted portfolio
Determine the average return and standard deviation for an equally weighted portfolio of the five stocks.
For each of 5-8 use constrained optimization (e.g., the "solver" in excel) to determine the investment in each stock needed to obtain the portfolio described.
For each portfolio, specify:
(a) the weight for each stock in the portfolio,
(b) the average portfolio return and
(c) the portfolio standard deviation.
5. Lowest risk portfolio
Find the portfolio that has the lowest possible risk (that is, the lowest standard deviation).
6. Highest return portfolio
Find the portfolio that has the highest average return.
7. Highest return portfolio with the same risk as the equally weighted portfolio
Find the portfolio that has the highest possible return and the same risk as the equally weighted portfolio (i.e., the same standard deviation as in question 4).
8. Lowest risk portfolio with a return selected by you
Select a specific monthly return that is between the monthly return on the equally weighted portfolio (i.e., from question 4) and the return on the highest return portfolio (i.e., from question 6). Find the portfolio with the selected return that has the lowest possible risk (standard deviation).
9. Comment (briefly) on your findings.
(Turn in the spreadsheet you use to do the calculations with the answers to each question highlighted to make them easy to locate.)