1. Suppose you want to construct a portfolio of five stocks: BAC, RIG, HPQ, IBM, and AAPL and hold it for one month. Suppose you use the data during 20162020 to estimate the parameters, and apply Markowitz portfolio selection theory (without risk-free asset) to construct your portfolio with expected (monthly) return target z = 0.02. You then use the data in 2021 for testing. The data of the monthly returns of these five stocks in 20162021 are provided in HistoricalData.xlsx.

(a) Calculate the sample mean and covariance matrix of the monthly returns of the five stocks from year 20162020 (60 returns for each stock). (Hint: In Excel, one can use COVARIANCE.S function to calculate the sample covariance between two arrays of returns. Repeat this for every pairs of stocks to get the sample covariance matrix. One can also calculate the sample covariance by definition.)

(b) Suppose you estimate the model parameters by the sample mean and covariance matrix calculated in (a). Calculate the weight w of your optimal portfolio. (Hint: In Excel, one can calculate the inverse of a matrix by MINVERSE function, and the matrix multiplication by MMULT function.)

(c) Using the monthly returns of the 5 stocks in 2021, you can obtain the 12 monthly returns of the optimal portfolio with weight calculated in (b). Calculate the sample mean and sample variance of these 12 monthly returns of this optimal portfolio in 2021, and compare them with those of an equal-weighted portfolio (i.e. w = (0.2, 0.2, 0.2, 0.2, 0.2)). Which portfolio is better?