Description
Please see attached documents and answer these questions in detail. Please calculate numbers in excel and please mention how did you get each number and how it is calculated
2. If the portfolio was equally weighted (20% in all stocks and ETF) what is the resulting portfolio position (risk and return)? How does the variability of each stock affect the portfolio? How does this relate to your answer in question 1 above? How would the portfolio risk and return change if 50% of it is in SBUX, 20% in SPY, and 10% in each of the remainder securities.
3. Compute the “beta” for each stock. What does beta measure? (Use SPY as the measurement of the Market). How does this relate to your previous answers? What is the portfolio beta (for the equally weighed)? What does it indicate?
Please attached documents excel and pdf and answer question 2 & 3 and also please use excel to calculate numbers and formula. Please also mention where each number coming from and how it is calculated. Only use numbers in word, use detail calculation in excel and mention where each number is from. Thank you
Unformatted Attachment Preview
Purchase answer to see full attachment
Explanation & Answer
Attached.
Running Head: CALCULATION PROCEDURE
Beta Calculation Procedure
Name
Instructor
Institutional Affiliation
Date
1
2
CALCULATION PROCEDURE
#2
If the portfolio was equally weighted at 20%, then the portfolio return would be given by the formula:
Portfolio return= 20%* (Sum of the individual asset return). Note that the individual returns re obtained
by the simple average for the returns over the period given. The following is an extract from excel
showing the average return:
SPY
SBUX GM
VZ
XPO
RF
Return 0.73% 1.03% 0.10% 0.78% 2.46% 0.65%
The portfolio return is: 20%* (0.73%+1.03%+0.10%+0.78%+2.46%+0.65%) =1.005%
The portfolio risk is the variance for the individual returns in a given period obtained using the variance
of a sample formula in the excel and the proportion of the particular stock. The risk is obtained as shown
below:
Return
Variance (risk)
Standard deviation
Covariance
Beta
SPY
0.73%
0.0009
0.0295
SBUX
1.03%
0.0025
0.0496
4.497E-04
1.831E-01
GM
0.10%
0.0043
0.0652
1.059E-03
2.490E-01
VZ
0.78%
0.0023
0.0482
4.628E-04
1.988E-01
XPO
2.46%
0.0156
0.1250
2.086E-03
1.334E-01
The variability of the stocks affects the average stock return by plus or minus the variance in a given
stock.
How would the portfolio risk and return change if 50% of it is in SBUX, 20% in SPY, and 10% in each of the
remainder securities.
The following shows how this change:
Return
Variance (risk)
Standard
deviation
Covariance
SPY
0.73%
0.0009
0.0295
Beta
Portfolio return
#3
1.00%
SBUX
1.03%
0.0025
0.0496
GM
0.10%
0.0043
0.0652
VZ
0.78%
0.0023
0.0482
XPO
2.46%
0.0156
0.1250
RF
0.65%
0.0001
0.0075
4.497E04
1.831E01
1.059E03
2.490E01
4.628E04
1.988E01
2.086E03
1.334E01
-1.266E06
-2.271E02
RF
0.65%
0.0001
0.0075
-1.266E-06
-2.271E-02
3
CALCULATION PROCEDURE
Beta is a measure of risk arising from asset exposure due to market movements. The betas are as shown
below:
Return
Variance (risk)
Standard
deviation
Covariance
SPY
0.73%
0.0009
0.0295
Beta
Portfolio return
1.00%
SBUX
1.03%
0.0025
0.0496
GM
0.10%
0.0043
0.0652
VZ
0.78%
0.0023
0.0482
XPO
2.46%
0.0156
0.1250
RF
0.65%
0.0001
0.0075
4.497E04
1.831E01
1.059E03
2.490E01
4.628E04
1.988E01
2.086E03
1.334E01
-1.266E06
-2.271E02
Date
SPY
1/1/2014
2/1/2014
3/1/2014
4/1/2014
5/1/2014
6/1/2014
7/1/2014
8/1/2014
9/1/2014
10/1/2014
11/1/2014
12/1/2014
1/1/2015
2/1/2015
3/1/2015
4/1/2015
5/1/2015
6/1/2015
7/1/2015
8/1/2015
9/1/2015
10/1/2015
11/1/2015
12/1/2015
1/1/2016
2/1/2016
3/1/2016
4/1/2016
5/1/2016
6/1/2016
7/1/2016
8/1/2016
9/1/2016
10/1/2016
11/1/2016
12/1/2016
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
6/1/2017
7/1/2017
8/1/2017
9/1/2017
10/1/2017
-3,52%
4,55%
0,39%
0,70%
2,32%
1,58%
-1,34%
3,95%
-1,84%
2,36%
2,75%
-0,80%
-2,96%
5,62%
-2,01%
0,98%
1,29%
-2,49%
2,21%
-5,36%
-3,80%
9,03%
-0,10%
-2,31%
-4,98%
-0,08%
6,18%
0,39%
1,70%
-0,17%
3,65%
0,12%
-0,50%
-1,73%
3,68%
1,43%
1,79%
3,93%
-0,31%
0,99%
1,41%
0,15%
2,06%
0,29%
1,51%
2,36%
Returns
SBUX
Returns
GM
Returns
VZ
-177,44%
-9,26%
4017,16% -11,72%
-3623,87%
-2,28%
1077,65%
-0,22%
-106,60%
0,33%
-106,76%
0,16%
-44,40%
3,41%
-190,67%
-4,92%
-2920,70%
-0,02%
-70,04%
-3,76%
-201,38%
0,17%
-39,89%
-1,77%
47,09%
3,71%
-34,37%
0,29%
-94,17%
8,10%
-217,41%
5,65%
1358,00%
4,97%
-172,80%
-2,06%
-134,05%
0,39%
131,67%
-6,83%
-335,77%
3,05%
-314,65%
0,17%
-105,54%
2,90%
-135,26%
-0,13%
-178,06%
-3,02%
-2379,07%
-8,22%
386,10%
0,34%
-14,27%
0,13%
-98,23%
-1,69%
-126,15%
0,52%
-442,90%
7,48%
622,91%
6,46%
46,03%
1,80%
-72,96%
1,03%
-84,51%
4,43%
-167,49%
-7,53%
-152,72%
6,68%
-1,91%
-6,56%
-145,62%
-2,29%
-379,91%
6,81%
426,10%
14,38%
2723,34%
9,46%
-304,19%
1,29%
-72,52%
0,51%
-107,83%
-1,66%
-23,51%
4,71%
-1,89%
-6,51%
-350,69%
3,72%
-151,70%
4,80%
50,26%
2,60%
-135,36%
-0,88%
-212,27%
3,19%
-60,25%
-7,34%
34,41%
-5,72%
-141,28%
8,04%
-335,17%
-5,46%
-16,88%
0,39%
41,14%
-3,42%
-314,87%
-6,57%
-433,45%
-0,51%
-142,09%
1,59%
-84,22%
1,97%
-87,91%
-5,43%
-9531,21%
10,08%
-634,55%
16,29%
340,82%
7,75%
-95,85%
-1,89%
-14,87%
3,70%
-161,08%
-1,79%
-53,61%
-2,22%
-279,71%
-6,05%
-52,92%
1,69%
5927,80%
1,23%
-129,26% -12,85%
1804,26%
8,11%
-101,34%
-4,21%
-264,57%
-0,67%
-109,98%
2,77%
1467,78%
2,56%
-144,04%
6,76%
474,19%
6,60%
-76,83%
-5,81%
143,90%
1,18%
-171,99%
-5,81%
-1091,58%
-2,38%
-158,66%
-1,64%
-82,84%
0,98%
-104,70%
4,06%
149,53%
-9,53%
-183,21%
9,71%
2945,51%
1,63%
-151,93%
11,45%
850,25%
-0,77%
-124,10%
-3,14%
-15,65%
1,20%
-356,38%
-4,61%
-71,34%
-3,72%
88,07%
-0,47%
-12,18%
-0,67%
-147,06%
-1,98%
-121,41%
-0,54%
-105,77%
-7,46%
157,73%
9,23%
-318,47%
9,27%
932,79%
4,92%
-20,12%
-4,23%
682,15%
0,90%
-82,33%
6,97%
-54,46%
-0,54%
-118,08%
5,08%
708,67%
-8,19%
-1372,78%
2,99%
11,80%
0,63%
-115,64%
2,35%
-131,10%
2,67%
-6,55%
-4,02%
97,30%
-1,77%
-29,67%
2,86%
-51,61%
-2,04%
-0,66%
-5,83%
846,50%
5,91%
-170,94%
-2,05%
-169,54%
2,80%
-92,75%
-8,33%
12,20%
2,95%
-1,96%
-4,25%
604,47%
-7,43%
-555,51%
3,01%
93,14%
8,37%
-80,69%
1,63%
-177,77%
1,56%
-85,19%
0,41%
-35,87%
-2,10%
-199,64%
10,51%
63,21%
3,17%
-22,91%
2,10%
-61,28%
6,44%
2415,83%
-3,27%
11/1/2017
12/1/2017
1/1/2018
2/1/2018
3/1/2018
4/1/2018
5/1/2018
6/1/2018
7/1/2018
8/1/2018
9/1/2018
10/1/2018
11/1/2018
12/1/2018
3,06%
0,70%
5,64%
-3,64%
-3,13%
0,52%
2,43%
0,13%
3,70%
3,19%
0,14%
-6,91%
1,85%
1,32%
337,86%
-87,61%
-255,00%
16,20%
-705,44%
-78,74%
1837,16%
-96,61%
16,06%
2160,14%
-102,04%
-472,54%
40,09%
5,43%
-0,67%
-1,08%
0,51%
1,38%
-0,55%
-1,56%
-13,80%
7,25%
2,02%
6,34%
2,52%
14,50%
1,17%
-905,62%
-37,52%
-311,49%
-63,10%
-350,29%
-64,64%
-88,67%
-290,42%
258,16%
-68,10%
152,10%
-82,65%
1140,43%
0,26%
-4,87%
3,46%
-7,22%
-7,65%
1,10%
16,22%
-7,73%
-3,78%
-4,91%
-6,60%
8,67%
3,72%
1,32%
-105,25%
-240,68%
-148,01%
-5,67%
-794,94%
-93,21%
-309,90%
104,36%
-22,92%
-25,68%
-176,13%
133,33%
182,11%
7,58%
4,01%
2,15%
-10,70%
0,17%
3,20%
-2,19%
5,54%
2,64%
6,51%
-1,80%
6,93%
6,79%
-3,55%
Returns
XPO
Returns
RF
Returns
-1520,10%
-5,21%
-119,92%
0,04%
-20,0%
-864,02%
26,16%
-505,22%
0,05%
0,0%
-98,81%
-6,46%
-16,35%
0,05%
66,7%
-121,79%
-7,72%
4,22%
0,03%
0,0%
-493,02%
-7,41%
-153,33%
0,03%
-25,0%
-167,70%
13,89%
75,10%
0,04%
33,3%
-2516,67%
7,93%
2962,55%
0,03%
0,0%
-136,93%
0,26%
-98,80%
0,03%
50,0%
-34,40%
21,63%
262,20%
0,02%
0,0%
-71,16%
5,97%
-292,29%
0,02%
0,0%
-123,95%
-3,11%
-154,61%
0,02%
-33,3%
229,26%
5,69%
-156,85%
0,03%
0,0%
-124,17% -10,00%
-150,01%
0,03%
50,0%
-670,60%
20,01%
569,12%
0,02%
-33,3%
-144,55%
2,99%
-55,13%
0,03%
50,0%
-524,87%
6,66%
389,68%
0,02%
0,0%
-84,70%
1,36%
-116,81%
0,02%
0,0%
-1582,25%
-8,10%
99,88%
0,02%
-33,3%
-175,88%
-4,05%
-78,72%
0,03%
-57,1%
-90,63% -19,03%
-40,73%
0,07%
250,0%
-170,15% -32,11%
-294,69%
0,02%
0,0%
-532,46%
16,49%
67,08%
0,02%
-83,3%
-205,71%
9,87%
-192,63%
0,12%
-47,8%
-79,12% -10,66%
-34,01%
0,23%
-11,5%
192,94% -16,15%
-293,17%
0,26%
-16,1%
-58,06%
8,36%
-65,16%
0,31%
6,9%
-213,73%
23,99% -1415,18%
0,29%
26,1%
-694,76%
-1,82%
-37,52%
0,23%
-14,8%
-89,94%
-2,92%
-71,52%
0,27%
0,0%
-1360,35% -10,25%
-180,13%
0,27%
-10,0%
-83,28%
12,80%
-38,67%
0,30%
0,0%
588,54%
20,86%
758,55%
0,30%
3,4%
-91,04%
2,43%
-123,83%
0,29%
-12,1%
-251,58% -10,20%
-128,95%
0,33%
-26,7%
-29,39%
35,23% -1244,98%
0,45%
-11,8%
-185,19%
-3,08%
-184,04%
0,51%
0,0%
-448,84%
3,66%
-73,79%
0,51%
-1,9%
...