The data that attached can be downloaded under CRSP/Quarterly update/Stock-version 2.  The “List of tickers.txt” file contains the list of all the tickers in the recommended portfolio of the Sample Report. More specifically, it includes the tickers of the 5 asset classes, and the list of 20 tickers with the biggest market caps from each asset class (so there are 100 individual tickers in total). The “Individual Stock Returns.csv” file contains monthly return data of all of these tickers going back 20 years. In this assignment, you will

Calculate the average returns and the variance-covariance matrix of the returns on the 5 tickers that correspond to the 5 asset classes, both using monthly and annual returns.

Are the above annual variances and covariances approximately the same to or different from the ones on page 6 of the Wells Fargo Capital Market Assumptions white paper (attached)? If they’re different, what do you think could be the reason? 

Use the weights of the 5 asset classes in the Recommended Portfolio and the average returns found in 1. to calculate the expected return of the Recommended Portfolio. (this could be different from 5.52% given in the Sample Report because that return is based on the forward-looking Capital Market Assumptions)

1. Find the portfolio of the above 5 asset classes that has the same expected return as the one found in 4. and that has the smallest volatility.
2. Calculate the variance-covariance matrix of the 100 tickers that correspond to the 100 individual stocks with the biggest market caps.
3. Find the portfolio of the above 100 stocks that has the same expected return as the one found in 4.  and that has the smallest volatility.
4. Use Excel to solve those questions.