Instructions and Data have been attached. This work is done on EXCEL. Thank you!

Use the data in the spreadsheet Assignement3_Data.xlsx.

1. Worksheet "Returns" contains monthly returns over the 20-year period for three stocks, F, IBM, XOM, as well as monthly returns on RF (risk-free), MKTRF (excess return on market portfolio), and Fama-French factors SMB and HML. Estimate the factor loadings (betas) for the excess returns on F, IBM, and XOM using Fama-French 3-factor model. For the three stocks, compute predicted excess returns and residuals.

1. For F, IBM, and XOM, compute t-statistics for the OLS regression and highlight statistically significant coefficients.

1. Compute the factor covariance matrix V and the residuals covariance matrix (for specific risks of the three stocks) Delta. Next, compute the covariance matrix for the three stocks Sigma in two ways:

(1) using historical excess returns, Sigma = Sigma(historical), and

(2) using the estimates from the 3-factor model, Sigma = BVB' + Delta.

Verify that the two approaches produce the same matrices. Is matrix Delta close to diagonal? Please report all covariance matrices in annualized form.

Now replace Delta with a diagonal matrix diag(Delta), where the main diagonal of diag(Delta) is the same as that for Delta and all other elements are set to zero. Define matrix Sigma1 = BVB' + diag(Delta).

For covariance matrix Sigma, compute the minimum-variance portfolio (MVP) of the three stocks. Similarly, for covariance matrix Sigma1, also compute the minimum-variance portfolio (MVP1). Are MVP and MVP1 similar?

1. Worksheet "Characteristics" contains monthly returns for December, 2003 on six stocks (BUD, DELL, DIS, IBM, INTC, XOM) along with their Market Cap, Price-Earnings ratio, and Price-Book ratio. Run a cross-sectional regression on constant, Size, PE ratio, and BP ratio to estimate factor returns for December, 2003. (Remember to first normalize raw values for Size, PE ratio, and BP ratio.) For the six stocks, compute predicted excess returns and residuals for December, 2003. Finally, using the sensitivity matrix B, compute the factor-mimicking portfolios (matrix W).