complete the attached finance work
iamnickok
copy_of_8639484_q2.xlsxQPS2
| FIN 5620 - Jack De Jong: Fall 2014 | |||||||||||
| Data for Problem 2 on QPS #2 | |||||||||||
| Ticker | Name of Exchange Traded Fund | ||||||||||
| 1 | SPY | SPDR S&P 500 ETF | |||||||||
| 2 | MDY | SPDR S&P MidCap 400 ETF | |||||||||
| 3 | IWM | iShares Russell 2000 ETF | |||||||||
| 4 | QQQ | Power Shares QQQ ETF | |||||||||
| 5 | EFA | iShares MSCI EAFE ETF | |||||||||
| 6 | VWO | Vanguard FTSE Emerging Markets Stock Index ETF | |||||||||
| 7 | VNQ | Vanguard REIT Index ETF | |||||||||
| 8 | BND | Vanguard Total Bond Market ETF | |||||||||
| 9 | PFF | iShares US Preferred Stock ETF | |||||||||
| 10 | GLD | SPDR Gold Shares ETF | |||||||||
| 11 | JNK | SPDR Barclays High Yield Bond ETF | |||||||||
| Adjusted Closing Prices: | |||||||||||
| Date | SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | PFF | GLD | JNK |
| 9/1/09 | $ 95.41 | $ 118.45 | $ 55.94 | $ 39.96 | $ 46.88 | $ 33.89 | $ 34.31 | $ 67.77 | $ 26.00 | $ 98.85 | $ 25.73 |
| 10/1/09 | $ 93.58 | $ 113.11 | $ 52.31 | $ 38.74 | $ 45.70 | $ 33.07 | $ 32.78 | $ 67.95 | $ 25.09 | $ 102.53 | $ 25.66 |
| 11/2/09 | $ 99.34 | $ 117.86 | $ 53.94 | $ 41.20 | $ 47.49 | $ 35.45 | $ 34.93 | $ 68.84 | $ 25.47 | $ 115.64 | $ 26.02 |
| 12/1/09 | $ 101.24 | $ 125.04 | $ 58.22 | $ 43.34 | $ 47.83 | $ 36.55 | $ 37.51 | $ 67.84 | $ 27.00 | $ 107.31 | $ 26.97 |
| 1/4/10 | $ 97.56 | $ 121.02 | $ 56.05 | $ 40.54 | $ 45.41 | $ 34.09 | $ 35.44 | $ 68.71 | $ 27.27 | $ 105.96 | $ 27.02 |
| 2/1/10 | $ 100.61 | $ 127.14 | $ 58.56 | $ 42.40 | $ 45.53 | $ 34.74 | $ 37.42 | $ 68.94 | $ 28.27 | $ 109.43 | $ 27.20 |
| 3/1/10 | $ 106.73 | $ 136.25 | $ 63.37 | $ 45.67 | $ 48.43 | $ 37.58 | $ 41.23 | $ 68.78 | $ 28.78 | $ 108.95 | $ 28.14 |
| 4/1/10 | $ 108.38 | $ 141.94 | $ 66.97 | $ 46.70 | $ 47.08 | $ 37.50 | $ 44.18 | $ 69.52 | $ 28.79 | $ 115.36 | $ 28.67 |
| 5/3/10 | $ 99.77 | $ 131.92 | $ 61.93 | $ 43.25 | $ 41.81 | $ 34.06 | $ 41.82 | $ 70.30 | $ 27.54 | $ 118.88 | $ 27.17 |
| 6/1/10 | $ 94.61 | $ 123.25 | $ 57.13 | $ 40.66 | $ 40.94 | $ 33.87 | $ 39.64 | $ 71.32 | $ 28.06 | $ 121.68 | $ 27.42 |
| 7/1/10 | $ 101.07 | $ 131.64 | $ 60.98 | $ 43.61 | $ 45.70 | $ 37.33 | $ 43.45 | $ 71.94 | $ 30.00 | $ 115.49 | $ 28.81 |
| 8/2/10 | $ 96.52 | $ 125.11 | $ 56.44 | $ 41.38 | $ 43.96 | $ 36.38 | $ 42.89 | $ 73.06 | $ 30.69 | $ 122.08 | $ 28.69 |
| 9/1/10 | $ 105.17 | $ 139.21 | $ 63.46 | $ 46.83 | $ 48.35 | $ 40.53 | $ 44.81 | $ 73.05 | $ 30.80 | $ 127.91 | $ 29.71 |
| 10/1/10 | $ 109.19 | $ 143.93 | $ 66.09 | $ 49.79 | $ 50.19 | $ 41.79 | $ 46.93 | $ 73.29 | $ 30.85 | $ 132.62 | $ 30.57 |
| 11/1/10 | $ 109.19 | $ 148.20 | $ 68.40 | $ 49.71 | $ 47.77 | $ 40.60 | $ 46.06 | $ 72.79 | $ 30.65 | $ 135.42 | $ 29.99 |
| 12/1/10 | $ 116.48 | $ 157.90 | $ 73.89 | $ 52.07 | $ 51.74 | $ 43.68 | $ 48.16 | $ 72.05 | $ 30.74 | $ 138.72 | $ 30.81 |
| 1/3/11 | $ 119.20 | $ 160.94 | $ 73.62 | $ 53.54 | $ 52.82 | $ 42.17 | $ 49.73 | $ 72.11 | $ 30.96 | $ 129.87 | $ 31.42 |
| 2/1/11 | $ 123.34 | $ 168.25 | $ 77.70 | $ 55.24 | $ 54.70 | $ 42.10 | $ 52.07 | $ 72.32 | $ 31.47 | $ 137.66 | $ 31.84 |
| 3/1/11 | $ 123.35 | $ 172.61 | $ 79.66 | $ 54.99 | $ 53.39 | $ 44.40 | $ 51.23 | $ 72.21 | $ 31.77 | $ 139.86 | $ 31.83 |
| 4/1/11 | $ 126.93 | $ 177.15 | $ 81.76 | $ 56.57 | $ 56.39 | $ 45.90 | $ 54.17 | $ 73.27 | $ 32.28 | $ 152.37 | $ 32.34 |
| 5/2/11 | $ 125.50 | $ 175.52 | $ 80.29 | $ 55.88 | $ 55.15 | $ 44.55 | $ 54.92 | $ 74.14 | $ 32.47 | $ 149.64 | $ 32.52 |
| 6/1/11 | $ 123.39 | $ 171.06 | $ 78.36 | $ 54.75 | $ 54.48 | $ 44.10 | $ 53.10 | $ 73.86 | $ 32.29 | $ 146.00 | $ 32.20 |
| 7/1/11 | $ 120.92 | $ 165.14 | $ 75.69 | $ 55.66 | $ 53.19 | $ 43.83 | $ 53.94 | $ 75.06 | $ 31.68 | $ 158.29 | $ 32.43 |
| 8/1/11 | $ 114.27 | $ 153.32 | $ 68.96 | $ 52.84 | $ 48.53 | $ 39.85 | $ 50.90 | $ 76.30 | $ 31.09 | $ 177.72 | $ 31.44 |
| 9/1/11 | $ 106.34 | $ 137.25 | $ 61.27 | $ 50.47 | $ 43.28 | $ 32.50 | $ 45.39 | $ 76.81 | $ 29.47 | $ 158.06 | $ 29.53 |
| 10/3/11 | $ 117.94 | $ 155.86 | $ 70.53 | $ 55.71 | $ 47.45 | $ 37.67 | $ 51.87 | $ 76.90 | $ 31.06 | $ 167.34 | $ 32.01 |
| 11/1/11 | $ 117.46 | $ 155.48 | $ 70.26 | $ 54.21 | $ 46.42 | $ 37.03 | $ 49.90 | $ 76.85 | $ 30.08 | $ 170.13 | $ 31.30 |
| 12/1/11 | $ 118.69 | $ 154.55 | $ 70.62 | $ 53.88 | $ 45.41 | $ 35.49 | $ 52.32 | $ 77.76 | $ 30.12 | $ 151.99 | $ 32.38 |
| 1/3/12 | $ 124.20 | $ 164.73 | $ 75.67 | $ 58.42 | $ 47.80 | $ 39.31 | $ 55.66 | $ 78.25 | $ 32.25 | $ 169.31 | $ 33.24 |
| 2/1/12 | $ 129.59 | $ 172.16 | $ 77.61 | $ 62.16 | $ 50.11 | $ 41.45 | $ 55.02 | $ 78.28 | $ 33.23 | $ 164.29 | $ 33.95 |
| 3/1/12 | $ 133.76 | $ 175.48 | $ 79.54 | $ 65.30 | $ 50.32 | $ 40.37 | $ 57.88 | $ 77.89 | $ 33.29 | $ 162.12 | $ 33.54 |
| 4/2/12 | $ 132.86 | $ 174.98 | $ 78.25 | $ 64.54 | $ 49.28 | $ 39.53 | $ 59.53 | $ 78.75 | $ 33.36 | $ 161.88 | $ 34.05 |
| 5/1/12 | $ 124.88 | $ 163.69 | $ 73.10 | $ 60.00 | $ 43.79 | $ 35.31 | $ 56.85 | $ 79.48 | $ 32.97 | $ 151.62 | $ 32.87 |
| 6/1/12 | $ 129.95 | $ 166.85 | $ 76.80 | $ 62.17 | $ 46.87 | $ 37.08 | $ 59.99 | $ 79.53 | $ 33.77 | $ 155.19 | $ 34.25 |
| 7/2/12 | $ 131.49 | $ 166.81 | $ 75.63 | $ 62.79 | $ 46.91 | $ 37.16 | $ 61.19 | $ 80.51 | $ 34.28 | $ 156.49 | $ 34.82 |
| 8/1/12 | $ 134.78 | $ 172.73 | $ 78.30 | $ 66.04 | $ 48.41 | $ 37.25 | $ 61.18 | $ 80.64 | $ 34.79 | $ 164.22 | $ 35.25 |
| 9/4/12 | $ 138.20 | $ 175.64 | $ 80.86 | $ 66.63 | $ 49.72 | $ 39.23 | $ 60.04 | $ 80.79 | $ 35.04 | $ 171.89 | $ 35.51 |
| 10/1/12 | $ 135.68 | $ 174.01 | $ 79.10 | $ 63.11 | $ 50.27 | $ 39.01 | $ 59.49 | $ 80.71 | $ 35.40 | $ 166.83 | $ 35.82 |
| 11/1/12 | $ 136.45 | $ 178.01 | $ 79.54 | $ 63.94 | $ 51.66 | $ 39.51 | $ 59.34 | $ 80.75 | $ 35.27 | $ 166.05 | $ 36.02 |
| 12/3/12 | $ 137.67 | $ 182.08 | $ 82.42 | $ 63.64 | $ 53.93 | $ 42.30 | $ 61.55 | $ 80.05 | $ 35.41 | $ 162.02 | $ 36.54 |
| 1/2/13 | $ 144.72 | $ 194.98 | $ 87.56 | $ 65.34 | $ 55.94 | $ 42.33 | $ 63.85 | $ 79.49 | $ 35.89 | $ 161.20 | $ 36.64 |
| 2/1/13 | $ 146.57 | $ 196.60 | $ 88.44 | $ 65.57 | $ 55.22 | $ 41.33 | $ 64.62 | $ 79.92 | $ 36.08 | $ 153.00 | $ 36.90 |
| 3/1/13 | $ 152.13 | $ 206.12 | $ 92.56 | $ 67.55 | $ 55.94 | $ 40.81 | $ 66.48 | $ 79.98 | $ 36.48 | $ 154.45 | $ 37.29 |
| 4/1/13 | $ 155.05 | $ 207.35 | $ 92.23 | $ 69.26 | $ 58.75 | $ 41.63 | $ 70.96 | $ 80.79 | $ 36.87 | $ 142.77 | $ 38.06 |
| 5/1/13 | $ 158.71 | $ 212.09 | $ 95.86 | $ 71.74 | $ 56.97 | $ 39.52 | $ 66.72 | $ 79.24 | $ 36.62 | $ 133.92 | $ 37.18 |
| 6/3/13 | $ 156.60 | $ 207.22 | $ 95.08 | $ 70.02 | $ 55.45 | $ 37.40 | $ 65.39 | $ 77.93 | $ 35.82 | $ 119.11 | $ 36.37 |
| 7/1/13 | $ 164.69 | $ 221.04 | $ 102.05 | $ 74.44 | $ 58.40 | $ 37.66 | $ 65.98 | $ 78.23 | $ 35.62 | $ 127.96 | $ 37.29 |
| 8/1/13 | $ 159.75 | $ 212.50 | $ 98.82 | $ 74.15 | $ 57.26 | $ 36.37 | $ 61.38 | $ 77.56 | $ 34.84 | $ 134.62 | $ 36.90 |
| 9/3/13 | $ 164.80 | $ 223.88 | $ 105.23 | $ 77.73 | $ 61.74 | $ 39.02 | $ 63.51 | $ 78.42 | $ 35.08 | $ 128.18 | $ 37.25 |
| 10/1/13 | $ 172.44 | $ 231.98 | $ 107.78 | $ 81.59 | $ 63.75 | $ 40.71 | $ 66.39 | $ 79.09 | $ 35.40 | $ 127.74 | $ 38.19 |
| 11/1/13 | $ 177.55 | $ 234.86 | $ 112.04 | $ 84.48 | $ 64.10 | $ 40.33 | $ 62.90 | $ 78.86 | $ 35.52 | $ 120.70 | $ 38.48 |
| 12/2/13 | $ 182.15 | $ 242.28 | $ 114.31 | $ 86.96 | $ 65.49 | $ 40.21 | $ 62.96 | $ 78.36 | $ 35.06 | $ 116.12 | $ 38.68 |
| 1/2/14 | $ 175.73 | $ 236.90 | $ 111.14 | $ 85.28 | $ 62.09 | $ 36.82 | $ 65.66 | $ 79.57 | $ 36.10 | $ 120.09 | $ 38.90 |
| 2/3/14 | $ 183.73 | $ 248.42 | $ 116.45 | $ 89.68 | $ 65.89 | $ 38.01 | $ 68.98 | $ 79.95 | $ 36.92 | $ 127.62 | $ 39.80 |
| 3/3/14 | $ 185.25 | $ 249.28 | $ 115.58 | $ 87.23 | $ 65.59 | $ 39.77 | $ 69.32 | $ 79.81 | $ 37.56 | $ 123.61 | $ 39.79 |
| 4/1/14 | $ 186.54 | $ 245.57 | $ 111.25 | $ 86.95 | $ 66.68 | $ 40.13 | $ 71.60 | $ 80.45 | $ 38.25 | $ 124.22 | $ 40.01 |
| 5/1/14 | $ 190.87 | $ 249.59 | $ 112.12 | $ 90.85 | $ 67.75 | $ 41.37 | $ 73.32 | $ 81.30 | $ 38.73 | $ 120.43 | $ 40.38 |
| 6/2/14 | $ 194.81 | $ 260.00 | $ 118.03 | $ 93.69 | $ 68.37 | $ 42.68 | $ 74.15 | $ 81.37 | $ 38.97 | $ 128.04 | $ 40.76 |
| 7/1/14 | $ 192.19 | $ 248.59 | $ 110.89 | $ 94.79 | $ 66.59 | $ 43.27 | $ 74.21 | $ 81.14 | $ 38.78 | $ 123.39 | $ 39.80 |
| 8/1/14 | $ 199.78 | $ 261.18 | $ 116.24 | $ 99.54 | $ 66.71 | $ 44.93 | $ 76.47 | $ 82.07 | $ 39.47 | $ 123.86 | $ 40.79 |
| 9/2/14 | $ 197.02 | $ 249.32 | $ 109.35 | $ 98.79 | $ 64.12 | $ 41.71 | $ 71.85 | $ 81.60 | $ 39.14 | $ 116.21 | $ 39.80 |
| Monthly Returns in %: | |||||||||||
| Date | SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | PFF | GLD | JNK |
| 10/1/09 | -1.92% | -4.51% | -6.49% | -3.05% | -2.52% | -2.42% | -4.46% | 0.27% | -3.50% | 3.72% | -0.27% |
| 11/2/09 | 6.16% | 4.20% | 3.12% | 6.35% | 3.92% | 7.20% | 6.56% | 1.31% | 1.51% | 12.79% | 1.40% |
| 12/1/09 | 1.91% | 6.09% | 7.93% | 5.19% | 0.72% | 3.10% | 7.39% | -1.45% | 6.01% | -7.20% | 3.65% |
| 1/4/10 | -3.63% | -3.21% | -3.73% | -6.46% | -5.06% | -6.73% | -5.52% | 1.28% | 1.00% | -1.26% | 0.19% |
| 2/1/10 | 3.13% | 5.06% | 4.48% | 4.59% | 0.26% | 1.91% | 5.59% | 0.33% | 3.67% | 3.27% | 0.67% |
| 3/1/10 | 6.08% | 7.17% | 8.21% | 7.71% | 6.37% | 8.18% | 10.18% | -0.23% | 1.80% | -0.44% | 3.46% |
| 4/1/10 | 1.55% | 4.18% | 5.68% | 2.26% | -2.79% | -0.21% | 7.15% | 1.08% | 0.03% | 5.88% | 1.88% |
| 5/3/10 | -7.94% | -7.06% | -7.53% | -7.39% | -11.19% | -9.17% | -5.34% | 1.12% | -4.34% | 3.05% | -5.23% |
| 6/1/10 | -5.17% | -6.57% | -7.75% | -5.99% | -2.08% | -0.56% | -5.21% | 1.45% | 1.89% | 2.36% | 0.92% |
| 7/1/10 | 6.83% | 6.81% | 6.74% | 7.26% | 11.63% | 10.22% | 9.61% | 0.87% | 6.91% | -5.09% | 5.07% |
| 8/2/10 | -4.50% | -4.96% | -7.45% | -5.11% | -3.81% | -2.54% | -1.29% | 1.56% | 2.30% | 5.71% | -0.42% |
| 9/1/10 | 8.96% | 11.27% | 12.44% | 13.17% | 9.99% | 11.41% | 4.48% | -0.01% | 0.36% | 4.78% | 3.56% |
| 10/1/10 | 3.82% | 3.39% | 4.14% | 6.32% | 3.81% | 3.11% | 4.73% | 0.33% | 0.16% | 3.68% | 2.89% |
| 11/1/10 | 0.00% | 2.97% | 3.50% | -0.16% | -4.82% | -2.85% | -1.85% | -0.68% | -0.65% | 2.11% | -1.90% |
| 12/1/10 | 6.68% | 6.55% | 8.03% | 4.75% | 8.31% | 7.59% | 4.56% | -1.02% | 0.29% | 2.44% | 2.73% |
| 1/3/11 | 2.34% | 1.93% | -0.37% | 2.82% | 2.09% | -3.46% | 3.26% | 0.08% | 0.72% | -6.38% | 1.98% |
| 2/1/11 | 3.47% | 4.54% | 5.54% | 3.18% | 3.56% | -0.17% | 4.71% | 0.29% | 1.65% | 6.00% | 1.34% |
| 3/1/11 | 0.01% | 2.59% | 2.52% | -0.45% | -2.39% | 5.46% | -1.61% | -0.15% | 0.95% | 1.60% | -0.03% |
| 4/1/11 | 2.90% | 2.63% | 2.64% | 2.87% | 5.62% | 3.38% | 5.74% | 1.47% | 1.61% | 8.94% | 1.60% |
| 5/2/11 | -1.13% | -0.92% | -1.80% | -1.22% | -2.20% | -2.94% | 1.38% | 1.19% | 0.59% | -1.79% | 0.56% |
| 6/1/11 | -1.68% | -2.54% | -2.40% | -2.02% | -1.21% | -1.01% | -3.31% | -0.38% | -0.55% | -2.43% | -0.98% |
| 7/1/11 | -2.00% | -3.46% | -3.41% | 1.66% | -2.37% | -0.61% | 1.58% | 1.62% | -1.89% | 8.42% | 0.71% |
| 8/1/11 | -5.50% | -7.16% | -8.89% | -5.07% | -8.76% | -9.08% | -5.64% | 1.65% | -1.86% | 12.27% | -3.05% |
| 9/1/11 | -6.94% | -10.48% | -11.15% | -4.49% | -10.82% | -18.44% | -10.83% | 0.67% | -5.21% | -11.06% | -6.08% |
| 10/3/11 | 10.91% | 13.56% | 15.11% | 10.38% | 9.63% | 15.91% | 14.28% | 0.12% | 5.40% | 5.87% | 8.40% |
| 11/1/11 | -0.41% | -0.24% | -0.38% | -2.69% | -2.17% | -1.70% | -3.80% | -0.07% | -3.16% | 1.67% | -2.22% |
| 12/1/11 | 1.05% | -0.60% | 0.51% | -0.61% | -2.18% | -4.16% | 4.85% | 1.18% | 0.13% | -10.66% | 3.45% |
| 1/3/12 | 4.64% | 6.59% | 7.15% | 8.43% | 5.26% | 10.76% | 6.38% | 0.63% | 7.07% | 11.40% | 2.66% |
| 2/1/12 | 4.34% | 4.51% | 2.56% | 6.40% | 4.83% | 5.44% | -1.15% | 0.04% | 3.04% | -2.96% | 2.14% |
| 3/1/12 | 3.22% | 1.93% | 2.49% | 5.05% | 0.42% | -2.61% | 5.20% | -0.50% | 0.18% | -1.32% | -1.21% |
| 4/2/12 | -0.67% | -0.28% | -1.62% | -1.16% | -2.07% | -2.08% | 2.85% | 1.10% | 0.21% | -0.15% | 1.52% |
| 5/1/12 | -6.01% | -6.45% | -6.58% | -7.03% | -11.14% | -10.68% | -4.50% | 0.93% | -1.17% | -6.34% | -3.47% |
| 6/1/12 | 4.06% | 1.93% | 5.06% | 3.62% | 7.03% | 5.01% | 5.52% | 0.06% | 2.43% | 2.35% | 4.20% |
| 7/2/12 | 1.19% | -0.02% | -1.52% | 1.00% | 0.09% | 0.22% | 2.00% | 1.23% | 1.51% | 0.84% | 1.66% |
| 8/1/12 | 2.50% | 3.55% | 3.53% | 5.18% | 3.20% | 0.24% | -0.02% | 0.16% | 1.49% | 4.94% | 1.23% |
| 9/4/12 | 2.54% | 1.68% | 3.27% | 0.89% | 2.71% | 5.32% | -1.86% | 0.19% | 0.72% | 4.67% | 0.74% |
| 10/1/12 | -1.82% | -0.93% | -2.18% | -5.28% | 1.11% | -0.56% | -0.92% | -0.10% | 1.03% | -2.94% | 0.87% |
| 11/1/12 | 0.57% | 2.30% | 0.56% | 1.32% | 2.77% | 1.28% | -0.25% | 0.05% | -0.37% | -0.47% | 0.56% |
| 12/3/12 | 0.89% | 2.29% | 3.62% | -0.47% | 4.39% | 7.06% | 3.72% | -0.87% | 0.40% | -2.43% | 1.44% |
| 1/2/13 | 5.12% | 7.08% | 6.24% | 2.67% | 3.73% | 0.07% | 3.74% | -0.70% | 1.36% | -0.51% | 0.27% |
| 2/1/13 | 1.28% | 0.83% | 1.01% | 0.35% | -1.29% | -2.36% | 1.21% | 0.54% | 0.53% | -5.09% | 0.71% |
| 3/1/13 | 3.79% | 4.84% | 4.66% | 3.02% | 1.30% | -1.26% | 2.88% | 0.08% | 1.11% | 0.95% | 1.06% |
| 4/1/13 | 1.92% | 0.60% | -0.36% | 2.53% | 5.02% | 2.01% | 6.74% | 1.01% | 1.07% | -7.56% | 2.06% |
| 5/1/13 | 2.36% | 2.29% | 3.94% | 3.58% | -3.03% | -5.07% | -5.98% | -1.92% | -0.68% | -6.20% | -2.31% |
| 6/3/13 | -1.33% | -2.30% | -0.81% | -2.40% | -2.67% | -5.36% | -1.99% | -1.65% | -2.18% | -11.06% | -2.18% |
| 7/1/13 | 5.17% | 6.67% | 7.33% | 6.31% | 5.32% | 0.70% | 0.90% | 0.38% | -0.56% | 7.43% | 2.53% |
| 8/1/13 | -3.00% | -3.86% | -3.17% | -0.39% | -1.95% | -3.43% | -6.97% | -0.86% | -2.19% | 5.20% | -1.05% |
| 9/3/13 | 3.16% | 5.36% | 6.49% | 4.83% | 7.82% | 7.29% | 3.47% | 1.11% | 0.69% | -4.78% | 0.95% |
| 10/1/13 | 4.64% | 3.62% | 2.42% | 4.97% | 3.26% | 4.33% | 4.53% | 0.85% | 0.91% | -0.34% | 2.52% |
| 11/1/13 | 2.96% | 1.24% | 3.95% | 3.54% | 0.55% | -0.93% | -5.26% | -0.29% | 0.34% | -5.51% | 0.76% |
| 12/2/13 | 2.59% | 3.16% | 2.03% | 2.94% | 2.17% | -0.30% | 0.10% | -0.63% | -1.30% | -3.79% | 0.52% |
| 1/2/14 | -3.52% | -2.22% | -2.77% | -1.93% | -5.19% | -8.43% | 4.29% | 1.54% | 2.97% | 3.42% | 0.57% |
| 2/3/14 | 4.55% | 4.86% | 4.78% | 5.16% | 6.12% | 3.23% | 5.06% | 0.48% | 2.27% | 6.27% | 2.31% |
| 3/3/14 | 0.83% | 0.35% | -0.75% | -2.73% | -0.46% | 4.63% | 0.49% | -0.18% | 1.73% | -3.14% | -0.03% |
| 4/1/14 | 0.70% | -1.49% | -3.75% | -0.32% | 1.66% | 0.91% | 3.29% | 0.80% | 1.84% | 0.49% | 0.55% |
| 5/1/14 | 2.32% | 1.64% | 0.78% | 4.49% | 1.60% | 3.09% | 2.40% | 1.06% | 1.25% | -3.05% | 0.92% |
| 6/2/14 | 2.06% | 4.17% | 5.27% | 3.13% | 0.92% | 3.17% | 1.13% | 0.09% | 0.62% | 6.32% | 0.94% |
| 7/1/14 | -1.34% | -4.39% | -6.05% | 1.17% | -2.60% | 1.38% | 0.08% | -0.28% | -0.49% | -3.63% | -2.36% |
| 8/1/14 | 3.95% | 5.06% | 4.82% | 5.01% | 0.18% | 3.84% | 3.05% | 1.15% | 1.78% | 0.38% | 2.49% |
| 9/2/14 | -1.38% | -4.54% | -5.93% | -0.75% | -3.88% | -7.17% | -6.04% | -0.57% | -0.84% | -6.18% | -2.43% |
Answer Report 1
| Microsoft Excel 12.0 Answer Report | ||||||
| Worksheet: [8639484_Q2.xlsx]Questions | ||||||
| Report Created: 11/14/2014 4:38:29 PM | ||||||
| Target Cell (Max) | ||||||
| Cell | Name | Original Value | Final Value | |||
| $C$97 | Portfolio Return = MDY | 0.0036083337 | 0.0151607558 | |||
| Adjustable Cells | ||||||
| Cell | Name | Original Value | Final Value | |||
| $B$74 | Weights SPY | 17% | 98% | |||
| $C$74 | Weights MDY | -9% | -100% | |||
| $D$74 | Weights IWM | 15% | -100% | |||
| $E$74 | Weights QQQ | -5% | 100% | |||
| $F$74 | Weights EFA | -1% | -100% | |||
| $G$74 | Weights VWO | -2% | -100% | |||
| $H$74 | Weights VNQ | -10% | 100% | |||
| $I$74 | Weights BND | 94% | 100% | |||
| $J$74 | Weights JNK | 0% | 100% | |||
| Constraints | ||||||
| Cell | Name | Cell Value | Formula | Status | Slack | |
| $C$93 | Portfolio Std. Dev. = MDY | 0.0674999962 | $C$93=0.0675 | Not Binding | 0 | |
| $B$74 | Weights SPY | 98% | $B$74<=1 | Not Binding | 0.0243909416 | |
| $C$74 | Weights MDY | -100% | $C$74<=1 | Not Binding | 2 | |
| $D$74 | Weights IWM | -100% | $D$74<=1 | Not Binding | 2 | |
| $E$74 | Weights QQQ | 100% | $E$74<=1 | Binding | 0 | |
| $F$74 | Weights EFA | -100% | $F$74<=1 | Not Binding | 2 | |
| $G$74 | Weights VWO | -100% | $G$74<=1 | Not Binding | 2 | |
| $H$74 | Weights VNQ | 100% | $H$74<=1 | Binding | 0 | |
| $I$74 | Weights BND | 100% | $I$74<=1 | Binding | 0 | |
| $J$74 | Weights JNK | 100% | $J$74<=1 | Binding | 0 | |
| $B$74 | Weights SPY | 98% | $B$74>=-1 | Not Binding | 198% | |
| $C$74 | Weights MDY | -100% | $C$74>=-1 | Binding | 0% | |
| $D$74 | Weights IWM | -100% | $D$74>=-1 | Binding | 0% | |
| $E$74 | Weights QQQ | 100% | $E$74>=-1 | Not Binding | 200% | |
| $F$74 | Weights EFA | -100% | $F$74>=-1 | Binding | 0% | |
| $G$74 | Weights VWO | -100% | $G$74=-1 | Not Binding | 0 | |
| $H$74 | Weights VNQ | 100% | $H$74>=-1 | Not Binding | 200% | |
| $I$74 | Weights BND | 100% | $I$74>=-1 | Not Binding | 200% | |
| $J$74 | Weights JNK | 100% | $J$74>=-1 | Not Binding | 200% |
Q 1-10
| 1. What is the average return for each of the nine indexes? | ||||||||||
| Index | SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | |
| Avg. Return | 1.29% | 1.35% | 1.26% | 1.61% | 0.65% | 0.52% | 1.35% | 0.31% | 0.76% | |
| 2. Show the covariance matrix of returns. Briefly describe how you constructed the covariance matrix. | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | ||
| SPY | 0.001431129 | 0.0016455315 | 0.0018268206 | 0.0015300285 | 0.0016544361 | 0.0017931899 | 0.0013563694 | -0.0000773593 | 0.0006984718 | |
| MDY | 0.0016455315 | 0.0021342647 | 0.002379859 | 0.0017744279 | 0.0018766614 | 0.0021672887 | 0.0016709454 | -0.0001021474 | 0.0008387692 | |
| IWM | 0.0018268206 | 0.002379859 | 0.0027881709 | 0.0019844306 | 0.0020567532 | 0.0023765615 | 0.0018159983 | -0.0001443889 | 0.0009274554 | |
| QQQ | 0.0015300285 | 0.0017744279 | 0.0019844306 | 0.0019232893 | 0.0017514627 | 0.0019001392 | 0.0014671156 | -0.0000738479 | 0.0007242209 | |
| EFA | 0.0016544361 | 0.0018766614 | 0.0020567532 | 0.0017514627 | 0.0024242987 | 0.0024962674 | 0.0016937591 | -0.0000645799 | 0.0009492393 | |
| VWO | 0.0017931899 | 0.0021672887 | 0.0023765615 | 0.0019001392 | 0.0024962674 | 0.0034079481 | 0.0019970427 | -0.0000514165 | 0.0010916407 | |
| VNQ | 0.0013563694 | 0.0016709454 | 0.0018159983 | 0.0014671156 | 0.0016937591 | 0.0019970427 | 0.0023187665 | 0.0000421698 | 0.0009278379 | |
| BND | -0.0000773593 | -0.0001021474 | -0.0001443889 | -0.0000738479 | -0.0000645799 | -0.0000514165 | 0.0000421698 | 0.0000715527 | 0.0000153919 | |
| JNK | 0.0006984718 | 0.0008387692 | 0.0009274554 | 0.0007242209 | 0.0009492393 | 0.0010916407 | 0.0009278379 | 0.0000153919 | 0.0005612642 | |
| This covariance matrix is constructed using the Data Analysis Tool in the Excel. | ||||||||||
| Consider the simple case where short sales are allowed. Use Excel Solver to find the Minimum Variance Portfolio (MVP). | ||||||||||
| Correlation Matrix | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | ||
| SPY | 1 | 0.9415484405 | 0.9145275959 | 0.922226977 | 0.8882138602 | 0.8119707986 | 0.7445778071 | -0.2417463751 | 0.7793379433 | |
| MDY | 0.9415484405 | 1 | 0.9755901297 | 0.8758140451 | 0.8250280366 | 0.8036114858 | 0.751120485 | -0.2613906574 | 0.7663632874 | |
| IWM | 0.9145275959 | 0.9755901297 | 1 | 0.8569468896 | 0.7910964242 | 0.7709795964 | 0.7142121314 | -0.323266745 | 0.7413950672 | |
| QQQ | 0.922226977 | 0.8758140451 | 0.8569468896 | 1 | 0.8111207718 | 0.7421926066 | 0.6947259495 | -0.1990686976 | 0.6970518882 | |
| EFA | 0.8882138602 | 0.8250280366 | 0.7910964242 | 0.8111207718 | 1 | 0.868463448 | 0.7143812734 | -0.1550569827 | 0.8137649353 | |
| VWO | 0.8119707986 | 0.8036114858 | 0.7709795964 | 0.7421926066 | 0.868463448 | 1 | 0.7104153696 | -0.1041221869 | 0.7893135535 | |
| VNQ | 0.7445778071 | 0.751120485 | 0.7142121314 | 0.6947259495 | 0.7143812734 | 0.7104153696 | 1 | 0.1035285642 | 0.8133171972 | |
| BND | -0.2417463751 | -0.2613906574 | -0.323266745 | -0.1990686976 | -0.1550569827 | -0.1041221869 | 0.1035285642 | 1 | 0.0768062148 | |
| JNK | 0.7793379433 | 0.7663632874 | 0.7413950672 | 0.6970518882 | 0.8137649353 | 0.7893135535 | 0.8133171972 | 0.0768062148 | 1 | |
| Taking the Initial Weights of the different indexes as follow and then calculating the MVP using the Solver to minimize the variance. | ||||||||||
| (As short selling is allowed, weights of the indexes can be negative.) | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Standard Dev. | 0.0381495138 | 0.0465879667 | 0.0532487371 | 0.0442254162 | 0.0496526789 | 0.0588702805 | 0.048559939 | 0.0085302686 | 0.023890942 | |
| Weights | 17% | -9% | 15% | -5% | -1% | -2% | -10% | 94% | 0% | 100% |
| w*σ | 0.0065376057 | -0.0040360279 | 0.0077702171 | -0.0020453148 | -0.0003295517 | -0.0013557484 | -0.0047688039 | 0.008018522 | 0.0000824661 | |
| Annual volatility | ||||||||||
| Portfolio Variance = | 0.0000434468 | |||||||||
| Using the Solver function to minimize the Variance by changing the Weights. | ||||||||||
| 3. What is the expected portfolio return for the MVP portfolio? | ||||||||||
| Portfolio Return = | 0.36% | |||||||||
| 4. What is the portfolio standard deviation for the MVP portfolio? | ||||||||||
| Portfolio Std. Dev. = | 0.66% | |||||||||
| 5. What is the portfolio composition (i.e., what are the weights for the nine ETFs)? | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Weights | 17% | -9% | 15% | -5% | -1% | -2% | -10% | 94% | 0% | 100% |
| (Negative weights show the short selling.) | ||||||||||
| Consider the simple case where short sales are allowed, but short positions must be greater than or equal to –100% and long positions must be less than or equal to 100%. Use Excel Solver to find the Maximum return portfolio with a standard deviation of exactly 6.75%. | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Weights | 100% | -100% | -100% | 100% | -100% | -100% | 100% | 100% | 100% | 100% |
| Avg. Return | 0.0128705467 | 0.0135422113 | 0.0126205435 | 0.0161493861 | 0.0064509139 | 0.0051893062 | 0.0135457508 | 0.0031357104 | 0.0075762612 | |
| Standard Dev. | 0.0249196793 | 0.0346147884 | 0.0393162509 | 0.0265640539 | 0.0342800188 | 0.0421189833 | 0.0382472404 | 0.009140048 | 0.0157106397 | |
| w*σ | 0.0249197042 | -0.0346147884 | -0.0393162509 | 0.0265640273 | -0.0342800188 | -0.0421189833 | 0.0382472404 | 0.009140048 | 0.0157106397 | |
| Correlation Matrix | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | ||
| SPY | 1 | 0.9415484405 | 0.9145275959 | 0.922226977 | 0.8882138602 | 0.8119707986 | 0.7445778071 | -0.2417463751 | 0.7793379433 | |
| MDY | 0.9415484405 | 1 | 0.9755901297 | 0.8758140451 | 0.8250280366 | 0.8036114858 | 0.751120485 | -0.2613906574 | 0.7663632874 | |
| IWM | 0.9145275959 | 0.9755901297 | 1 | 0.8569468896 | 0.7910964242 | 0.7709795964 | 0.7142121314 | -0.323266745 | 0.7413950672 | |
| QQQ | 0.922226977 | 0.8758140451 | 0.8569468896 | 1 | 0.8111207718 | 0.7421926066 | 0.6947259495 | -0.1990686976 | 0.6970518882 | |
| EFA | 0.8882138602 | 0.8250280366 | 0.7910964242 | 0.8111207718 | 1 | 0.868463448 | 0.7143812734 | -0.1550569827 | 0.8137649353 | |
| VWO | 0.8119707986 | 0.8036114858 | 0.7709795964 | 0.7421926066 | 0.868463448 | 1 | 0.7104153696 | -0.1041221869 | 0.7893135535 | |
| VNQ | 0.7445778071 | 0.751120485 | 0.7142121314 | 0.6947259495 | 0.7143812734 | 0.7104153696 | 1 | 0.1035285642 | 0.8133171972 | |
| BND | -0.2417463751 | -0.2613906574 | -0.323266745 | -0.1990686976 | -0.1550569827 | -0.1041221869 | 0.1035285642 | 1 | 0.0768062148 | |
| JNK | 0.7793379433 | 0.7663632874 | 0.7413950672 | 0.6970518882 | 0.8137649353 | 0.7893135535 | 0.8133171972 | 0.0768062148 | 1 | |
| Portfolio Variance = | 0.0045008549 | |||||||||
| Portfolio Std. Dev. = | 0.0670884113 | |||||||||
| Using the solver for Std. Dev. To be = 6.75% and maximizing the Return. | ||||||||||
| 6. What is the expected portfolio return for this portfolio? | ||||||||||
| Portfolio Return = | 1.55% | |||||||||
| 7. What is the portfolio composition (i.e., what are the weights for the nine ETFs)? | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Weights | 100% | -100% | -100% | 100% | -100% | -100% | 100% | 100% | 100% | 100% |
| Consider the more realistic case where short sales are NOT allowed and no more than 40% of the portfolio and no less than 4% is invested in any ETF. Use Excel Solver to find the Minimum Variance Portfolio (MVP). | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Weights | 20% | 4% | 4% | 20% | 4% | 4% | 4% | 20% | 20% | 100% |
| Avg. Return | 0.0128705467 | 0.0135422113 | 0.0126205435 | 0.0161493861 | 0.0064509139 | 0.0051893062 | 0.0135457508 | 0.0031357104 | 0.0075762612 | |
| Standard Dev. | 0.0249196793 | 0.0346147884 | 0.0393162509 | 0.0265640539 | 0.0342800188 | 0.0421189833 | 0.0382472404 | 0.009140048 | 0.0157106397 | |
| w*σ | 0.0249197042 | -0.0346147884 | -0.0393162509 | 0.0265640273 | -0.0342800188 | -0.0421189833 | 0.0382472404 | 0.009140048 | 0.0157106397 | |
| Correlation Matrix | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | ||
| SPY | 1 | 0.9415484405 | 0.9145275959 | 0.922226977 | 0.8882138602 | 0.8119707986 | 0.7445778071 | -0.2417463751 | 0.7793379433 | |
| MDY | 0.9415484405 | 1 | 0.9755901297 | 0.8758140451 | 0.8250280366 | 0.8036114858 | 0.751120485 | -0.2613906574 | 0.7663632874 | |
| IWM | 0.9145275959 | 0.9755901297 | 1 | 0.8569468896 | 0.7910964242 | 0.7709795964 | 0.7142121314 | -0.323266745 | 0.7413950672 | |
| QQQ | 0.922226977 | 0.8758140451 | 0.8569468896 | 1 | 0.8111207718 | 0.7421926066 | 0.6947259495 | -0.1990686976 | 0.6970518882 | |
| EFA | 0.8882138602 | 0.8250280366 | 0.7910964242 | 0.8111207718 | 1 | 0.868463448 | 0.7143812734 | -0.1550569827 | 0.8137649353 | |
| VWO | 0.8119707986 | 0.8036114858 | 0.7709795964 | 0.7421926066 | 0.868463448 | 1 | 0.7104153696 | -0.1041221869 | 0.7893135535 | |
| VNQ | 0.7445778071 | 0.751120485 | 0.7142121314 | 0.6947259495 | 0.7143812734 | 0.7104153696 | 1 | 0.1035285642 | 0.8133171972 | |
| BND | -0.2417463751 | -0.2613906574 | -0.323266745 | -0.1990686976 | -0.1550569827 | -0.1041221869 | 0.1035285642 | 1 | 0.0768062148 | |
| JNK | 0.7793379433 | 0.7663632874 | 0.7413950672 | 0.6970518882 | 0.8137649353 | 0.7893135535 | 0.8133171972 | 0.0768062148 | 1 | |
| Portfolio Variance = | 0.0045008549 | |||||||||
| Portfolio Std. Dev. = | 0.0670884113 | |||||||||
| Using the solver for ETF between 4% and 40% | ||||||||||
| Minimizing the variance. | ||||||||||
| 8. What is the expected portfolio return for the MVP portfolio? | ||||||||||
| Portfolio Return = | 1.00% | |||||||||
| 9. What is the portfolio standard deviation for the MVP portfolio? | ||||||||||
| Portfolio Std. Dev. = | 0.0670884113 | |||||||||
| 10. What is the portfolio composition (i.e., what are the weights for the nine ETFs)? | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Weights | 20% | 4% | 4% | 20% | 4% | 4% | 4% | 20% | 20% | 100% |
Q 11-14
| Consider the simple case where short sales are NOT allowed and no more than 50% and no less than 5% of the portfolio is invested in any ETF. Use Excel Solver to find the Market Portfolio if the risk-free rate is 0.1250%/month (1.5%/year). | ||||||||||
| Risk Free Rate = | 1.50% | |||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Weights | 5% | 5% | 5% | 50% | 5% | 5% | 15% | 5% | 5% | 100% |
| Return | 0.0128705467 | 0.0135422113 | 0.0126205435 | 0.0161493861 | 0.0064509139 | 0.0051893062 | 0.0135457508 | 0.0031357104 | 0.0075762612 | |
| Std. Dev. | 0.0249196793 | 0.0346147884 | 0.0393162509 | 0.0265640539 | 0.0342800188 | 0.0421189833 | 0.0382472404 | 0.009140048 | 0.0157106397 | |
| w*σ | 0.0249197042 | -0.0346147884 | -0.0393162509 | 0.0265640273 | -0.0342800188 | -0.0421189833 | 0.0382472404 | 0.009140048 | 0.0157106397 | |
| Correlation Matrix | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | ||
| SPY | 1 | 0.9415484405 | 0.9145275959 | 0.922226977 | 0.8882138602 | 0.8119707986 | 0.7445778071 | -0.2417463751 | 0.7793379433 | |
| MDY | 0.9415484405 | 1 | 0.9755901297 | 0.8758140451 | 0.8250280366 | 0.8036114858 | 0.751120485 | -0.2613906574 | 0.7663632874 | |
| IWM | 0.9145275959 | 0.9755901297 | 1 | 0.8569468896 | 0.7910964242 | 0.7709795964 | 0.7142121314 | -0.323266745 | 0.7413950672 | |
| QQQ | 0.922226977 | 0.8758140451 | 0.8569468896 | 1 | 0.8111207718 | 0.7421926066 | 0.6947259495 | -0.1990686976 | 0.6970518882 | |
| EFA | 0.8882138602 | 0.8250280366 | 0.7910964242 | 0.8111207718 | 1 | 0.868463448 | 0.7143812734 | -0.1550569827 | 0.8137649353 | |
| VWO | 0.8119707986 | 0.8036114858 | 0.7709795964 | 0.7421926066 | 0.868463448 | 1 | 0.7104153696 | -0.1041221869 | 0.7893135535 | |
| VNQ | 0.7445778071 | 0.751120485 | 0.7142121314 | 0.6947259495 | 0.7143812734 | 0.7104153696 | 1 | 0.1035285642 | 0.8133171972 | |
| BND | -0.2417463751 | -0.2613906574 | -0.323266745 | -0.1990686976 | -0.1550569827 | -0.1041221869 | 0.1035285642 | 1 | 0.0768062148 | |
| JNK | 0.7793379433 | 0.7663632874 | 0.7413950672 | 0.6970518882 | 0.8137649353 | 0.7893135535 | 0.8133171972 | 0.0768062148 | 1 | |
| Portfolio Variance = | 0.0045008549 | |||||||||
| Portfolio Std. Dev. = | 0.0670884113 | |||||||||
| Using the solver for ETF between 5% and 50% | ||||||||||
| Portfolio Return = | 1.32% | |||||||||
| Maximizing the Return for Market Portfolio. | ||||||||||
| 11. What is the expected portfolio return for this portfolio? | ||||||||||
| Portfolio Return = | 0.0131758168 | |||||||||
| 12. What is the portfolio standard deviation for this portfolio? | ||||||||||
| Portfolio Std. Dev. = | 0.0670884113 | |||||||||
| 13. What is the portfolio composition (i.e., what are the weights for the nine ETFs)? | ||||||||||
| SPY | MDY | IWM | QQQ | EFA | VWO | VNQ | BND | JNK | Total | |
| Weights | 5% | 5% | 5% | 50% | 5% | 5% | 15% | 5% | 5% | 100% |
| 14. What is the maximum Sharpe ratio? | ||||||||||
| Sharp Ratio = (Portfolio return - Risk free Rate) / Portfolio Std. Dev. | ||||||||||
| -0.0271907349 |
