Assignment 1.
In this assignment, you are tasked with constructing a portfolio of five major US firms, Starbucks (SBUX), Costco (COSTCO), Exxon Mobil (XOM), Netflix (NFLX), and General Electric (GE). In order to construct the portfolio, you are given the historical prices and daily returns of these firms, covering the period from 2020 to 2023. This data, as well as a proxy for the market return (the S&P500 index, ^GSPC) and a risk-free asset (^IRX) are provided to you over this sample period.
The key question of interest in this assignment is to examine different weighting schemes, and then to examine the performance of the constructed portfolios out of sample during a holdout period covering the year 2024. There is a solution template for you to paste your answers into, please use that for your submission. You must submit electronically both your solution and Excel spreadsheet as working.
Required:
- Using the data from 2020-2023 (the full four-year in-sample data), estimate the mean, standard deviation, and skewness of the five stocks, as well as the S&P 500 Index and the risk-free asset. Report the statistics in a table, with the assets as columns and the statistics listed as rows in the table. Ensure that the mean and standard deviations are annualized, assuming that there are 252 days in the year. [1]
- Using the Capital Asset Pricing Model, estimate the Betas for each of the five stocks, and use these to create a set of expected returns for each of the five stocks, as well as a vector of idiosyncratic variances. For this question, assume that the risk-free rate is the value at the end of 2023, and report the expected returns in annualized terms. [1]
- Using the full sample of daily data, estimate the sample covariance matrix for the five stocks. Use four decimal places for the answers. Report the answers in annualized terms. [1
- Given the covariance matrix from Q3, construct the minimum variance portfolio, and reports the weights of each stock in the portfolio. [1]
- Using the Single Index Model (CAPM) from part 2, create a covariance matrix with variance terms constructed as total variance from the CAPM and covariance terms estimated from the single index model. Report the covariance matrix from this approach in a 5×5 matrix table. Ensure that the values are reported in annualized terms. [1]
- What is the weights of each asset in minimum variance portfolio if we use the alternative covariance matrix constructed in Q5? [1]
- Using the vector of expected returns computed under the CAPM from Q2, and the risk-free estimate as the last observation of 2023, find the weights of each stock in the tangency portfolio. Use the sample covariance matrix from Q3 in your calculations. [1]
- Plot the Efficient frontier using the asset means from the CAPM (Q2) and the sample covariance matrix from Q3. Take values of the target portfolio return (i.e., ) from 5% to 13% p.a., increasing in units of 1% in expected return. On the same graph, plot the tangency portfolio, the minimum variance portfolio from Q4, and the Tangency portfolio from Q7. Ensure that these points are clearly labelled and distinct from the efficient frontier you have plotted. Then, plot the capital allocation line as a dashed black line.
Next, generate a series of random portfolio weights. To do this, use Excels Random Number Generator (you will need to ensure that the Data Analysis Toolpak is installed). Choose Number of Variables as 5 and Number of Random Numbers as 10. Select Distribution as Uniform and Parameters as between 0 and 1.5. For Random Seed, please input the last four digits of your student ID.
This will generate a series of random numbers. You will need to normalize them (divide by the sum of each five random number draw) and then use these as random portfolio weights. For each of the 10 sets of random portfolio weights, compute the portfolio mean and standard deviation. Using markers of different colour to those used previously, plot these random portfolio mean-standard deviation combinations on your same set of axes. [4]
- Suppose that someone is investing with the tangency portfolio and the risk-free asset as above. Assume that they would like to target a return of 13% p.a. on their portfolio. [2]
- What is the required weight to invest in the risky asset () to achieve a rate of return of 13% per annum.
- What is the Standard Deviation of the portfolio () that achieves the return of 13% per annum?
- What would be the estimated risk aversion coefficient, , for someone taking this position, assuming that they hold a utility function of ?
- Next, we will consider alternative weighting schemes, not based on the optimization of mean-variance alone. Suppose that an investor is mandated to hold a minimum position of (at least) a) 15% and b) 10% in each of the five stocks. Compute the weights of each asset in the minimum variance portfolio, assuming that these constraints must be satisfied. [2]
- More practical portfolio managers may consider other approaches to constructing a portfolio.
- Inverse Volatility: : allocates more weight to assets with lower volatility and less weight to those with higher volatility.
- Maximum Diversification Portfolio: . maximizes the diversification ratio (DR), which is the ratio of the weighted sum of asset volatilities to total portfolio volatility.
- Risk parity: for all Allocates volatility contribution equally across all assets.
The first of these can be found simply from using your estimate of standard deviation. The maximum diversification and risk parity portfolios require the use of Solver (or other numerical method).
For the maximum diversification portfolio, we need to find the value of DR then use solver to maximise that value, given the sum of the weights needs to be 1.
For our risk parity portfolio, we need to find each assets Marginal Contribution to Risk (MCR) which measures how much an individual asset contributes to the overall risk (volatility) of a portfolio when its weight changes. This will aid us in determining whether an asset adds (more or less) risk to the portfolio relative to its weight.
The MCR is calculated from the matrix multiplication of the weight vector and the covariance matrix. Where is the covariance matrix, is the column vector of portfolio weights, and is the portfolio standard deviation given this vector of weights.
This will result in a vector of MCRs (one for each asset). If we multiply this vector (element wise, we can get the TCR (total contribution to risk). This provides us with a decomposed version of the portfolio volatility, such that the sum of the asset TCRs will equal the portfolio volatility. Using our MCR expression, we obtain:
Then, for the risk parity weighting, we would like the TCRs to all be equal. To do this, we get the average TCR (across the stocks) and create a metric of Squared difference of each assets TCR from the average TCR of all five assets). Then we add the squared differences and use Solver to minimize the sum of squared differences (while also requiring that the weights of the portfolio add to 1). This gives us the risk parity portfolio. Generally, assets with a higher volatility will get a lower weight in the portfolio, as they will contribute more to overall portfolio risk.
For this part, create the portfolio of five stocks using these three approaches. [2]
- Next, some final version of portfolios for us to look at, mainly for the purposes of comparison. Suppose that we find the number of shares outstanding (in thousands) [1]
SBUX |
Costco |
GE |
NFLX |
XOM |
|
31/12/2024 |
1,135,800 |
443,942 |
1,073,692.18 |
427,757.10 |
4,353,094.54 |
30/09/2024 |
1,133,500 |
443,126 |
1,082,294 |
427,458.11 |
4,395,094.54 |
30/06/2024 |
1,133,100 |
443,335.02 |
1,084,311.02 |
429,164.62 |
4,442,826.58 |
31/03/2024 |
1,132,700 |
443,504.04 |
1,094,606.68 |
430,964.99 |
3,943,006.87 |
31/12/2023 |
1,132,200 |
443,787 |
1,088,416 |
432,759.58 |
3,971,000 |
- Compute the equally-weighted (and capitalization-weighted portfolios.
- Using the estimated values of Beta from Q2, compute the inverse-beta weighted portfolios (similar to the inverse volatility portfolio, but using beta instead of standard deviation).
- Now, we should have several different weighting schemes to consider. [3]
- Provide a visualization presenting each of the weights of the portfolios in the same graph.
- Calculate the expected returns and volatility of the portfolios, using the CAPM for expected returns, and the sample covariance matrix for the volatility. Present these in the table above with the weights in a summary.
- Next, use the data from 2024, and the portfolio weights that you have created in-sample from the data from 2020-2023 (as in your above table) to create a table of realised returns, volatilities and Sharpe ratios that is the return and standard deviation of the portfolio in 2024. Use the geometric mean of the daily returns in 2024 to estimate the average return, and create a sample covariance matrix from the 2024 data only. Assume that the risk-free rate is the latest value in 2024. [2]
- Provide the table of realized returns, volatilities, and Sharpe Ratios:
- Rank the portfolios (from 1 to 11) based on the realised Sharpe ratio.
- What is the value of A (if there is one) that would be required to prefer the second-ranked portfolio in terms of Sharpe ratio to the first-ranked portfolio.
- Given that we have both initial weights, and realized returns over the 2024 period, we will have new weights of each asset in the portfolio. Stocks that have performed particularly well will have an increase in weight while those that have performed poorly a likely to have a decrease in weight. [3]
- Recompute the asset weights after the returns have been realized, then create a table of Changes in weights (New weight minus old weight). Report these in a table below, as well as the sum of the absolute values of the changes in weights:
- With our new portfolio weights as created, we will need to rebalance our portfolio to meet our criteria. Using the 2024 data to estimate the volatility only, find updated weights for the Inverse Volatility portfolio. Given the new weights at the end of 2024, and the updated weights that are required after re-calculation, what amount of each stock will need to be purchased/sold given this weighting scheme (assume that the portfolio is normalized to $1, so the amounts are weights)?
- Repeat the process for part b, but for the minimum variance portfolio. Assume that you only use the 2024 return data to estimate the sample covariance matrix.
- Given the information in this assignment, in 500 words or less provide a discussion of the relative merits of mean-variance optimization compared with other approaches that we have considered. Describe, with reference to the particular values that you have found with our five-stock scenario here, how various aspects of portfolio management can be improved by measuring and managing risk. You may discuss alternative strategies or aspects of portfolio management that we have not explicitly examined in this assignment. [4]