Form a correlation matrix and a variance-covariance matrix from the excess returns of the three stocks and
Fantastic news! We've Found the answer you've been seeking!
Question:
Form a correlation matrix and a variance-covariance matrix from the excess returns of the three stocks and the Russell 1000.
Calculate betas for all three of the stocks in two ways: (i) using the entries from the variance-covariance matrix, and (ii) using the SLOPE() function.
Link to Excel File: https://docs.google.com/spreadsheets/d/1WjtfWuJuzDOWiXEtrpEaItooKOGvXF4NyGtjnJDpKbU/edit#gid=0
Transcribed Image Text:
Share Price and Yield Data BBY Best Buy 30.45 27.93 32.39 32.44 32.08 32.17 30.60 33.60 38.48 38.18 38.91 45.70 Date 12/31/15 1/31/16 2/29/16 3/31/16 4/30/16 5/31/16 6/30/16 7/31/16 8/31/16 9/30/16 10/31/16 11/30/16 12/31/16 1/31/17 2/28/17 3/31/17 4/30/17 5/31/17 6/30/17 7/31/17 8/31/17 9/30/17 10/31/17 11/30/17 12/31/17 1/31/18 2/28/18 3/31/18 4/30/18 5/31/18 6/30/18 7/31/18 8/31/18 9/30/18 10/31/18 11/30/18 12/31/18 1/31/19 2/28/19 3/31/19 4/30/19 5/31/19 6/30/19 7/31/19 8/31/19 9/30/19 10/31/19 11/30/19 12/31/19 1/31/20 2/29/20 3/31/20 4/30/20 5/31/20 6/30/20 7/31/20 8/31/20 9/30/20 10/31/20 11/30/20 12/31/20 42.67 44.52 44.13 49.15 51.81 59.39 57.33 58.34 54.26 56.96 55.98 59.61 68.47 73.06 72.44 69.99 76.53 68.25 74.58 75.03 79.56 79.36 70.16 64.59 52.96 59.24 68.84 71.06 74.41 62.67 69.73 76.53 63.65 68.99 71.83 80.64 87.80 75.65 57.00 76.73 78.09 87.27 99.59 110.91 111.29 111.55 108.80 99.79 114.93 NKE Nike 62.50 62.01 61.59 61.47 58.94 55.22 55.20 55.50 57.64 52.65 50.18 50.07 50.83 52.90 57.16 55.73 55.41 52.99 59.00 59.05 52.81 51.85 54.99 60.42 62.55 68.22 67.03 66.44 68.39 71.80 79.68 76.91 82.20 84.72 75.04 75.12 74.14 81.88 85.73 84.21 87.83 77.14 83.95 86.03 84.50 93.92 89.55 93.49 101.31 96.30 89.38 82.74 87.18 98.58 98.05 97.61 111.89 125.54 120.08 134.70 141.47 STT State Street 66.36 55.73 54.78 58.52 62.30 63.06 53.92 65.78 70.24 69.63 70.21 78.80 77.72 76.20 79.71 79.61 83.90 81.46 89.73 93.23 92.49 95.54 92.00 95.35 97.61 110.17 106.15 99.73 99.78 96.11 93.09 88.31 86.91 83.78 68.75 73.02 63.07 70.90 71.87 65.81 67.66 55.25 56.06 58.09 51.31 59.19 66.07 75.10 79.10 68.11 53.27 63.04 60.96 63.55 63.79 68.09 59.33 58.90 70.48 72.78 74.17 RUI Russell 1000 5796.184 5521.7100 6170.5150 6233.9150 6337.7490 6354.6020 6559.5550 6603.0760 6585.0350 6479.2330 6740.9920 6829.8910 6999.1720 7266.2060 7241.3780 7347.9370 7442.1340 7495.3270 7602.8240 7662.7620 7786.9020 8001.2940 8242.8290 8308.8020 8796.1390 8470.4620 8246.4150 8302.3120 8516.0160 8569.5820 8825.5770 9165.8880 9159.0930 8548.3600 8712.7960 7891.1890 8587.5160 8879.9080 9001.2140 9392.7100 8798.1260 9351.9220 9556.6940 9378.2100 9504.1290 9745.7430 10117.0310 10350.8270 10413.1170 9573.1430 8249.9350 9390.7000 9874.8590 10063.0130 10698.3390 11479.5220 11008.9290 10800.5880 12063.4620 12512.9690 12903.5800 1-Year T-Bill Annual YTM 0.5200 0.6400 0.5400 0.5700 0.5400 0.5000 0.5500 0.5800 0.6400 0.7100 0.8400 0.8000 0.7900 0.9800 1.0100 1.0900 1.1700 1.1900 1.2000 1.2500 1.3600 1.5100 1.6500 1.7500 1.9000 2.0000 2.0800 2.2000 2.2500 2.3100 2.3600 2.4700 2.5600 2.6000 2.5700 2.5000 2.4700 2.4100 2.3400 2.2700 1.9400 1.9100 1.7300 1.7500 1.5700 1.5300 1.5100 1.4900 1.3700 0.3200 0.1800 0.1600 0.1800 0.1500 0.1300 0.1300 0.1300 0.1200 0.1000 0.1000 0.0700 Best Buy Ex-Dividend Date 3/15/16 6/10/16 9/9/16 12/6/16 3/20/17 6/13/17 9/18/17 12/6/17 3/21/18 6/13/18 9/17/18 12/10/18 3/19/19 6/12/19 9/18/19 12/16/19 3/18/20 6/10/20 9/14/20 12/14/20 Amount 0.28 0.28 0.28 0.28 0.34 0.34 0.34 0.34 0.45 0.45 0.45 0.45 0.50 0.50 0.50 0.50 0.55 0.55 0.55 0.55 Nike Ex-Dividend Date 3/3/16 6/2/16 9/1/16 12/1/16 3/2/17 6/1/17 8/31/17 12/1/17 3/2/18 6/1/18 8/31/18 11/30/18 3/1/19 5/31/19 8/30/19 11/29/19 2/28/20 5/29/20 8/28/20 12/4/20 Amount 0.16 0.16 0.16 0.18 0.18 0.18 0.18 0.20 0.20 0.20 0.20 0.22 0.22 0.22 0.22 0.25 0.25 0.25 0.25 0.28 State Street Ex-Dividend Date 3/30/16 6/29/16 9/29/16 12/29/16 3/30/17 6/29/17 9/29/17 12/29/17 3/29/18 6/29/18 9/28/18 12/31/18 3/29/19 6/28/19 9/30/19 12/31/19 3/31/20 6/30/20 9/30/20 12/31/20 Amount 0.34 0.34 0.38 0.38 0.38 0.38 0.42 0.42 0.42 0.42 0.47 0.47 0.47 0.47 0.52 0.52 0.52 0.52 0.52 0.52 Share Price and Yield Data BBY Best Buy 30.45 27.93 32.39 32.44 32.08 32.17 30.60 33.60 38.48 38.18 38.91 45.70 Date 12/31/15 1/31/16 2/29/16 3/31/16 4/30/16 5/31/16 6/30/16 7/31/16 8/31/16 9/30/16 10/31/16 11/30/16 12/31/16 1/31/17 2/28/17 3/31/17 4/30/17 5/31/17 6/30/17 7/31/17 8/31/17 9/30/17 10/31/17 11/30/17 12/31/17 1/31/18 2/28/18 3/31/18 4/30/18 5/31/18 6/30/18 7/31/18 8/31/18 9/30/18 10/31/18 11/30/18 12/31/18 1/31/19 2/28/19 3/31/19 4/30/19 5/31/19 6/30/19 7/31/19 8/31/19 9/30/19 10/31/19 11/30/19 12/31/19 1/31/20 2/29/20 3/31/20 4/30/20 5/31/20 6/30/20 7/31/20 8/31/20 9/30/20 10/31/20 11/30/20 12/31/20 42.67 44.52 44.13 49.15 51.81 59.39 57.33 58.34 54.26 56.96 55.98 59.61 68.47 73.06 72.44 69.99 76.53 68.25 74.58 75.03 79.56 79.36 70.16 64.59 52.96 59.24 68.84 71.06 74.41 62.67 69.73 76.53 63.65 68.99 71.83 80.64 87.80 75.65 57.00 76.73 78.09 87.27 99.59 110.91 111.29 111.55 108.80 99.79 114.93 NKE Nike 62.50 62.01 61.59 61.47 58.94 55.22 55.20 55.50 57.64 52.65 50.18 50.07 50.83 52.90 57.16 55.73 55.41 52.99 59.00 59.05 52.81 51.85 54.99 60.42 62.55 68.22 67.03 66.44 68.39 71.80 79.68 76.91 82.20 84.72 75.04 75.12 74.14 81.88 85.73 84.21 87.83 77.14 83.95 86.03 84.50 93.92 89.55 93.49 101.31 96.30 89.38 82.74 87.18 98.58 98.05 97.61 111.89 125.54 120.08 134.70 141.47 STT State Street 66.36 55.73 54.78 58.52 62.30 63.06 53.92 65.78 70.24 69.63 70.21 78.80 77.72 76.20 79.71 79.61 83.90 81.46 89.73 93.23 92.49 95.54 92.00 95.35 97.61 110.17 106.15 99.73 99.78 96.11 93.09 88.31 86.91 83.78 68.75 73.02 63.07 70.90 71.87 65.81 67.66 55.25 56.06 58.09 51.31 59.19 66.07 75.10 79.10 68.11 53.27 63.04 60.96 63.55 63.79 68.09 59.33 58.90 70.48 72.78 74.17 RUI Russell 1000 5796.184 5521.7100 6170.5150 6233.9150 6337.7490 6354.6020 6559.5550 6603.0760 6585.0350 6479.2330 6740.9920 6829.8910 6999.1720 7266.2060 7241.3780 7347.9370 7442.1340 7495.3270 7602.8240 7662.7620 7786.9020 8001.2940 8242.8290 8308.8020 8796.1390 8470.4620 8246.4150 8302.3120 8516.0160 8569.5820 8825.5770 9165.8880 9159.0930 8548.3600 8712.7960 7891.1890 8587.5160 8879.9080 9001.2140 9392.7100 8798.1260 9351.9220 9556.6940 9378.2100 9504.1290 9745.7430 10117.0310 10350.8270 10413.1170 9573.1430 8249.9350 9390.7000 9874.8590 10063.0130 10698.3390 11479.5220 11008.9290 10800.5880 12063.4620 12512.9690 12903.5800 1-Year T-Bill Annual YTM 0.5200 0.6400 0.5400 0.5700 0.5400 0.5000 0.5500 0.5800 0.6400 0.7100 0.8400 0.8000 0.7900 0.9800 1.0100 1.0900 1.1700 1.1900 1.2000 1.2500 1.3600 1.5100 1.6500 1.7500 1.9000 2.0000 2.0800 2.2000 2.2500 2.3100 2.3600 2.4700 2.5600 2.6000 2.5700 2.5000 2.4700 2.4100 2.3400 2.2700 1.9400 1.9100 1.7300 1.7500 1.5700 1.5300 1.5100 1.4900 1.3700 0.3200 0.1800 0.1600 0.1800 0.1500 0.1300 0.1300 0.1300 0.1200 0.1000 0.1000 0.0700 Best Buy Ex-Dividend Date 3/15/16 6/10/16 9/9/16 12/6/16 3/20/17 6/13/17 9/18/17 12/6/17 3/21/18 6/13/18 9/17/18 12/10/18 3/19/19 6/12/19 9/18/19 12/16/19 3/18/20 6/10/20 9/14/20 12/14/20 Amount 0.28 0.28 0.28 0.28 0.34 0.34 0.34 0.34 0.45 0.45 0.45 0.45 0.50 0.50 0.50 0.50 0.55 0.55 0.55 0.55 Nike Ex-Dividend Date 3/3/16 6/2/16 9/1/16 12/1/16 3/2/17 6/1/17 8/31/17 12/1/17 3/2/18 6/1/18 8/31/18 11/30/18 3/1/19 5/31/19 8/30/19 11/29/19 2/28/20 5/29/20 8/28/20 12/4/20 Amount 0.16 0.16 0.16 0.18 0.18 0.18 0.18 0.20 0.20 0.20 0.20 0.22 0.22 0.22 0.22 0.25 0.25 0.25 0.25 0.28 State Street Ex-Dividend Date 3/30/16 6/29/16 9/29/16 12/29/16 3/30/17 6/29/17 9/29/17 12/29/17 3/29/18 6/29/18 9/28/18 12/31/18 3/29/19 6/28/19 9/30/19 12/31/19 3/31/20 6/30/20 9/30/20 12/31/20 Amount 0.34 0.34 0.38 0.38 0.38 0.38 0.42 0.42 0.42 0.42 0.47 0.47 0.47 0.47 0.52 0.52 0.52 0.52 0.52 0.52
Expert Answer:
Answer rating: 100% (QA)
To form the correlation matrix and variancecovariance matrix and calculate the betas for the three stocks I will use the following steps 1 Calculate the Excess Returns The excess return for each stock ... View the full answer
Related Book For
Posted Date:
Students also viewed these finance questions
-
PLEASE GIVE CORRECT ANSWERS Prove that the number of comparators in any sorting network is (n log n). [4 marks] (ii) What does Part (d)(i) imply in terms of the depth of any sorting network? [1 mark]...
-
The following additional information is available for the Dr. Ivan and Irene Incisor family from Chapters 1-5. Ivan's grandfather died and left a portfolio of municipal bonds. In 2012, they pay Ivan...
-
Air at room temperature has a density of about 1.29 kg/m 3 . Assuming it to be entirely one gas, find rms for its molecules.
-
Hardwood Inc. produces mostly wooden crates used for shipping products by ocean freighter. In 2012, Hardwood incurred the following costs: Wood used...
-
1) Diagnosing and addressing employee stress is vital for organizational development (OD). Surveys, questionnaires, and interviews can be employed to gauge stress levels, while indicators like...
-
A four ply symmetric \([0 / 90]_{\mathrm{s}}\) laminate has the following ply properties; ply thickness \(=0.25 \mathrm{~mm}\); \[E_{1}=129 \mathrm{GPa}, \quad E_{2}=12.8 \mathrm{GPa}, \quad...
-
Ken Ham started his own consulting firm, Hambone Consulting, on May 1, 2010. The trial balance at May 31 is as follows. In addition to those accounts listed on the trial balance, the chart of...
-
1) What is JavaScript? 2) Advantages of JavaScript? 3) Disadvantages of JavaScript? 4) Difference between Java and JavaScript? 5) What is JavaScript Engine? 6) Types of JavaScript? 7) Types of...
-
Water jets with a flow rate of 200 L/s and a diameter of 20 cm are used to drive two hydraulic turbines shown below. The diameter of the turbines is 2 m. In both turbines, the cross-sectional area of...
-
5:10 PM Sun Nov 5 Question 17 of 22 1 4 7 +/- Tap here or pull up for additional resources When copper is heated with an excess of sulfur, copper (1) sulfide is formed. In a given experiment, 0.0970...
-
Merck & Company: Evaluating a Drug Licensing Opportunity Rich Kender, Vice President of Financial Evaluation & Analysis at Merck, was working with his team to decide whether his company should...
-
Express the equilibrium constant for the following reaction. 2 C4H10(9)+13 O2(g) = 8 CO2(g) + 10 HO(l) K= [CO,18 13 [0] 3 K= K=- [CO,][H,O]10 [CH 101002113 K= [CH 10 13 [CO] [CO]8 [CH1013 4 [CH013...
-
What are the evolutionary mechanisms underlying the origin of novel adaptations, phenotypic innovations, and evolutionary novelties, including gene duplication, gene recruitment, co-option, and...
-
Module Six Assignment For each scenario listed below, identify the steps the consumer should take in order to get a refund or replacement based on consumer protection laws. Explain what might happen...
-
Port Allen Chemical Company processes raw material D into joint products E and F. Raw material D costs $6 per liter. It costs $100 to convert 100 liters of D into 60 liters of E and 40 liters of F....
-
(a) What do data breach notification laws require? (b) Why has this caused companies to think more about security?
-
A sample space includes the events A, B, and C. Draw a Venn diagram in which events A and B are mutually exclusive but events B and C are not mutually exclusive.
-
A random sample of 10 observations has been drawn from a normal population, and the sample variance is found to be s2 = 19.5. Determine the 98% confidence interval for 2.
-
Comparing the lifetimes of two brands of penlight batteries, a researcher has randomly selected 10 batteries of brand A and 10 of brand B, then subjected each battery to the same usage test. Given...
-
Show all the steps leading to the integral balance equation (18.6) in the text. Use the following boundary conditions and verify Eq. (18.7). (i) At \(y=0, T=T_{\mathrm{S}}\). (ii) At...
-
Consider the flat-plate heat transfer to be solved by the similarity approach. Show all the details leading to Eq. (18.15). What are the boundary conditions on \(\theta\) ? Extend your MATLAB code to...
-
For fluids with \(\operatorname{Pr} <1\) the velocity profile is assumed to be a cubic for \(y
Study smarter with the SolutionInn App