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 5x5 matrix table. Ensure that the values are reported in annualized terms. [1]

What is the weight 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 Excel’s 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 asset’s ‘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 asset’s 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]

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]

Hints and answering frequently asked questions for the assignment:

Overall – if not sure, do something ‘sensible’ – you could use some space to explain that you thought something isn’t clear or why you said four decimal places (use that value as a guide if not sure). Answers should have enough detail (clear, within the template) to show what you’ve done. Think what your boss at work might expect you to report for a client. If you happen to make a mistake in one part, you should be able to still answer the others so don’t lose momentum.

You need to report the statistics for the returns of the stocks, not the prices.

When estimating the betas, we subtract the (daily) risk-free rate from the returns to get ‘excess returns.’ We need to use the ‘annualised’ risk-free rate to estimate ‘Expected Returns’ via the CAPM. What is the end of 2023 (choose a sensible value, if not sure, examine in more detail what ^IRX is and how it is quoted). We don’t want to add a daily interest rate to other annualized estimates when coming up with expected returns under the CAPM. When we calculate idiosyncratic variances, we can use our variance decomposition formula or examine the variance of residuals from the regression. Given the information in the question, you can report the idiosyncratic variance of the returns of the assets or the excess returns. Remember, we can annualize variance by multiplying by 252, and the decomposition works on variances, not volatilities.

When it states the ‘full sample’ of daily data, we are referring to (of course) the ‘in-sample’ data as has been described in the preamble. Apologies for any confusion with the sheet names. You can use the COVARIANCE.S function with the ‘INDEX’ method as shown in tutorials or manually do this for each pair of stocks.

You can use either the matrix approach or the Solver approach to find the Minimum Variance Portfolio. Both should give you the same answer.

When it says ‘Part 2’ this means question 2. We need to create a diagonal (i.e., variance terms) that is the total variance and the off-diagonal terms (i.e., covariance terms) that are based on the single index model. If you missed how this was explained in lectures, we used the formula to find the covariance between two stocks under the single index model. You can use your idiosyncratic variance estimates, turned into a 5x5 diagonal matrix, and then create the covariance matrix (as a whole) using the single index model, and add together. Either way, this should yield a different covariance matrix to that in Q3. Why would we want to have different estimates of the covariance matrix? One might yield more accurate values (out of sample). Or, practitioners might put different weights on the systematic and firm-specific components of risk.

If we have a different covariance matrix, we should get different weights to that found in Q4.

Here, we are using the Expected returns from the CAPM to find the tangency portfolio (i.e., as the vector of means). We have covered this in tutorials, but you can watch the video as to how to find the tangency portfolio. You can also use Solver to find the tangency portfolio – you will need to maximise the Sharpe ratio.

You can use our method in class (e.g., finding the values of A, B, and C) to find values of the portfolio variance given a target return This approach can also be used to find the optimal weights. Alternatively, you can set the value of   as an additional constraint in Solver, but you will need to run this several times to get the portfolio. Both approaches should give you the same values. We ideally want to plot the entire range from 5% to 13% (not just parts on the top of the efficient portfolio) but if you have already plotted only the top part, that is ok. Please make the figure look presentable and large enough to be read clearly (after all, graphs are used to help explain your processes and for the purposes of communication.) Try using “Data Labels” to clearly mark points of interest such as the minimum variance portfolio.

You need to report the weight in the tangency portfolio, not the weights that this would correspond to in each of the stocks. Whether this leads to a realistic value of for this utility function is not the question.

In this question you will need to use Solver. Note that you can actually set multiple cells to be greater than or less than a particular value in one single “Add Constraint”. If you compare the constrained portfolios to unconstrained values, hopefully you can see that some assets might be affected by requiring a minimum position.

These are more practitioner-oriented schemes for creating portfolios. Notice that they aren’t using the mean values or expected returns in construction. Follow the steps to get the Risk Parity Portfolio. It might be interesting in later parts of the assignment to compare and contrast this to the ‘inverse volatility’ case – why do you think that is?

You can use these shares outstanding data points at the end of 2023 – you could check how stable your weights are at other points later on if you like.

How you want to present the different weights is up to you, but please make it clear which portfolio is which. Again, think of what is going to best communicate the point.

To get realized returns you can take the geometric average of the daily returns (e.g. could use = PRODUCT(1+(range of cells))-1.) This will give the compounded daily returns over the full year (remember, this is the ‘out-of-sample’ year). Obviously, portfolios with different weights will give us different realized returns – you can sort and number them or try out the = RANK function to get them in the desired order.

Based on the realized returns, some ‘dollar’ positions would be worth more than others after the year. E.g. if we’re equally-weighted, our dollar value in stocks that have performed well will be higher than the dollar values in stocks that have performed poorly. A portfolio manager would likely need to ‘rebalance’ to get back in line with their preferred strategy. So it would be useful to know the weightings of stocks (before we rebalance), to know how much we will need to be purchasing and selling of each one to get back to our strategy. This is what we will report in part a. Of course, given that weights add to 1, we cannot just add the changes together (that should sum to zero) so we take the ‘absolute value’ of the changes in each asset and add them together. Why do you suppose that we will need to do this? If we have assumed the portfolio is $1, then we can just adjust the dollar values by ‘fractions of dollars’ (as in our changes in weights will reflect dollar values). We don’t need to buy or sell round numbers of the stocks in this case.

This is fairly open ended, you might want to explain the relative performance of the different portfolios that we’ve found. You can use some academic references to support your case as well.