Diversification is the risk reduction that occurs from creating portfolios of investments with less than perfect correlation. Diversification results when the unique or unsystematic risk of one investment offsets the unique or unsystematic risk of another investment. We should be able to identify the benefit of diversification by comparing the risk reduction that occurs from combing two investments in common stock that are highly correlated with the risk reduction that occurs from combining two investments in common stock that are less correlated. For this exercise you will use the monthly returns from owning Apple Computer (AAPL), Walmart (WMT) and Microsoft (MSFT) common stock from 10/31/2012 through 10/31/2018. Intuitively it seems that Apple and Microsoft would share more risks than Apple and Walmart. If this is true, the correlation coefficient between Apple and Microsoft should be higher than the correlation coefficient between Apple and Walmart.
1 -In the posted template you should calculate the average monthly return, median monthly return, standard deviation of monthly returns, and variance of monthly returns for each of the three stock investments. I have also included the daily returns for a portfolio that is invested in equal dollar amounts of Apple & Walmart and the daily returns for a portfolio that is invested in equal dollar amounts of Apple & Microsoft each month.
2 - In the posted template you should calculate the average monthly return, median monthly return, standard deviation of monthly returns, and variance of monthly returns for each of these two portfolios. If there is no reduction of risk from diversification the average standard deviation of the two stocks should be the same as the standard deviation of the portfolio combining the two stocks.
3 - Use the average standard deviation of the returns of the pairs of stock investments compared to the actual computed standard deviation (not the analytical standard deviation) of each of the pairs of stocks to measure the percentage reduction in risk achieved through diversification.
4 - Use Excel to calculate the correlation coefficient (=CORREL(array 1, array2)) between Apple and Walmart and the correlation coefficient between Apple and Microsoft.
5 - Using your calculated correlation coefficients, variances and standard deviations I have entered the formula found on page 377 in Chapter 12 of the your textbook to calculate the Analytical Variance of these two portfolios. Use this value to calculate the Analytical Standard Deviation and compare it to the actual standard deviation you calculated in #2 above. 5 - Comment on whether or not the risk reduction is consistent with the measures correlation coefficients.
*use template below*
WMT MSFT AAPL & MSFT Correlation and Risk Reduction #1 AAPL Average Monthly Return Median Monthly Return Std Deviation of Monthly Return Variance of Monthly Return #2 AAPL & WMT Average Monthly Return Median Monthly Return Std Deviation of Monthly Return Variance of Monthly Return 0.00% Correlation coefficient Between AAPL & WMT #4 Correlation coefficient Between AAPL & MSFT Analytical Variance Analytical Standard Deviation 0.00% #S Average of Standard Deviations Average of Monthly Returns 0.00% 0.00% 0.00% 0.00% #6 Comment on Risk Reduction & Correlation: Risk Reduction as % of Average Std Dev #3 AAPL WMT AAPL & WMT AAPL & MSFT Monthly Return Monthly Return MSFT Monthly Return Monthly Return Monthly Return 28.54 10/31/12 $ 85.05 11/30/12 83.61 12/31/12 76.02 1/31/13 65.07 2/28/13 63.06 3/29/13 63.24 4/30/13 63.25 5/31/13 64.25 6/28/13 56.65 7/31/13 64.65 8/30/13 69.60 9/30/13 68.11 10/31/13 74.67 11/29/13 79.44 12/31/13 80.15 1/31/14 71.51 2/28/14 75.18 3/31/14 76.68 4/30/14 84.30 -1.69% -9.07% -14.41% -3.09% 0.29% 0.03% 1.57% -11.83% 14.12% 7.66% -2.15% 9.64% % 6.38% 0.89% -10.77% 5.12% 2.00% 9.94% $ 75.02 72.02 68.23 69.95 70.78 74.83 77.72 74.84 74.49 77.94 72.98 73.96 76.75 81.01 78.69 74.68 74.70 76.43 79.71 4.00% -5.26% 2.52% 1.19% 5.72% 3.86% -3.71% -0.47% 4.63% 6.36% 1.34% 3.77% 5.55% -2.86% -5.10% 0.03% 2.32% 4.29% 26.62 26.71 27.45 27.80 28.61 33.10 34.90 34.55 31.84 33.40 33.28 35.41 38.13 37.41 37.84 38.31 40.99 40.40 -6.74% 0.36% 2.77% 1.28% 2.90% 15.71% 5.44% -1.02% -7.83% 4.90% -0.36% 6.39% 7.70% -1.89% 1.15% 1.24% 7.00% -1.44% $ 100.00 $ 97,16 $ 90.19 $ 84.83 $ 84.02 $ 86.55 $ 88.23 $ 87.29 $ 81.92 $ 89.60 $ 90.19 $ 89.82 $ 95.84 $ 101.56 $ 100.56 $ 92.58 $ 94.97 $ 97.02 $ 103.92 $ 100.00 -2.84% $ 95.78 -7.17% $ 91.61 -5.94% $ 86.28 -0.95% $ 85.49 3.00% $ 86.85 1.94% $ 93.69 -1.07% $ 96.97 -6.15% $ 90.74 9.38% $ 93.60 0.65% $ 99.48 -0.40% $ 98.23 6.71% $ 106.10 5.97% $ 113.57 -0.99% $ 113.00 -7.93% $ 107.57 2.57% $ 110.99 2.16% $ 115.98 7.12% $ 120.91 -4.22% -4.36% -5.82% -0.91% 1.59% 7.87% 3.50% -6.42% 3.15% 6.28% -1.25% 8.01% 7.04% -0.50% 4.81% 3.18% 4.50% 4.25% 3/31/14 4/30/14 5/30/14 6/30/14 7/31/14 8/29/14 9/30/14 10/31/14 11/28/14 12/31/14 1/30/15 2/27/15 3/31/15 4/30/15 5/29/15 6/30/15 7/31/15 8/31/15 9/30/15 10/30/15 11/30/15 12/31/15 1/29/16 2/29/16 3/31/16 4/29/16 5/31/16 6/30/16 7/29/16 8/31/16 9/30/16 10/31/16 11/30/16 12/30/16 1/31/17 2/28/17 3/31/17 4/28/17 5/31/17 6/30/17 7/31/17 8/31/17 9/29/17 10/31/17 11/30/17 12/29/17 1/31/18 2/28/18 3/30/18 4/30/18 5/31/18 6/29/18 7/31/18 8/31/18 9/28/18 10/31/18 76.68 84.30 90.43 92.93 95.60 102.50 100.75 108.00 118.93 110.38 117.16 128.46 124.43 125.15 130.28 125.43 121.30 112.76 110.30 119.50 118.30 105.26 97.34 96.69 108.99 93.74 99.86 95.60 104.21 106.10 113.05 113.54 110.52 115.82 121.35 136.99 143.66 143.65 152.76 144.02 148.73 164.00 154.12 169.04 171.85 169.23 167.43 178.12 167.78 165.26 186.87 185.11 190.29 227.63 225.74 218.86 2.00% 9.94% 7.27% 2.77% 2.87% 7.22% -1.71% 7.20% 10.12% -7.19% 6.14% 9.64% 3.14% 0.58% 4.10% -3.73% -3.29% -7.04% -2.18% 8.34% -1.00% -11.02% -7.52% 0.67% 12.72% -13.99% 6.53% -4.27% 9.01% 1.81% 6.55% 0.43% -2.66% % 4.80% 4.77% 12.89% 4.87% -0.01% 6.34% -5.72% 3.27% 10.27% 6.02% 9.68% 1.66% -1.52% -1.06% 6.38% 5.81% -1.50% 13.08% -0.94% 2.80% 19.62% -0.83% -3.05% 76.43 79.71 76.77 75.07 73.58 75.50 76.47 76.27 87.54 85.88 84.98 83.93 82.25 78.05 74.27 70.93 71.98 64.73 64.84 57.24 58.84 61.30 66.36 66.34 68.49 66.87 70.78 73.02 72.97 71.44 72.12 70.02 70.43 69.12 66.74 70.93 72.08 75.18 78.60 75.68 79.99 78.07 78.14 87.31 97.23 98.75 106.60 90.01 88.97 88.46 82.54 85.65 89.23 95.86 93.91 100.28 2.32% 4.29% -3.69% -2.21% -1.98% 2.61% 1.28% -0.26% 14.78% -1.90% -1.05% -1.24% -2.00% -5.11% 4.84% 4.50% 1.48% -10.07% 0.17% -11.72% 2.80% 4.18% 8.25% -0.03% 3.24% -2.37% 5.85% 3.16% -0.07% -2.10% 0.95% -2.91% 0.59% -1.86% -3.44% % 6.28% 1.62% 4.30% 4.55% -3.72% 5.70% -2.40% 0.09% 11.74% 11.36% 1.56% 7.95% -15.56% -1.16% -0.57% -6.69% 3.77% 4.18% 7.43% -2.03% 6.78% 40.99 40.40 40.94 41.70 43.16 45.43 46.36 46.95 47.81 46.45 40.40 43.85 40.66 48.64 46.86 44.15 46.70 43.52 44.26 52.64 54.35 55.48 55.09 50.88 55.23 49.87 53.00 51.17 56.68 57.46 57.60 59.92 60.26 62.14 64.65 63.98 65.86 68.46 69.84 68.93 72.70 74.77 74.49 83.18 84.17 85.54 95.01 93.77 91.27 93.52 98.84 98.61 106.08 112.33 114.37 106.81 7.00% -1.44% 1.34% 1.86% 3.50% 5.26% 2.05% 1.27% 1.83% -2.84% -13.02% 8.54% -7.29% 19.64% -3.66% -5.78% 5.78% 6.81% 1.70% 18.93% 3.25% 2.08% -0.70% -7.64% 8.55% -9.70% 6.28% -3.45% 10.77% 1.38% 0.24% 4.03% 0.57% 3.12% 4,04% -1.04% 2.94% 3.95% 2.02% -1.30% 5.47% 2.85% -0.37% 11.67% 1.19% 1.63% 11.07% -1.31% -2.67% 2.47% 5.69% -0.23% 7.58% 5.89% 1.82% -6.61% $ 97.02 $ 103.92 $ 105.78 $106.07 $ 106.54 $ 111.78 $ 111.54 $ 115.41 $ 129.78 $ 123.88 $ 127.04 $132.38 $ 128.98 $ 126.06 $ 125.59 $ 120.42 $ 119.33 $ 109.12 $ 108.03 $ 106.20 $ 107.15 $ 103.49 $ 103.86 $ 103.50 $ 111.76 $ 102.62 $ 108.97 $ 108.37 $ 113.21 $ 113.05 $ 117.29 $ 115.84 $ 114.64 $ 116.32 $ 117.10 $ 128.32 $ 132.48 $ 135.33 $ 142.70 $ 135.96 $ 142.06 $ 147.65 $ 143.26 $ 158.61 $ 168.93 $168.97 $ 174.78 $ 166.76 $ 160.96 $ 159.29 $164.37 $ 166.70 $ 172.51 $ 195.85 $ 193.04 $ 196.65 2.16% $ 115.98 7.12%'$ 120.91 1.79% $ 126.12 0.28% $ 129.03 0.44% $ 133.14 4.91% $ 141.45 ' -0.21% $ 141.69 3.47% $ 147.69 12.45% $ 156.51 4.54% $ 148.56 2.55% $ 143.55 4.20% $ 156.60 -2.57% $ 148.44 -2.26%'$ 163.44 -0.37% $ 163.80 4.11% $ 156.01 -0.90% $ 157.95 -8.56% $ 147.02 -1.01% $ 146.66 -1.69%$ 166.66 0.90%'$ 168.53 -3.42% $ 161.00 0.37% $ 154.37 -0.35% $ 147.96 7.98% $ 163.70 -8.18%$ 144.30 6.19% $ 153.54 -0.55%$ 147.61 4.47% $ 162.21 -0.14% $ 164.79 $ 3.75% $ 170.39 -1.24%'$ 174.19 - 1.04%'$ 172.37 1.47% $ 179.19 0.67% $ 187.09 9.58% $ 198.18 3.25% $ 205.91 2.15% $ 209.97 5.45% $ 218.74 4.72% $ 211.06 4.48% $ 220.29 3.93% $ 234.73 -2.97% $ 227.22 10.71%'$ 251.47 6.51% $ 255,06 0.02%'$ 255.19 3.44% $ 267.96 $ 4.59% $ 274.76 -3.48%$ 263.13 -1.04%'$ 264.39 3.19%'$ 289.20 1.41% $ 287.50 3.49%'$ 302.41 13.53%$ 340.99 % -1.43%'$ 342.67 1.87%'$ 326.13 4.507 4.25% 4.30% 2.31% 3.19% 6.24% 0.17% 4.23% 5.98% -5.02% -3.44% 9.09% -5.21% 10.11% 0.22% -4.75% 1.24% -6.92% -0.24% 13.64% 1.12% 4.47% 4.11% -4.15% 10.64% -11.85% 6.40% -3.86% 9.89% 1.59% % 3.40% 2.23% -1.05% 3.96% 4.41% 5.93% 3.90% 1.97% 4.18% -3.51% 4.37% 6.56% -3.20% 10.67% 1.43% 0.05% 5.00% 2.54% -4.24% 0.48% 9.38% % -0.59% 5.19% 12.76% 0.49% -4.83% WMT MSFT AAPL & MSFT Correlation and Risk Reduction #1 AAPL Average Monthly Return Median Monthly Return Std Deviation of Monthly Return Variance of Monthly Return #2 AAPL & WMT Average Monthly Return Median Monthly Return Std Deviation of Monthly Return Variance of Monthly Return 0.00% Correlation coefficient Between AAPL & WMT #4 Correlation coefficient Between AAPL & MSFT Analytical Variance Analytical Standard Deviation 0.00% #S Average of Standard Deviations Average of Monthly Returns 0.00% 0.00% 0.00% 0.00% #6 Comment on Risk Reduction & Correlation: Risk Reduction as % of Average Std Dev #3 AAPL WMT AAPL & WMT AAPL & MSFT Monthly Return Monthly Return MSFT Monthly Return Monthly Return Monthly Return 28.54 10/31/12 $ 85.05 11/30/12 83.61 12/31/12 76.02 1/31/13 65.07 2/28/13 63.06 3/29/13 63.24 4/30/13 63.25 5/31/13 64.25 6/28/13 56.65 7/31/13 64.65 8/30/13 69.60 9/30/13 68.11 10/31/13 74.67 11/29/13 79.44 12/31/13 80.15 1/31/14 71.51 2/28/14 75.18 3/31/14 76.68 4/30/14 84.30 -1.69% -9.07% -14.41% -3.09% 0.29% 0.03% 1.57% -11.83% 14.12% 7.66% -2.15% 9.64% % 6.38% 0.89% -10.77% 5.12% 2.00% 9.94% $ 75.02 72.02 68.23 69.95 70.78 74.83 77.72 74.84 74.49 77.94 72.98 73.96 76.75 81.01 78.69 74.68 74.70 76.43 79.71 4.00% -5.26% 2.52% 1.19% 5.72% 3.86% -3.71% -0.47% 4.63% 6.36% 1.34% 3.77% 5.55% -2.86% -5.10% 0.03% 2.32% 4.29% 26.62 26.71 27.45 27.80 28.61 33.10 34.90 34.55 31.84 33.40 33.28 35.41 38.13 37.41 37.84 38.31 40.99 40.40 -6.74% 0.36% 2.77% 1.28% 2.90% 15.71% 5.44% -1.02% -7.83% 4.90% -0.36% 6.39% 7.70% -1.89% 1.15% 1.24% 7.00% -1.44% $ 100.00 $ 97,16 $ 90.19 $ 84.83 $ 84.02 $ 86.55 $ 88.23 $ 87.29 $ 81.92 $ 89.60 $ 90.19 $ 89.82 $ 95.84 $ 101.56 $ 100.56 $ 92.58 $ 94.97 $ 97.02 $ 103.92 $ 100.00 -2.84% $ 95.78 -7.17% $ 91.61 -5.94% $ 86.28 -0.95% $ 85.49 3.00% $ 86.85 1.94% $ 93.69 -1.07% $ 96.97 -6.15% $ 90.74 9.38% $ 93.60 0.65% $ 99.48 -0.40% $ 98.23 6.71% $ 106.10 5.97% $ 113.57 -0.99% $ 113.00 -7.93% $ 107.57 2.57% $ 110.99 2.16% $ 115.98 7.12% $ 120.91 -4.22% -4.36% -5.82% -0.91% 1.59% 7.87% 3.50% -6.42% 3.15% 6.28% -1.25% 8.01% 7.04% -0.50% 4.81% 3.18% 4.50% 4.25% 3/31/14 4/30/14 5/30/14 6/30/14 7/31/14 8/29/14 9/30/14 10/31/14 11/28/14 12/31/14 1/30/15 2/27/15 3/31/15 4/30/15 5/29/15 6/30/15 7/31/15 8/31/15 9/30/15 10/30/15 11/30/15 12/31/15 1/29/16 2/29/16 3/31/16 4/29/16 5/31/16 6/30/16 7/29/16 8/31/16 9/30/16 10/31/16 11/30/16 12/30/16 1/31/17 2/28/17 3/31/17 4/28/17 5/31/17 6/30/17 7/31/17 8/31/17 9/29/17 10/31/17 11/30/17 12/29/17 1/31/18 2/28/18 3/30/18 4/30/18 5/31/18 6/29/18 7/31/18 8/31/18 9/28/18 10/31/18 76.68 84.30 90.43 92.93 95.60 102.50 100.75 108.00 118.93 110.38 117.16 128.46 124.43 125.15 130.28 125.43 121.30 112.76 110.30 119.50 118.30 105.26 97.34 96.69 108.99 93.74 99.86 95.60 104.21 106.10 113.05 113.54 110.52 115.82 121.35 136.99 143.66 143.65 152.76 144.02 148.73 164.00 154.12 169.04 171.85 169.23 167.43 178.12 167.78 165.26 186.87 185.11 190.29 227.63 225.74 218.86 2.00% 9.94% 7.27% 2.77% 2.87% 7.22% -1.71% 7.20% 10.12% -7.19% 6.14% 9.64% 3.14% 0.58% 4.10% -3.73% -3.29% -7.04% -2.18% 8.34% -1.00% -11.02% -7.52% 0.67% 12.72% -13.99% 6.53% -4.27% 9.01% 1.81% 6.55% 0.43% -2.66% % 4.80% 4.77% 12.89% 4.87% -0.01% 6.34% -5.72% 3.27% 10.27% 6.02% 9.68% 1.66% -1.52% -1.06% 6.38% 5.81% -1.50% 13.08% -0.94% 2.80% 19.62% -0.83% -3.05% 76.43 79.71 76.77 75.07 73.58 75.50 76.47 76.27 87.54 85.88 84.98 83.93 82.25 78.05 74.27 70.93 71.98 64.73 64.84 57.24 58.84 61.30 66.36 66.34 68.49 66.87 70.78 73.02 72.97 71.44 72.12 70.02 70.43 69.12 66.74 70.93 72.08 75.18 78.60 75.68 79.99 78.07 78.14 87.31 97.23 98.75 106.60 90.01 88.97 88.46 82.54 85.65 89.23 95.86 93.91 100.28 2.32% 4.29% -3.69% -2.21% -1.98% 2.61% 1.28% -0.26% 14.78% -1.90% -1.05% -1.24% -2.00% -5.11% 4.84% 4.50% 1.48% -10.07% 0.17% -11.72% 2.80% 4.18% 8.25% -0.03% 3.24% -2.37% 5.85% 3.16% -0.07% -2.10% 0.95% -2.91% 0.59% -1.86% -3.44% % 6.28% 1.62% 4.30% 4.55% -3.72% 5.70% -2.40% 0.09% 11.74% 11.36% 1.56% 7.95% -15.56% -1.16% -0.57% -6.69% 3.77% 4.18% 7.43% -2.03% 6.78% 40.99 40.40 40.94 41.70 43.16 45.43 46.36 46.95 47.81 46.45 40.40 43.85 40.66 48.64 46.86 44.15 46.70 43.52 44.26 52.64 54.35 55.48 55.09 50.88 55.23 49.87 53.00 51.17 56.68 57.46 57.60 59.92 60.26 62.14 64.65 63.98 65.86 68.46 69.84 68.93 72.70 74.77 74.49 83.18 84.17 85.54 95.01 93.77 91.27 93.52 98.84 98.61 106.08 112.33 114.37 106.81 7.00% -1.44% 1.34% 1.86% 3.50% 5.26% 2.05% 1.27% 1.83% -2.84% -13.02% 8.54% -7.29% 19.64% -3.66% -5.78% 5.78% 6.81% 1.70% 18.93% 3.25% 2.08% -0.70% -7.64% 8.55% -9.70% 6.28% -3.45% 10.77% 1.38% 0.24% 4.03% 0.57% 3.12% 4,04% -1.04% 2.94% 3.95% 2.02% -1.30% 5.47% 2.85% -0.37% 11.67% 1.19% 1.63% 11.07% -1.31% -2.67% 2.47% 5.69% -0.23% 7.58% 5.89% 1.82% -6.61% $ 97.02 $ 103.92 $ 105.78 $106.07 $ 106.54 $ 111.78 $ 111.54 $ 115.41 $ 129.78 $ 123.88 $ 127.04 $132.38 $ 128.98 $ 126.06 $ 125.59 $ 120.42 $ 119.33 $ 109.12 $ 108.03 $ 106.20 $ 107.15 $ 103.49 $ 103.86 $ 103.50 $ 111.76 $ 102.62 $ 108.97 $ 108.37 $ 113.21 $ 113.05 $ 117.29 $ 115.84 $ 114.64 $ 116.32 $ 117.10 $ 128.32 $ 132.48 $ 135.33 $ 142.70 $ 135.96 $ 142.06 $ 147.65 $ 143.26 $ 158.61 $ 168.93 $168.97 $ 174.78 $ 166.76 $ 160.96 $ 159.29 $164.37 $ 166.70 $ 172.51 $ 195.85 $ 193.04 $ 196.65 2.16% $ 115.98 7.12%'$ 120.91 1.79% $ 126.12 0.28% $ 129.03 0.44% $ 133.14 4.91% $ 141.45 ' -0.21% $ 141.69 3.47% $ 147.69 12.45% $ 156.51 4.54% $ 148.56 2.55% $ 143.55 4.20% $ 156.60 -2.57% $ 148.44 -2.26%'$ 163.44 -0.37% $ 163.80 4.11% $ 156.01 -0.90% $ 157.95 -8.56% $ 147.02 -1.01% $ 146.66 -1.69%$ 166.66 0.90%'$ 168.53 -3.42% $ 161.00 0.37% $ 154.37 -0.35% $ 147.96 7.98% $ 163.70 -8.18%$ 144.30 6.19% $ 153.54 -0.55%$ 147.61 4.47% $ 162.21 -0.14% $ 164.79 $ 3.75% $ 170.39 -1.24%'$ 174.19 - 1.04%'$ 172.37 1.47% $ 179.19 0.67% $ 187.09 9.58% $ 198.18 3.25% $ 205.91 2.15% $ 209.97 5.45% $ 218.74 4.72% $ 211.06 4.48% $ 220.29 3.93% $ 234.73 -2.97% $ 227.22 10.71%'$ 251.47 6.51% $ 255,06 0.02%'$ 255.19 3.44% $ 267.96 $ 4.59% $ 274.76 -3.48%$ 263.13 -1.04%'$ 264.39 3.19%'$ 289.20 1.41% $ 287.50 3.49%'$ 302.41 13.53%$ 340.99 % -1.43%'$ 342.67 1.87%'$ 326.13 4.507 4.25% 4.30% 2.31% 3.19% 6.24% 0.17% 4.23% 5.98% -5.02% -3.44% 9.09% -5.21% 10.11% 0.22% -4.75% 1.24% -6.92% -0.24% 13.64% 1.12% 4.47% 4.11% -4.15% 10.64% -11.85% 6.40% -3.86% 9.89% 1.59% % 3.40% 2.23% -1.05% 3.96% 4.41% 5.93% 3.90% 1.97% 4.18% -3.51% 4.37% 6.56% -3.20% 10.67% 1.43% 0.05% 5.00% 2.54% -4.24% 0.48% 9.38% % -0.59% 5.19% 12.76% 0.49% -4.83%
