Question

1. The Excel file Stock Data contains monthly data for several stocks and the S&P 500 Index (i.e., the market).
   1. Compute Beta for NT and the S & P 500 using the Covariance/Variance relationship.
   2. Compute Beta for NT and the S & P 500 using the EXCEL slope function.
   3. Compute the equation: R_i= a_i + b_iR_MKT for NT.
      1. Compute R² for this equation.
      2. Compute the t-statistic for a.
         1. Is the t-statistic for a Significant or Not Significant?
      3. Compute the t-statistic for β.
         1. Is the t-statistic for β Significant or Not Significant?

NT
-3.23%
16.66%
-0.72%
2.16%
13.38%
10.56%
-3.93%
5.85%
-2.76%
5.11%
1.08%
3.74%
2.06%
-4.55%
17.46%
-0.45%
-1.81%
-11.98%
-2.09%
12.84%
-2.37%
9.71%
-1.77%
5.86%
6.81%
11.55%
7.14%
-2.67%
8.90%
0.31%
-1.57%
6.69%
4.18%
3.15%
3.89%
2.94%
-6.75%
-9.47%
-4.00%
-5.77%
-5.10%
-2.51%
-6.62%
22.05%
7.42%
3.30%
0.58%
1.16%
-0.72%
2.16%
13.38%
3.74%
2.06%
-4.55%
6.81%
11.55%
7.14%
-6.75%
7.42%
-9.47%

S&P 500
2.40%
5.20%
4.00%
-0.50%
9.00%
1.90%
-0.40%
-2.30%
2.10%
2.40%
-6.70%
1.30%
2.60%
-2.50%
9.80%
-0.70%
-0.30%
-9.00%
-4.90%
-0.40%
6.40%
2.70%
4.40%
7.20%
2.40%
0.30%
4.30%
-4.60%
4.70%
2.40%
-1.60%
1.30%
-4.00%
11.40%
-1.90%
1.30%
-2.00%
2.90%
0.50%
-1.50%
4.00%
-2.00%
1.20%
0.40%
3.40%
1.30%
0.70%
1.40%
4.00%
-0.50%
9.00%
1.30%
2.60%
-2.50%
2.40%
0.30%
4.30%
-2.00%
3.40%
2.90%

Answer 1

Ans a)- The first is to use the formula for beta, which is calculated as the covariance between the return (r_a) of the stock and the return (r_b) of the index divided by the variance of the index (over a period of three years).

beta formula in excel BETA = COVAR (A2: A61; B2: B61) / VAR (B2: B61)

following results are draw

Covariance(NT,SP)	0.001141
		=	0.791566
Variance of S&P	0.001441

Ans b)-

Beta for NT and the S & P 500 using the EXCEL slope function.

BETA FORMULA = SLOPE (B2: B61; A2: A61)

for NT =	0.230584

Ans c)- following are the output of excel when we apply the regression analysis

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.430832
R Square	0.185616
Adjusted R Square	0.171575
Standard Error	0.064566
Observations	60
ANOVA
	df	SS	MS	F	Significance F
Regression	1	0.055109	0.055109	13.21949	0.00059
Residual	58	0.24179	0.004169
Total	59	0.296899
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	0.013434	0.008909	1.508002	0.136982	-0.0044	0.031267	-0.0044	0.031267
X Variable 1	0.804983	0.221401	3.635862	0.00059	0.361801	1.248164	0.361801	1.248164

1) Note that here we have taken NT=Y as a dependent variable and S&P=X as a independent variable

y = 0. 804 x + 0.0134
R² = 0.185

2) the t-statistic for a= intercept.is 1.508002

	Coefficients	Standard Error	t Stat	P-value
Intercept	0.013434	0.008909	1.508002	0.136982

here the p-value for 0.136982 > 0.05 since there is weak evidence against the null hypothesis hence we accept the null hypothesis therefore it is Not significant.

3) t-statistic for β. = 3.635862

X Variable

0.804983

0.221401

3.635862

0.00059

here the p-value for 0.00059< 0.05 since there is strong evidence against the null hypothesis hence we reject the null hypothesis therefore it is significant.

Screenshot-

Note- if it helps you then please appreciate the work.