Question

Year 2015 2016 2017 5 Exp Return 6 Std Dev 7 Covariance 8 Correlation Stock 1 13.00% 17.00% 8.00% 12.67% 3.68% 0.002411 Stock 2 -7.00% 14.00% -3.00% 1.33% 9.10% Which formula/function is the correct one to calculate the correlation in B8? =CORREL(B2:B4,C2:C4) All of the above =B7/(16*C6) =((B2-B5)*(C2-C5)+(B3-B5)*(C3-C5)+(B4-B5)*(C4-C5))/(B6*C6*COUNT(A2:44))

Answer 1

We know,
Let the return of Stock 1 be x and Stock 2 be y
Covariance= (∑x-Expected return of Stock 1)(y-Expected return of Stock 2))/n
where,
n= number of years
Correlation coefficient= Covariance/(Sd of Stock 1*Sd of Stock 2)
So, the answer will be all the above.
The same can be summarised as below:
Option A= The correl function of excel requires the return of both the stocks as input i.e. Array 1 and Array 2= Hence this option is correct
Option C= As per the correlation coefficient cited above, this is also correct
Option D= This option is first calculating covariance and than coefficient of variance, hence the referencing in this case is also true.
Answer: Option B