Question

Following are the price data of Telus and Roger from 2014 - 2016
Date	Telus_Adj_Price	Roger_Adj_price
2014-01-02	31.74	41.01
2014-02-03	31.94	38.13
2014-03-03	32.67	35.58
2014-04-01	33.48	38.92
2014-05-01	32.94	36.92
2014-06-02	35.54	37.91
2014-07-01	35.44	38.05
2014-08-01	32.76	36.38
2014-09-02	34.36	38.08
2014-10-01	32.63	35.45
2014-11-03	33.90	35.77
2014-12-01	36.39	38.42
2015-01-02	34.36	37.15
2015-02-02	33.52	34.22
2015-03-02	33.74	33.71
2015-04-01	32.32	32.77
2015-05-01	33.45	34.26
2015-06-01	32.94	33.17
2015-07-01	34.01	34.83
2015-08-03	33.39	34.29
2015-09-01	31.17	32.41
2015-10-01	31.02	34.03
2015-11-02	32.90	39.21
2015-12-01	31.43	38.09
2016-01-04	27.23	34.31

a) Calculate Telus's and Roger's monthly return for all months in the sample.

b) Calculate the average return and standard deviation of the monthly return for each company

c) Estimate the monthly expected return of both companies with 95% level of confidence. Convert the bounds to annual returns. Comment on the accuracy of the estimates.

d) Calculate the covariance and correlation between the companies. Graph the monthly return of Roger's over Telus's.

Answer 1

Soln: Please refer here the table for monthly returns, we have calculated the same by formula = (price of month2- price of month1)/price of month1

Date	Telus_Adj_Price	Roger_Adj_price	Telus Monthly return	Roger Monthly returns
02-01-2014	31.74	41.01	NA
03-02-2014	31.94	38.13	0.63%	-7.02%
03-03-2014	32.67	35.58	2.29%	-6.69%
01-04-2014	33.48	38.92	2.48%	9.39%
01-05-2014	32.94	36.92	-1.61%	-5.14%
02-06-2014	35.54	37.91	7.89%	2.68%
01-07-2014	35.44	38.05	-0.28%	0.37%
01-08-2014	32.76	36.38	-7.56%	-4.39%
02-09-2014	34.36	38.08	4.88%	4.67%
01-10-2014	32.63	35.45	-5.03%	-6.91%
03-11-2014	33.90	35.77	3.89%	0.90%
01-12-2014	36.39	38.42	7.35%	7.41%
02-01-2015	34.36	37.15	-5.58%	-3.31%
02-02-2015	33.52	34.22	-2.44%	-7.89%
02-03-2015	33.74	33.71	0.66%	-1.49%
01-04-2015	32.32	32.77	-4.21%	-2.79%
01-05-2015	33.45	34.26	3.50%	4.55%
01-06-2015	32.94	33.17	-1.52%	-3.18%
01-07-2015	34.01	34.83	3.25%	5.00%
03-08-2015	33.39	34.29	-1.82%	-1.55%
01-09-2015	31.17	32.41	-6.65%	-5.48%
01-10-2015	31.02	34.03	-0.48%	5.00%
02-11-2015	32.90	39.21	6.06%	15.22%
01-12-2015	31.43	38.09	-4.47%	-2.86%
04-01-2016	27.23	34.31	-13.36%	-9.92%

For average return with the help of excel we have calculated , For Telus's average return in this period = -0.51%

For Roger's, return in this period = -0.56%

Again with the help of excel formula STDEV(all returns of the months) , we calculated the Telus's std = 5.15%

and for Roger's = 6.21%

c) Monthly expected return for Telus's using formula E(r) = average return + 95% z value * standard deviation

E(r)_T = -0.51 + 1.645 * 5.15 = 7.96%

E(r)_R = -0.56 + 1.645 *6.21 = 9.65%

Please note z value of 95% confidence interval = 1.645

d) Covariance is defined as the measure how one variable changes with the change in other here the return of Telus's will affect with the return of roger's . So, covariance will be calculated using the standard devoation table in excel and using formula = Covar(array1, array2)

Select the array1 = all return values of Telus's and array 2 = all return values of Roger's

Covariance = 0.22%

Similarly we can calculate the value of Correlation using correlation formula in excel = Correl(array1,array2)

Correlation = 0.727