Question

In: Finance

Year Asset Return S&P 500 Return 1 12 7 2 12 14 3 -1 -3 4...

Year Asset Return S&P 500 Return
1 12 7
2 12 14
3 -1 -3
4 14 9
5 9 8
6 15 15
7 20 12
8 -10 -5
9 7 9
10 12 14

Find the Beta in Excel

Solutions

Expert Solution

Year Asset Return(x) S&P 500(y) (x-(∑x/n))*(y-(∑y/n)) (y-(∑y/n))^2
1 12 7 -3 1
2 12 14 18 36
3 -1 -3 110 121
4 14 9 5 1
5 9 8 0 0
6 15 15 42 49
7 20 12 44 16
8 -10 -5 247 169
9 7 9 -2 1
10 12 14 18 36
Total 90 80 479 430
Expected Return=Total Return/total no. of years
So expected return= 9 8
(90/10) (80/10)
Covariance=((∑(x-(∑x/n))*(y-(∑y/n)))/n)
So, covariance(x,y)=                  47.90
(479/10)
Variance of market= ((∑(y-(∑y/n))^2)/n)                  43.00
(430/10)
Beta=Covariance(x,y)/Variance of market                    1.11
47.9/43

Related Solutions

For the data set 1 4 4 4 7 7 7 9 12 13 14 14...
For the data set 1 4 4 4 7 7 7 9 12 13 14 14 14 15 16 16 17 17 19 20 22 23 23 24 24 25 26 26 27 29 29 31 34 34 35 35 36 37 43 44 44 47 48 48 49 50 52 52 54 54 55 56 56 58 58 59 59 60 64 64 65 66 69 69 73 76 76 77 77 79 (a) Find the 76th percentile. (b)...
Suppose that the S&P 500, with a beta of 1.0, has an expected return of 12%...
Suppose that the S&P 500, with a beta of 1.0, has an expected return of 12% and T-bills provide a risk-free return of 3%. a. What would be the expected return and beta of portfolios constructed from these two assets with weights in the S&P 500 of (i) 0; (ii) .25; (iii) .50; (iv) .75; (v) 1.0? (Leave no cells blank - be certain to enter "0" wherever required. Do not round intermediate calculations. Round your answers to 2 decimal...
Define the following terms:    2 - 401 K 3 - S&P 500 4 - Dow...
Define the following terms:    2 - 401 K 3 - S&P 500 4 - Dow Jones 5 - Mutual fund 6 - Exchange traded fund (ETF)
The following data is provided for the S&P 500 Index: Year Total Return Year Total Return...
The following data is provided for the S&P 500 Index: Year Total Return Year Total Return 1988 16.81% 1998 28.58% 1989 31.49% 1999 21.04% 1990 -3.17% 2000 -9.11% 1991 30.55% 2001 -11.88% 1992 7.67% 2002 -22.10% 1993 9.99% 2003 28.70% 1994 1.31% 2004 10.87% 1995 37.43% 2005 4.91% 1996 23.07% 2006 15.80% 1997 33.36% 2007 5.49% Refer to the information above. Calculate the 20-year arithmetic average annual rate of return on the S&P 500 Index. Question 22 options: 13.04% 11.81%...
For the data set 1 2 3 4 7 7 7 8 11 12 12 15...
For the data set 1 2 3 4 7 7 7 8 11 12 12 15 15 16 17 17 17 18 20 20 22 24 24 25 26 26 26 26 27 30 32 32 33 34 34 36 38 39 43 44 45 46 47 47 48 51 52 52 53 54 54 54 55 56 58 58 59 61 63 65 65 67 69 70 73 75 75 76 77 77 79 80 81 82 82 (a)...
Match No. 1 2 3 4 5 6 7 8 9 10 11 12 13 14...
Match No. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Player A 8 42 56 68 91 123 12 46 57 137 5 80 14 10 19 Player B 38 44 46 59 57 61 48 42 51 39 58 41 55 45 68 1. For the given data set representing the runs scored by two players in last 15 matches, conduct the following analysis: i. Which average you will use to summarize...
Periods ​1% ​2% ​3% ​4% ​5% ​6% ​7% ​8% ​9% ​10% ​12% ​14% ​15% ​16% ​18%...
Periods ​1% ​2% ​3% ​4% ​5% ​6% ​7% ​8% ​9% ​10% ​12% ​14% ​15% ​16% ​18% ​20% 1 0.990 0.980 0.971 0.962 0.952 0.943 0.935 0.926 0.917 0.909 0.893 0.877 0.870 0.862 0.847 0.833 2 0.980 0.961 0.943 0.925 0.907 0.890 0.873 0.857 0.842 0.826 0.797 0.769 0.756 0.743 0.718 0.694 3 0.971 0.942 0.915 0.889 0.864 0.840 0.816 0.794 0.772 0.751 0.712 0.675 0.658 0.641 0.609 0.579 4 0.961 0.924 0.888 0.855 0.823 0.792 0.763 0.735 0.708 0.683 0.636...
Day 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15...
Day 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Number of Aides Absent 5 8 11 15 4 2 7 1 4 6 14 19 3 5 8 In which of the following ranges you can find the Upper Control Limit of the control chart? 0.1427 0.1536 0.1677 Not computable with information available In which of the following ranges you can find the Lower Control Limit of the control chart? Does not exit...
student 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15...
student 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 Test score 67 67 87 89 87 77 73 74 68 72 58 98 98 70 77 Above we have the final averages of the last stats and I want to know if the class average falls within the boundaries of all my statistics classes for the past 20 years. Find the sample size, mean, and standard deviation of the data above (Table 1)....
Company ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14...
Company ID 1 2 3 4 5 6 7 8 9 10 11 12 13 14 1 5330 6352 6340 3399 6566 7376 6882 1842 7362 8205 6222 7695 1681 2283 2 7972 5941 2861 3290 1019 7399 6442 8003 5092 4634 7569 2538 2619 7932 3 8545 6667 2121 2339 4770 7296 3215 7919 2176 9129 1321 695 7945 1673 4 4578 466 4142 8350 7439 6085 8381 5872 2703 6974 702 5846 7176 9179 5 7302 2727 3103...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT