Lab for Lecture 9: Review Session
Optimal portfolios and asset allocation with ETF data
1 Constructing the Optimal Portfolio and Asset Allocation
1.1 Data
Use the etf_returns.xlsx dataset to obtain monthly returns on the following six ETFs:
| Asset Class | Ticker | ETF Name | Why investors use it |
|---|---|---|---|
| Nominal Treasuries (medium-term) | IEF | iShares 7–10 Year Treasury Bond ETF | Benchmark intermediate-duration interest-rate exposure |
| High-yield corporate bonds | HYG | iShares High Yield Corporate Bond ETF | Exposure to credit risk and business-cycle sensitivity |
| U.S. stocks (large-cap) | SPY | SPDR S&P 500 ETF Trust | Core U.S. equity benchmark; market portfolio proxy |
| U.S. stocks (small-cap) | IWM | iShares Russell 2000 ETF | Small-cap risk and growth exposure |
| Gold | GLD | SPDR Gold Shares | Inflation hedge, crisis hedge, and store of value |
| Real estate | VNQ | Vanguard Real Estate ETF | Exposure to publicly traded real estate (REITs) |
1.2 Part 1: Risk and Return
Using the monthly returns data:
- Calculate the mean monthly return and standard deviation of monthly returns for each of the six ETFs (IEF, HYG, SPY, IWM, GLD, VNQ).
- Use 0.003 for your risk free rate.
- Calculate the Sharpe ratio for each ETF.
- Calculate the variance-covariance matrix for the six ETFs.
1.3 Part 2: Tangency Portfolio
Using the results from Part 1:
- Find the optimal risky portfolio using numerical optimization (Solver in Excel).
- Report the weight of each ETF in the tangency portfolio.
- Compare the Sharpe ratio of the tangency portfolio to the Sharpe ratios of the individual ETFs.
1.4 Part 3: Capital Allocation and the Complete Portfolio
Building on the tangency portfolio from Part 2:
- Using the optimal capital allocation formula, calculate the weight in the tangency portfolio (versus the risk free asset) for investors with risk aversion coefficients \(A\) = 1, 2, 3, 4, 5, and 6.
- For each value of \(A\), calculate the final weight in each of the six ETFs and the risk-free asset.
- Report the expected return and standard deviation of each complete portfolio.
1.5 Part 4: Constrained Optimization
Using Solver in Excel (or any optimization tool):
- Re-solve for the tangency portfolio weights with the constraint that all weights must be non-negative (no short selling allowed) and no single ETF can have a weight larger than 40%.
- Report the new optimal weights and compare the Sharpe ratio of this constrained tangency portfolio to the unconstrained version from Part 2.
- Calculate the optimal complete portfolio for \(A\) = 4 using the constrained tangency portfolio.