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

using ms-excel ther daily return=r=ln(ending price/ beginning price)

using ms-excel the following variance, standard deviation, correlation and co-variance is calculated

					retrun=r=
	IBM	CSCO	S&P		IBM	CSCO	S&P
Apr24,2018	169.78	28.82	2717.69	Apr24,2018	-0.00271	0.00487	0.001753
	170.24	28.68	2712.93		0.029084	0.002094	0.001834
	165.36	28.62	2707.96		0.006674	-0.00244	0.003948
	164.26	28.69	2697.29		-0.0115	0.007347	-0.00115
	166.16	28.48	2700.4		0.033599	0.019859	0.007143
	160.67	27.92	2681.18		-0.01519	-0.02406	-0.00884
	163.13	28.6	2704.99		-0.00611	0.012313	-0.00061
	164.13	28.25	2706.63	Apr15,2018	0.011212	0.015698	0.003994
Apr14,2018	162.3	27.81	2695.84	mean=	0.005632	0.004459	0.001009
				variance=	0.000287	0.000163	2.01E-05
				std. dev.	0.016952	0.01275	0.004482

Covariance
	IBM	CSCO	S&P
IBM	0.000287
CSCO	0.00011	0.000163
S&P	5.82E-05	4.6E-05	2.01E-05
Correlation
	IBM	CSCO	S&P
IBM	1
CSCO	0.508733	1
S&P	0.765878	0.805207	1