Question

Name	Enbridge Inc.	S&P/TSX Composite Index
1974	-47.18%	-29.78%
1975	-5.83%	5.33%
1976	16.49%	2.81%
1977	6.19%	-1.65%
1978	10.83%	23.95%
1979	0.75%	29.72%
1980	-4.48%	41.29%
1981	-14.06%	-13.92%
1982	67.27%	-2.53%
1983	33.70%	29.29%
1984	3.25%	0.16%
1985	40.94%	13.79%
1986	-12.57%	11.70%
1987	5.75%	-3.49%
1988	0.00%	10.64%
1989	13.90%	20.10%
1990	2.12%	-22.26%
1991	1.04%	16.37%
1992	-20.57%	-7.04%
1993	20.39%	27.35%
1994	-2.96%	-0.17%
1995	13.30%	8.73%
1996	25.18%	25.84%
1997	36.72%	18.98%
1998	19.86%	-6.33%
1999	-10.01%	13.55%
2000	29.14%	31.08%
2001	16.00%	-25.25%
2002	0.00%	-10.51%
2003	17.24%	23.01%
2004	-2.56%	12.28%
2005	38.31%	20.29%
2006	10.35%	16.60%
2007	-0.30%	14.14%
2008	2.95%	-32.08%
2009	5.63%	17.24%
2010	31.99%	16.02%
2011	25.27%	-4.32%
2012	10.86%	-2.38%
2013	16.97%	9.74%
2014	13.53%	9.81%
2015	-0.58%	-7.74%
2016	10.34%	8.14%
2017	-17.97%	9.88%
2018	-7.50%	-4.64%
2019	14.38%	9.90%

This question requires the use of the Excel file ‘Assignment 3 Data’ found on ACORN. In the file, you will find the annual returns for Enbridge Inc. (ENB) and the S&P/TSX Composite Index (which we will use for the market portfolio). All parts of this question are to be done in Excel with your results printed and submitted with the other questions in this assignment.

1. Use the data for each of ENB and the S&P/TSX Composite Index to find the historical 95% confidence interval of each. [10 points]

2. Plot the returns of the market against the returns of ENB. Add a trendline to the graph and determine the beta of the stock from your trendline. [5 points]

3. Determine the variance and average annual return of the market portfolio. [3 points]

4. Determine the correlation coefficient between the market portfolio and ENB. [3 points]

5. What is the calculated beta for ENB using the information derived from parts a, c, and d? How does this compare with your answer to part b? [2 points]

6. The current Government of Canada Benchmark 10 Year Bond Yield (which will be our risk-free rate) is 1.61%. What is the required return on ENB stock according to the CAPM? [2 points]

Answer 1

Ans ) Use the data for each of ENB and the S&P/TSX Composite Index to find the historical 95% confidence interval of each. [10 points]

Enbridge Inc.			S&P/TSX Composite Index
Mean	0.090015217		Mean	0.070356522
Standard Error	0.028135116		Standard Error	0.02411451
Median	0.08265		Median	0.09845
Mode	0		Mode	#N/A
Standard Deviation	0.190821643		Standard Deviation	0.163552563
Sample Variance	0.0364129		Sample Variance	0.026749441
Kurtosis	1.964118812		Kurtosis	0.078019517
Skewness	0.185123257		Skewness	-0.462598435
Range	1.1445		Range	0.7337
Minimum	-0.4718		Minimum	-0.3208
Maximum	0.6727		Maximum	0.4129
Sum	4.1407		Sum	3.2364
Count	46		Count	46
Confidence Level(95.0%)	0.056667033		Confidence Level(95.0%)	0.048569116

Ans) Plot the returns of the market against the returns of ENB. Add a trendline to the graph and determine the beta of the stock from your trendline. [5 points]

Considering linear equation

ENB - beta is 0.0014

Market Beta is -0.0006

Ans) Determine the variance and average annual return of the market portfolio. [3 points] Refer Table

Average Annual Return is Average of returns of Stock and market which is

9% for Enbridge Inc. and 7.04% for Market

to calculate variance we would do x-X where X is average and then (x-X)^2

