Question: (10 points)
Read Section 11.8 Beta Measure of Financial Risk on page 4 3 6 of your textbook.
This excise is an example of using regression models for the Capital Asset Pricing Model (CAPM). If you are interested in the CAPM, you can look up principle finance textbooks or search the Internet on CAPM.

Download the following three data series:

1. Monthly Microsoft stock prices (MSFT) prices from Yahoo Finance at http://www.yahoo.com/finance.
2. Monthly S&P 500 Index (^GSPC) from Yahoo Finance.
3. Three-month treasury bill rate (TB3MS) http://research.stlouisfed.org/fred2/series/TB3MS?cid=116 from FRED Database.

The sample period for Microsoft and S&P 500 is from January 1, 1990 to May 31, 2016. Use "Adj. Close" prices to calculate the annualized capital returns for Microsoft and for S&P 500 Index (see the procedure in Assignment 2; this assignment has different sample period). For the three-month treasury bill rate, go to the above link on FRED and click "Download data" on the left-hand side of the page and click "Download Data" to open the Excel file. Use only the data between February 1990 and May 2016. The unit of interest rates is in percent. Therefore, don’t calculate the percentage changes (the capital returns) for the three-month treasury bill rate. You organize the data as in the columns A to F in the following table.

1. Use Excel’s Data Analysis to get the descriptive statistics of Microsoft stock prices and S&P500 index; the data are in columns B and C in the above table. Print the descriptive statistics. Don’t print the data.
2. Use Excel’s Data Analysis to get the descriptive statistics of Microsoft stock returns, S&P500 returnsand three-month treasury bill rate; the data are in columns D, E, and F in the above table. Print the descriptive statistics. Don’t print the data.
3. Use Excel’s Data Analysis to get the correlation and the covariance matrices for Microsoft stock prices and S&P500 index. In Excel, click " Data " " Data Analysis " and select " C ovariance " or " C orrelation ." Then select all data in columns B and C. Print the correlation and covariance matrices. The correlation and covariance matrices show the correlation and the covariance for each pair of the two variables.
4. Use Excel’s Data Analysis to get the correlation and the covariance matrices for Microsoft stock returns, S&P500 returns,and three-month treasury bill rate (the data are in columns D, E, and F). Print the correlation and covariance matrices.
5. Use the correlation matrix from part d to determine which pair has the highest correlation.
6. Compare Microsoft stock returns with S&P 500 returns. We would like to test if the mean returns for these two series are the same. Get the Excel printout for the test and conduct the test with the 5% significance level. Note that this is a "paired sample."
7. Estimate a CAPM model using the following procedure.

1. Calculate the excess returns of Microsoft, which is the difference between the Microsoft stock returns and three-month treasury bill rates (MSFTreturns – interest rates). You organize these differences as in the column G in the above table. Don’t print the data.
2. Calculate the excess returns of S&P 500, which is the difference between the S&P 500 returns and three-month treasury bill rate (S&P 500 returns – interest rates). Organize these differences as in the column H in the above table. Don’t print the data.
3. Use the excess returns of Microsoft(MSFT returns – treasure bill rate)as the dependent variable and the excess returns of S&P 500 (S&P 500 returns – treasure bill rate)as the independent variable to get Excel regression results. Print the regression results.

1. Write the regression model for the regression in part g and write the estimated equation.
2. Explain the estimated coefficients for the intercept and the slope from part g . You may check the discussion of the CAPM for their meaning.
3. Test if the intercept in part g is different from zero with the 5% significance level.
4. Test if the slope in part g is greater than one with the 5% significance level. Note that the test statistic is and you cannot use the p -value from Excel printout for the test.

Price: $2.99

Solution: The downloadable solution consists of 7 pages
Deliverable: Word Document