A	B	C	D	E	F	G	H
1	Date	IBM	SP500
2	1/1/1990	24.65625	329.08	IBM Returns	SP500 Returns	TB3MS	IBM EXRTN	SP500 EXRTN
3	2/1/1990	25.96875	331.89	=(b3 - b2)/b2 * 1200	=(c3 - c2)/c2 * 1200	7.74	=d3 - f3	=e3 - f3
4	3/1/1990	26.53125	339.94	25.99278	29.10603	7.9	18.09278	21.20603
5	4/1/1990	27.25	330.8	32.50883	-32.2645	7.77	24.73883	-40.0345
-	---	---
381	8/1/2021	140.34	4522.68	-5.27819	34.78839	0.05	-5.32819	34.73839

a. Use Excel's data analysis to get descriptive statistics of IBM's stock prices and S&P500 index; the data are in columns B and C in the above table.

b. Use Excel's Data Analysis to get descriptive statistics for IBM's stock returns, S&P500 returns and three month treasury bill rate. The data are in columns D, E, and F in the above table. Print the descriptive stats, NOT the data.

c. Use Excel's data analysis to get the correlation and covariance matrices for IBM's stock prices and S&P500 index. In Excel click "data" then "data analysis" then select covariance or correlation. Select all data from columns B and C. Print the correlation and covariance matrices.

d. Use Excel's data analysis to get the correlation and to covariance matrices for IBM's stock returns, S&P500 returns, and three month treasury bill rate (data are in columns D, E, and F). Print the correlation and covariance matrices.

e. Use the correlation matrix from part d to determine which pair has the highest correlation.

f. Compare IBM's stock returns with S&P500 returns. We want to test if the mean returns for these two series are the same. Use Excel's "t-test: paired two sample for means" in data analysis to get the printout for the test and conduct the test with the 5% significance level.

g. Estimate a CAPM model using the following procedure: 1)calculate the excess returns of IBM (IBM Returns - Interest Rates) Put the difference in column G but do not print the data. 2) calculate the exess of S&P500 returns (S&P500 returns - Interest Rates) Put the differences in column H but do not print the data. 3) USe the excess returns of IBM (IBM Returns - treasury bill rate) as the dependent variable and excess returns of S&P500 (S&P500 Returns - treasury bill rate) as the independent variable to get Excel regression results. Print the regression results.

h. Write the regression model for the regression in part g and write the estimated equation.

i. Explain the estimated coefficients for the intercept and the slope in part g.

j. Test if the intercept in part g is different from zero with a 5% significance level.

k. Test if the slope in part g is greater than 1 with the 5% significance level. Note that the t statistic is t = b₁ -1/s_b1