Ans ) Determine the correlation coefficient between the market portfolio and ENB. [3 points Refer Table

Name	Enbridge Inc.	X- Mean	(X- Mean)^2	S&P/TSX Composite Index	Y- Mean	(Y- Mean)^2	X- Mean * Y-Mean
1974	-47.18%	-56.18%	31.56%	-29.78%	-36.82%	13.55%	20.68%
1975	-5.83%	-14.83%	2.20%	5.33%	-1.71%	0.03%	0.25%
1976	16.49%	7.49%	0.56%	2.81%	-4.23%	0.18%	-0.32%
1977	6.19%	-2.81%	0.08%	-1.65%	-8.69%	0.75%	0.24%
1978	10.83%	1.83%	0.03%	23.95%	16.91%	2.86%	0.31%
1979	0.75%	-8.25%	0.68%	29.72%	22.68%	5.15%	-1.87%
1980	-4.48%	-13.48%	1.82%	41.29%	34.25%	11.73%	-4.62%
1981	-14.06%	-23.06%	5.32%	-13.92%	-20.96%	4.39%	4.83%
1982	67.27%	58.27%	33.95%	-2.53%	-9.57%	0.92%	-5.57%
1983	33.70%	24.70%	6.10%	29.29%	22.25%	4.95%	5.50%
1984	3.25%	-5.75%	0.33%	0.16%	-6.88%	0.47%	0.40%
1985	40.94%	31.94%	10.20%	13.79%	6.75%	0.46%	2.16%
1986	-12.57%	-21.57%	4.65%	11.70%	4.66%	0.22%	-1.01%
1987	5.75%	-3.25%	0.11%	-3.49%	-10.53%	1.11%	0.34%
1988	0.00%	-9.00%	0.81%	10.64%	3.60%	0.13%	-0.32%
1989	13.90%	4.90%	0.24%	20.10%	13.06%	1.71%	0.64%
1990	2.12%	-6.88%	0.47%	-22.26%	-29.30%	8.58%	2.02%
1991	1.04%	-7.96%	0.63%	16.37%	9.33%	0.87%	-0.74%
1992	-20.57%	-29.57%	8.74%	-7.04%	-14.08%	1.98%	4.16%
1993	20.39%	11.39%	1.30%	27.35%	20.31%	4.13%	2.31%
1994	-2.96%	-11.96%	1.43%	-0.17%	-7.21%	0.52%	0.86%
1995	13.30%	4.30%	0.18%	8.73%	1.69%	0.03%	0.07%
1996	25.18%	16.18%	2.62%	25.84%	18.80%	3.54%	3.04%
1997	36.72%	27.72%	7.68%	18.98%	11.94%	1.43%	3.31%
1998	19.86%	10.86%	1.18%	-6.33%	-13.37%	1.79%	-1.45%
1999	-10.01%	-19.01%	3.61%	13.55%	6.51%	0.42%	-1.24%
2000	29.14%	20.14%	4.06%	31.08%	24.04%	5.78%	4.84%
2001	16.00%	7.00%	0.49%	-25.25%	-32.29%	10.42%	-2.26%
2002	0.00%	-9.00%	0.81%	-10.51%	-17.55%	3.08%	1.58%
2003	17.24%	8.24%	0.68%	23.01%	15.97%	2.55%	1.32%
2004	-2.56%	-11.56%	1.34%	12.28%	5.24%	0.28%	-0.61%
2005	38.31%	29.31%	8.59%	20.29%	13.25%	1.76%	3.88%
2006	10.35%	1.35%	0.02%	16.60%	9.56%	0.91%	0.13%
2007	-0.30%	-9.30%	0.87%	14.14%	7.10%	0.50%	-0.66%
2008	2.95%	-6.05%	0.37%	-32.08%	-39.12%	15.30%	2.37%
2009	5.63%	-3.37%	0.11%	17.24%	10.20%	1.04%	-0.34%
2010	31.99%	22.99%	5.28%	16.02%	8.98%	0.81%	2.07%
2011	25.27%	16.27%	2.65%	-4.32%	-11.36%	1.29%	-1.85%
2012	10.86%	1.86%	0.03%	-2.38%	-9.42%	0.89%	-0.17%
2013	16.97%	7.97%	0.63%	9.74%	2.70%	0.07%	0.22%
2014	13.53%	4.53%	0.21%	9.81%	2.77%	0.08%	0.13%
2015	-0.58%	-9.58%	0.92%	-7.74%	-14.78%	2.18%	1.42%
2016	10.34%	1.34%	0.02%	8.14%	1.10%	0.01%	0.01%
2017	-17.97%	-26.97%	7.27%	9.88%	2.84%	0.08%	-0.77%
2018	-7.50%	-16.50%	2.72%	-4.64%	-11.68%	1.36%	1.93%
2019	14.38%	5.38%	0.29%	9.90%	2.86%	0.08%	0.15%
Mean	9.00%			7.04%
Total			163.86%			120.37%	47.37%
Count	46	46	46	46	46	46
Variance			3.64%			2.67%
Correlation Coefficient							0.3373

Please repost for remaining questions as there is space limitation to answer other questions.