Reference no: EM131875141
The written analysis is to be turn in and the Excel spreadsheet should be emailed to me.
The following exercises should be completed in groups of up to three students. You are not permitted to collaborate in any form with members from other groups. Each group should turn in a single set of typed answers at the beginning of class on 2/21/2018. Note that you are required to submit your Excel files via email.
The file Assignment2 Data.xlsx is available on the course Blackboard site. It contains monthly returns over the period 2003:01 - 2016:12 for the following nine asset classes: (1) U.S. large-cap equity, (2) U.S. mid-cap equity, (3) U.S. small-cap equity, (4) International equity, (5) Real estate, (6) Investment-grade bonds, (7) High-yield bonds, (8) Mortgages, and (9) Commodities.
Your team has been hired as consultants by the portfolio manager for a small regional university's endowment portfolio. The current IPS for the endowment allows for investments in only the first seven asset classes listed above.
The portfolio manager provides you with the following information regarding the portfolio's current strategic asset allocation and expected return forecasts (in % per month) for each asset class:
|
Asset
|
Allocation
|
E(return)
|
|
Large Cap
|
0.15
|
0.64
|
|
Mid Cap
|
0.10
|
0.92
|
|
Small Cap
|
0.10
|
1.00
|
|
Intl
|
0.10
|
0.51
|
|
REIT
|
0.20
|
0.69
|
|
I G Bond
|
0.25
|
0.44
|
|
H Y Bond
|
0.10
|
-0.31
|
|
MBS
|
|
0.32
|
|
Commodity
|
|
0.32
|
The portfolio manager has asked your team to provide some guidance on the endowment's current policy portfolio. The manager is willing to make modifications to the weights listed above subject to the following constraints:
1. The portfolio weight in each asset class must be greater than or equal to zero (i.e., no short sales).
2. The total portfolio weight in common stocks (i.e., the sum of the firstfour asset classes) can not exceed 75%. Using the data file and information outlined above, please complete the following items.
1. Using the Covariance function I provided (varcovar) in Excel construct the historical variance-covariance matrix for the seven asset classes listed in the table above.
2. Given the endowment's current asset allocation and the expected returns listed in the table above, compute the monthly standard deviation and expected return for this portfolio.
3. Using the variance-covariance matrix from question (1), the expected returns listed in the table above, and the portfolio manager's constraints, construct the minimum variance frontier. Find the minimum variance portfolio. Find the tangency portfolio, maximum Sharpe Ratio.
4. Is the current policy portfolio efficient? Why or why not? Identify an efficient portfolio with the same expected return as the current endowment allocation. Report the expected return, standard deviation, and asset-class weights for this portfolio.
5. The portfolio manager would like to consider modifying the IPS to allow for investments in two additional asset classes: Mortgages and Commodities. The expected returns for these asset classes are 0.32% and 0.32%, respectively. Plot the new minimum-variance frontier (nine assets) along with the old frontier (seven assets) from question (3) and the endowment's current policy portfolio.
6. Does the addition of mortgages and commodities as potential investments offer meaningful diversification benefits? Why or why not?
7. Identify an efficient portfolio (from the nine-asset frontier) with the same expected return as the current endowment allocation. Report the expected return, standard deviation, and asset-class weights for this portfolio.
8. Provide a recommendation to the endowment's portfolio manager based on your analysis. (Note: A few sentences here is sufficient.)
Attachment:- Assignment data.rar