Question

Given the monthly returns that follow, find the R², alpha, and beta of the portfolio. Compute the average return differential with and without sign. Do not round intermediate calculations. Round your answers to two decimal places.

Month	Portfolio Return		S&P 500 Return
January	6.0	%	6.3	%
February	-2.6		-3.3
March	-1.5		-1.3
April	2.3		1.7
May	0.7		-0.1
June	-0.9		-0.3
July	0.5		0.8
August	1.5		1.8
September	-0.4		0.2
October	-3.0		-3.5
November	2.9		2.4
December	0.4		-0.1

R²:   

Alpha:   %

Beta:   

Average return difference (with signs):   %

Average return difference (without signs)   %

Answer 1

The above Solution can be computed through excel using Data Solver function by running the regression analysis:-

To do that = Excel->Data->Solver->Regression Analysis

Then select the data as X-axis = Porfolio returns and Y-axis = S&P Returns. You will get something like below:-

R2 = Cell P30 = 0.96

Alpha = Cell P42 = -0.12

Beta = Cell P43 = 1.02

{As regression line is like: Y = (Beta)*X + Alpha +-Error}

Average Return difference(with signs) = Average return of Portfolio - Average return of S&P 500 ; = 0.4917 - 0.3833 ; =0.1084%

Average Return difference(without signs) = Average return of Portfolio(without signs) - Average return of S&P 500(without signs) ; = 1.8917 - 1.8167 ; =0.0750%

Instruction:- To find the average use = AVERAGE() ; To find the absolute value = ABS()