In: Finance
In Yahoo Finance, find the weekly prices from 09/01/2017 to 08/31/2020 for two Vanguard funds – the Vanguard Index 500 fund (symbol VFINX) and the Vanguard High-Yield Corporate Bond fund (VWEHX). The first of these funds tracks the Standard and Poor’s 500, and VWEHX is a junk-bond fund. Compute the weekly mean returns, sample variance, sample standard deviation, sample covariance, and correlation statistics. Graph a weekly mean return and standard deviation frontier of the portfolios composed of these two funds.
Parameter | Excel formula used |
the weekly mean returns | '=Average() |
sample variance | = Var.s() |
sample standard deviation | '=Stdev.s() |
sample covariance | '=Covariance.s() |
correlation statistics | '=correl() |
The last few rows contain your answers:
Adjusted close price | Weekly Return | |||
Date | VFINX | VWEHX | VFINX | VWEHX |
8/28/2017 | 217.1326 | 5.058956 | ||
9/4/2017 | 215.8741 | 5.082017 | -0.58% | 0.46% |
9/11/2017 | 219.3752 | 5.082017 | 1.62% | 0.00% |
9/18/2017 | 218.5047 | 5.090532 | -0.40% | 0.17% |
9/25/2017 | 221.1413 | 5.099041 | 1.21% | 0.17% |
10/2/2017 | 223.8987 | 5.121309 | 1.25% | 0.44% |
10/9/2017 | 224.26 | 5.112761 | 0.16% | -0.17% |
10/16/2017 | 226.2378 | 5.121309 | 0.88% | 0.17% |
10/23/2017 | 226.7608 | 5.10421 | 0.23% | -0.33% |
10/30/2017 | 227.4169 | 5.10421 | 0.29% | 0.00% |
11/6/2017 | 227.0936 | 5.07416 | -0.14% | -0.59% |
11/13/2017 | 226.951 | 5.065575 | -0.06% | -0.17% |
11/20/2017 | 229.0714 | 5.082747 | 0.93% | 0.34% |
11/27/2017 | 232.7227 | 5.091331 | 1.59% | 0.17% |
12/4/2017 | 233.626 | 5.103401 | 0.39% | 0.24% |
12/11/2017 | 235.832 | 5.103401 | 0.94% | 0.00% |
12/18/2017 | 235.4612 | 5.094779 | -0.16% | -0.17% |
12/25/2017 | 235.7576 | 5.103401 | 0.13% | 0.17% |
1/1/2018 | 241.9472 | 5.152802 | 2.63% | 0.97% |
1/8/2018 | 245.8348 | 5.144143 | 1.61% | -0.17% |
1/15/2018 | 247.9839 | 5.135481 | 0.87% | -0.17% |
1/22/2018 | 253.524 | 5.135481 | 2.23% | 0.00% |
1/29/2018 | 243.8385 | 5.092183 | -3.82% | -0.84% |
2/5/2018 | 231.3924 | 5.032975 | -5.10% | -1.16% |
2/12/2018 | 241.5078 | 5.067746 | 4.37% | 0.69% |
2/19/2018 | 242.8928 | 5.050361 | 0.57% | -0.34% |
2/26/2018 | 238.0692 | 5.032975 | -1.99% | -0.34% |
3/5/2018 | 246.599 | 5.062544 | 3.58% | 0.59% |
3/12/2018 | 243.6474 | 5.045088 | -1.20% | -0.34% |
3/19/2018 | 228.2022 | 5.018901 | -6.34% | -0.52% |
3/26/2018 | 233.8635 | 5.02763 | 2.48% | 0.17% |
4/2/2018 | 230.6982 | 5.060078 | -1.35% | 0.65% |
4/9/2018 | 235.4078 | 5.095157 | 2.04% | 0.69% |
4/16/2018 | 236.674 | 5.077617 | 0.54% | -0.34% |
4/23/2018 | 236.6644 | 5.060078 | 0.00% | -0.35% |
4/30/2018 | 236.1656 | 5.033769 | -0.21% | -0.52% |
5/7/2018 | 242.036 | 5.065362 | 2.49% | 0.63% |
5/14/2018 | 240.8945 | 5.047744 | -0.47% | -0.35% |
5/21/2018 | 241.6811 | 5.038935 | 0.33% | -0.17% |
5/28/2018 | 242.9856 | 5.038935 | 0.54% | 0.00% |
6/4/2018 | 247.0046 | 5.071726 | 1.65% | 0.65% |
6/11/2018 | 247.1773 | 5.089429 | 0.07% | 0.35% |
6/18/2018 | 245.019 | 5.080577 | -0.87% | -0.17% |
6/25/2018 | 240.7506 | 5.045172 | -1.74% | -0.70% |
7/2/2018 | 245.5691 | 5.059401 | 2.00% | 0.28% |
7/9/2018 | 249.3552 | 5.086076 | 1.54% | 0.53% |
7/16/2018 | 249.4419 | 5.086076 | 0.03% | 0.00% |
7/23/2018 | 250.9641 | 5.094967 | 0.61% | 0.17% |
7/30/2018 | 252.9679 | 5.112751 | 0.80% | 0.35% |
8/6/2018 | 252.5055 | 5.145848 | -0.18% | 0.65% |
8/13/2018 | 254.1722 | 5.136914 | 0.66% | -0.17% |
8/20/2018 | 256.3976 | 5.154782 | 0.88% | 0.35% |
8/27/2018 | 258.9121 | 5.154782 | 0.98% | 0.00% |
9/3/2018 | 256.3591 | 5.170041 | -0.99% | 0.30% |
9/10/2018 | 259.4516 | 5.187992 | 1.21% | 0.35% |
9/17/2018 | 261.677 | 5.179016 | 0.86% | -0.17% |
9/24/2018 | 259.2396 | 5.179016 | -0.93% | 0.00% |
10/1/2018 | 257.8787 | 5.175408 | -0.52% | -0.07% |
10/8/2018 | 247.3812 | 5.148359 | -4.07% | -0.52% |
10/15/2018 | 247.4876 | 5.139343 | 0.04% | -0.18% |
10/22/2018 | 237.7449 | 5.085246 | -3.94% | -1.05% |
10/29/2018 | 243.5596 | 5.112293 | 2.45% | 0.53% |
11/5/2018 | 248.9388 | 5.138717 | 2.21% | 0.52% |
11/12/2018 | 245.0979 | 5.066212 | -1.54% | -1.41% |
11/19/2018 | 235.8582 | 5.048088 | -3.77% | -0.36% |
11/26/2018 | 247.4296 | 5.05715 | 4.91% | 0.18% |
12/3/2018 | 236.1581 | 5.075375 | -4.56% | 0.36% |
12/10/2018 | 232.0849 | 5.075375 | -1.72% | 0.00% |
12/17/2018 | 216.8431 | 4.956917 | -6.57% | -2.33% |
12/24/2018 | 223.1147 | 4.947805 | 2.89% | -0.18% |
12/31/2018 | 227.3542 | 5.01159 | 1.90% | 1.29% |
1/7/2019 | 233.198 | 5.141173 | 2.57% | 2.59% |
1/14/2019 | 239.946 | 5.168665 | 2.89% | 0.53% |
1/21/2019 | 239.4307 | 5.168665 | -0.21% | 0.00% |
1/28/2019 | 243.2909 | 5.205322 | 1.61% | 0.71% |
2/4/2019 | 243.5535 | 5.222084 | 0.11% | 0.32% |
2/11/2019 | 249.7959 | 5.249714 | 2.56% | 0.53% |
2/18/2019 | 251.4101 | 5.268134 | 0.65% | 0.35% |
2/25/2019 | 252.5671 | 5.286554 | 0.46% | 0.35% |
3/4/2019 | 247.1998 | 5.272719 | -2.13% | -0.26% |
3/11/2019 | 254.4827 | 5.309721 | 2.95% | 0.70% |
3/18/2019 | 251.235 | 5.318971 | -1.28% | 0.17% |
3/25/2019 | 255.6801 | 5.328222 | 1.77% | 0.17% |
4/1/2019 | 261.0173 | 5.390491 | 2.09% | 1.17% |
4/8/2019 | 262.4739 | 5.418373 | 0.56% | 0.52% |
4/15/2019 | 262.2881 | 5.409078 | -0.07% | -0.17% |
4/22/2019 | 265.4553 | 5.409078 | 1.21% | 0.00% |
4/29/2019 | 266.032 | 5.409078 | 0.22% | 0.00% |
5/6/2019 | 260.4406 | 5.405344 | -2.10% | -0.07% |
5/13/2019 | 258.6419 | 5.386672 | -0.69% | -0.35% |
5/20/2019 | 255.6899 | 5.386672 | -1.14% | 0.00% |
5/27/2019 | 249.0818 | 5.34933 | -2.58% | -0.69% |
6/3/2019 | 260.1962 | 5.4308 | 4.46% | 1.52% |
6/10/2019 | 261.555 | 5.44956 | 0.52% | 0.35% |
6/17/2019 | 267.3419 | 5.505838 | 2.21% | 1.03% |
6/24/2019 | 265.3086 | 5.496458 | -0.76% | -0.17% |
7/1/2019 | 271.1067 | 5.530376 | 2.19% | 0.62% |
7/8/2019 | 273.3365 | 5.520954 | 0.82% | -0.17% |
7/15/2019 | 270.0066 | 5.502111 | -1.22% | -0.34% |
7/22/2019 | 274.4956 | 5.530376 | 1.66% | 0.51% |
7/29/2019 | 266.0579 | 5.511532 | -3.07% | -0.34% |
8/5/2019 | 264.997 | 5.526632 | -0.40% | 0.27% |
8/12/2019 | 262.502 | 5.517168 | -0.94% | -0.17% |
8/19/2019 | 258.7694 | 5.545559 | -1.42% | 0.51% |
8/26/2019 | 266.0972 | 5.573948 | 2.83% | 0.51% |
9/2/2019 | 270.9594 | 5.608211 | 1.83% | 0.61% |
9/9/2019 | 273.7196 | 5.598705 | 1.02% | -0.17% |
9/16/2019 | 272.364 | 5.617716 | -0.50% | 0.34% |
9/23/2019 | 268.4743 | 5.598705 | -1.43% | -0.34% |
9/30/2019 | 268.8805 | 5.579694 | 0.15% | -0.34% |
10/7/2019 | 270.6566 | 5.613024 | 0.66% | 0.60% |
10/14/2019 | 272.1464 | 5.632116 | 0.55% | 0.34% |
10/21/2019 | 275.4913 | 5.641662 | 1.23% | 0.17% |
10/28/2019 | 279.5959 | 5.632116 | 1.49% | -0.17% |
11/4/2019 | 282.181 | 5.657045 | 0.92% | 0.44% |
11/11/2019 | 284.8154 | 5.657045 | 0.93% | 0.00% |
11/18/2019 | 283.9866 | 5.647457 | -0.29% | -0.17% |
11/25/2019 | 286.9171 | 5.666634 | 1.03% | 0.34% |
12/2/2019 | 287.4992 | 5.700336 | 0.20% | 0.59% |
12/9/2019 | 289.7192 | 5.729221 | 0.77% | 0.51% |
12/16/2019 | 293.222 | 5.748479 | 1.21% | 0.34% |
12/23/2019 | 296.3399 | 5.748479 | 1.06% | 0.00% |
12/30/2019 | 295.9831 | 5.748479 | -0.12% | 0.00% |
1/6/2020 | 298.8874 | 5.783296 | 0.98% | 0.61% |
1/13/2020 | 304.815 | 5.783296 | 1.98% | 0.00% |
1/20/2020 | 301.7322 | 5.763954 | -1.01% | -0.33% |
1/27/2020 | 295.3983 | 5.744612 | -2.10% | -0.34% |
2/3/2020 | 304.8745 | 5.798027 | 3.21% | 0.93% |
2/10/2020 | 309.8901 | 5.827163 | 1.65% | 0.50% |
2/17/2020 | 306.0838 | 5.817451 | -1.23% | -0.17% |
2/24/2020 | 271.0535 | 5.700907 | -11.44% | -2.00% |
3/2/2020 | 272.7981 | 5.693969 | 0.64% | -0.12% |
3/9/2020 | 247.9081 | 5.323471 | -9.12% | -6.51% |
3/16/2020 | 211.6622 | 4.767724 | -14.62% | -10.44% |
3/23/2020 | 233.4285 | 5.040722 | 10.28% | 5.73% |
3/30/2020 | 228.701 | 5.040722 | -2.03% | 0.00% |
4/6/2020 | 256.4688 | 5.29026 | 12.14% | 4.95% |
4/13/2020 | 264.3214 | 5.417617 | 3.06% | 2.41% |
4/20/2020 | 260.8778 | 5.300056 | -1.30% | -2.17% |
4/27/2020 | 260.3702 | 5.319649 | -0.19% | 0.37% |
5/4/2020 | 269.656 | 5.371793 | 3.57% | 0.98% |
5/11/2020 | 263.7044 | 5.322601 | -2.21% | -0.92% |
5/18/2020 | 272.3233 | 5.440663 | 3.27% | 2.22% |
5/25/2020 | 280.6039 | 5.529209 | 3.04% | 1.63% |
6/1/2020 | 294.4977 | 5.68137 | 4.95% | 2.75% |
6/8/2020 | 280.5641 | 5.592444 | -4.73% | -1.57% |
6/15/2020 | 285.839 | 5.631967 | 1.88% | 0.71% |
6/22/2020 | 276.3342 | 5.562803 | -3.33% | -1.23% |
6/29/2020 | 288.93 | 5.582564 | 4.56% | 0.36% |
7/6/2020 | 294.09 | 5.625388 | 1.79% | 0.77% |
7/13/2020 | 297.82 | 5.674994 | 1.27% | 0.88% |
7/20/2020 | 297.01 | 5.754366 | -0.27% | 1.40% |
7/27/2020 | 302.22 | 5.794051 | 1.75% | 0.69% |
8/3/2020 | 309.73 | 5.836961 | 2.48% | 0.74% |
8/10/2020 | 311.86 | 5.797118 | 0.69% | -0.68% |
8/17/2020 | 314.24 | 5.797118 | 0.76% | 0.00% |
8/24/2020 | 324.59 | 5.827 | 3.29% | 0.52% |
Weekly mean Return | 0.3017% | 0.1002% | ||
Sample Variance | 0.0862% | 0.0187% | ||
Sample std dev | 2.9355% | 1.3678% | ||
Covariance | 0.0329% | |||
Correlation Coefficient | 0.8202 |
-----------------------------
Frontier graph is:
And the Base data for efficient frontier:
Weight in portfolio | |||
VFINX | VWEHX | Portfolio Weekly Return | Portfolio Std Dev |
0% | 100% | 0.10% | 1.37% |
1.00% | 99% | 0.10% | 1.38% |
2.00% | 98% | 0.10% | 1.39% |
3.00% | 97% | 0.11% | 1.40% |
4.00% | 96% | 0.11% | 1.41% |
5.00% | 95% | 0.11% | 1.42% |
6.00% | 94% | 0.11% | 1.43% |
7.00% | 93% | 0.11% | 1.45% |
8.00% | 92% | 0.12% | 1.46% |
9.00% | 91% | 0.12% | 1.47% |
10.00% | 90% | 0.12% | 1.48% |
11.00% | 89% | 0.12% | 1.49% |
12.00% | 88% | 0.12% | 1.51% |
13.00% | 87% | 0.13% | 1.52% |
14.00% | 86% | 0.13% | 1.53% |
15.00% | 85% | 0.13% | 1.54% |
16.00% | 84% | 0.13% | 1.56% |
17.00% | 83% | 0.13% | 1.57% |
18.00% | 82% | 0.14% | 1.58% |
19.00% | 81% | 0.14% | 1.60% |
20.00% | 80% | 0.14% | 1.61% |
21.00% | 79% | 0.14% | 1.62% |
22.00% | 78% | 0.14% | 1.64% |
23.00% | 77% | 0.15% | 1.65% |
24.00% | 76% | 0.15% | 1.67% |
25.00% | 75% | 0.15% | 1.68% |
26.00% | 74% | 0.15% | 1.70% |
27.00% | 73% | 0.15% | 1.71% |
28.00% | 72% | 0.16% | 1.72% |
29.00% | 71% | 0.16% | 1.74% |
30.00% | 70% | 0.16% | 1.75% |
31.00% | 69% | 0.16% | 1.77% |
32.00% | 68% | 0.16% | 1.78% |
33.00% | 67% | 0.17% | 1.80% |
34.00% | 66% | 0.17% | 1.81% |
35.00% | 65% | 0.17% | 1.83% |
36.00% | 64% | 0.17% | 1.84% |
37.00% | 63% | 0.17% | 1.86% |
38.00% | 62% | 0.18% | 1.87% |
39.00% | 61% | 0.18% | 1.89% |
40.00% | 60% | 0.18% | 1.91% |
41.00% | 59% | 0.18% | 1.92% |
42.00% | 58% | 0.18% | 1.94% |
43.00% | 57% | 0.19% | 1.95% |
44.00% | 56% | 0.19% | 1.97% |
45.00% | 55% | 0.19% | 1.99% |
46.00% | 54% | 0.19% | 2.00% |
47.00% | 53% | 0.19% | 2.02% |
48.00% | 52% | 0.20% | 2.03% |
49.00% | 51% | 0.20% | 2.05% |
50.00% | 50% | 0.20% | 2.07% |
51.00% | 49% | 0.20% | 2.08% |
52.00% | 48% | 0.21% | 2.10% |
53.00% | 47% | 0.21% | 2.12% |
54.00% | 46% | 0.21% | 2.13% |
55.00% | 45% | 0.21% | 2.15% |
56.00% | 44% | 0.21% | 2.17% |
57.00% | 43% | 0.22% | 2.18% |
58.00% | 42% | 0.22% | 2.20% |
59.00% | 41% | 0.22% | 2.22% |
60.00% | 40% | 0.22% | 2.23% |
61.00% | 39% | 0.22% | 2.25% |
62.00% | 38% | 0.23% | 2.27% |
63.00% | 37% | 0.23% | 2.28% |
64.00% | 36% | 0.23% | 2.30% |
65.00% | 35% | 0.23% | 2.32% |
66.00% | 34% | 0.23% | 2.33% |
67.00% | 33% | 0.24% | 2.35% |
68.00% | 32% | 0.24% | 2.37% |
69.00% | 31% | 0.24% | 2.39% |
70.00% | 30% | 0.24% | 2.40% |
71.00% | 29% | 0.24% | 2.42% |
72.00% | 28% | 0.25% | 2.44% |
73.00% | 27% | 0.25% | 2.45% |
74.00% | 26% | 0.25% | 2.47% |
75.00% | 25% | 0.25% | 2.49% |
76.00% | 24% | 0.25% | 2.51% |
77.00% | 23% | 0.26% | 2.52% |
78.00% | 22% | 0.26% | 2.54% |
79.00% | 21% | 0.26% | 2.56% |
80.00% | 20% | 0.26% | 2.58% |
81.00% | 19% | 0.26% | 2.60% |
82.00% | 18% | 0.27% | 2.61% |
83.00% | 17% | 0.27% | 2.63% |
84.00% | 16% | 0.27% | 2.65% |
85.00% | 15% | 0.27% | 2.67% |
86.00% | 14% | 0.27% | 2.68% |
87.00% | 13% | 0.28% | 2.70% |
88.00% | 12% | 0.28% | 2.72% |
89.00% | 11% | 0.28% | 2.74% |
90.00% | 10% | 0.28% | 2.76% |
91.00% | 9% | 0.28% | 2.77% |
92.00% | 8% | 0.29% | 2.79% |
93.00% | 7% | 0.29% | 2.81% |
94.00% | 6% | 0.29% | 2.83% |
95.00% | 5% | 0.29% | 2.85% |
96.00% | 4% | 0.29% | 2.86% |
97.00% | 3% | 0.30% | 2.88% |
98.00% | 2% | 0.30% | 2.90% |
99.00% | 1% | 0.30% | 2.92% |
100.00% | 0% | 0.30% | 2.94% |