Question

Use the data given to calculate daily returns for IBM, CSCO, and, the market index. Use excel to calculate average daily returns, variance, standard deviations, covariances, and the correlations between the two stocks and the index.

Rate of Return Formula Ending Price Beginning Price Date Apr 24, 2018 Apr 23, 2018 Apr 22, 2018 Apr 21, 2018 Apr 20, 2018 Apr 17, 2018 Apr 16, 2018 Apr 15, 2018 Apr 14, 2018 IBM 169.78 170.24 165.36 164.26 166.16 160.67 163.13 164.13 162.30 CSCO 28.82 28.68 28.62 28.69 28.48 27.92 28.60 28.25 27.81 S&p* 2,717.69 2,712.93 2,707.96 2,697.29 2,700.40 2,681.18 2,704.99 2,706.63 2,695.84

Answer 1

	IBM	CSCO	S&P
April 24 2018	169.78	28.82	2171.69
April 23 2018	170.24	28.68	2172.93
April 22 2018	165.36	28.62	2707.96
April 21 2018	164.26	28.69	2697.29
April 20 2018	166.16	28.48	2700.4
April 17 2018	160.67	27.92	2681.18
April 16 2018	163.13	28.6	2704.99
April 15 2018	164.13	28.25	2706.63
April 14 2018	162.3	27.81	2695.84
Average	165.11	28.43	2582.10
Variance	10.31	0.13	54040.52
Standard deviation	3.21	0.36	232.47
Co-variance between two stocks and index	-564.75	-36.13
Correlation between two stocks and index	-0.85	-0.49
Co-variance between two stocks	0.74
Correlation between two stocks	0.76

Excel functions:

Average = AVERAGE( select 9 data values)

Variance = VAR( select 9 data values)

Standard deviation = STDEV(select 9 data values)

Covariance between two stocks and index = COVAR(select 9 data values of IBM in array1 and S&P values in array 2)

Correlation between two stocks and index = CORREL(select 9 data values of IBM in array1 and S&P values in array 2)

Covariance between two stocks = COVAR(select 9 data values of IBM in array1 and IBM values in array 2)

Correlation between two stocks = CORREL(select 9 data values of IBM in array1 and IBM values in array 2)