the last 3 photos are part of the same question. Its the information for the monthly returns
The Excel workbook titled "Return.xlsx" (available on the class web page) contains monthly returns on 4 stocks (FB: Facebook, AMZN: Amazon, MSFT: Microsoft, CMG: Chipotle) and one- month US T-bills for the period May 2012 -Sept. 2020. 1. For the 4 stocks and T-bill, calculate the following statistics using the return data/sample from 2012 to 2020: (20 points) a. Find the arithmetic mean, variance, and standard deviation of monthly returns. Also, state the annualized mean and standard deviation, assuming monthly compounding. (5 points) b. Find the skewness and kurtosis of monthly returns. Also interpret the findings. (5 points) c. Find the pair-wise covariances (in the form of a covariance matrix) for each set of assets. (5 points) d. Find the pair-wise correlations (in the form of a correlation matrix) for each set of assets. (5 points) 2. Find the average monthly risk premium earned by each of the four stocks over this period of time and the Sharpe Ratio for each of the four stocks? (Hint: A risk premium is defined as return of an investment in excess of risk-free rate and Sharpe ratio is defined as risk premium of an investment divided by the standard deviation of the investment. It is well- known that T-bill can be considered a proxy for risk-free investment. You have calculated average monthly return for cach stock and that for the T-bill investment. So, you have all the information required for the calculation of average monthly risk premium for each of the four stocks.) (5 points) P22 x x B D E G H 1 Date FB return AMZN return MSFT return CMG return T-bil return 2 5/1/12 5.07% 7.25% 4 80% -8.02% 0.01% 3 6/1/12 -30.19% 2.17% -3.66% -23.06% 0.00% 4 7/1/12 -16.81% 6.42% 4.58% -1.26% 0.00% 5 8/1/12 19.93% 2.44% -3.44% 10.01% 0.01% 6 9/1/12 -2.54% -8.43% -4.10% -19.84% 0.01% 7 10/1/12 32.64% 8.23% -6.73% 3.63% 0.01% 8 11/1/12 -4.93% -0.47% 0.34% 12.77% 0.01% 9 12/1/12 16.38% 5.83% 2.77% 3.21% 0.01% 10 1/1/13 -12.04% -0.46% 1.28% 3.19% 0.00% 11 2/1/13 -6.13% 0.84% 2.91% 2.87% 0.00% 12 3/1/13 8.56% -4.76% 15.69% 11.45% 0.00% 13 4/1/13 -12.32% 6.06% 5.44% -0.60% 0.00% 14 5/1/13 2.18% 3.15% -1.03% 0.93% 0.00% 15 6/1/13 47.91% 8.47% -7.82% 13.15% 0.00% 16 7/1/13 12.20% -6.72% 4.90% -0.99% 0.00% 17 8/1/13 21.65% 11.27% -0.36% 5.05% 0.00% 18 9/1/13 -0.04% 16.44% 6.40% 22.89% 0.00% 19 10/1/13 -6.37% 8.13% 7.68% -0.59% 0.00% 20 11/1/13 16.25% 1.31% -1.39% 1.70% 0.00% 21 12/1/13 14.49% 10.06% 1.15% 3.60% 0.00% 22 1/1/14 9.41% 0.95% 1.24% 2.40% 0.00% 2/1/14 - 12.01% -7.11% 7.00% 0.50% 0.00% 24 3/1/14 -0.76% -9.58% -1.44% -12.24% 0.00% 25 4/1/14 5.89% 2.77% 1.34% 9.75% D.DOX 26 5/1/14 6.30% 3.91% 1.86% 8.30% 0.DOX 27 6/1/14 7.97% -3.63% 3.50% 13.509 0.DOS 28 7/1/14 2.99% 8.32% 5.26% 0.91% 0.00% 29 8/1/14 5.64% -4.90% 2.05% -1.78% 0.00% 30 9/1/14 -5.12% 5.27% 1.2756 4.29% 0.00% 10/1/14 3.61% 10.86% 183% 4.02% ODOX 32 11/1/14 0.41% -8.35% -2.84% 3.15% 0.00% 33 12/1/14 -2.70% 14.24% 13.02% 3.70% 0.00% 34 1/1/15 4.03% 7.23% 8.54% -6.32% 0.BOX 35 2/1/15 4.1296 2.12% -7.27% -2.17% 0.00% 36 3/1/15 4.20% 13.35% 19.63% 4.49% 0.00N 37 4/1/15 0.53% 1.77% 3.6696 -0.94% 0.00% 5/1/15 3.31% 1.13% 5.78% 1.71% 0.OON 39 6/1/15 9.61% 23.519 5.78% 22.68% 0.00% 40 7/1/15 -4.55 -4.34% 6.813 -20356 0.00% 8/1/15 0.19% -0.19% 17ON 0.95% 0.00% 9/1/15 1343% 22.27% 58.93% 11.11% 000N return Stett + Ready K71 Vfx G H 42 13 23 2.23% 0.40% 7.21% -4.71% 6.72% 3.05% 1.05% -3.81% 8.45% 1.76% 1.70% 2.12% -9.60% -2.85% 13.27% 4.01% 9/1/15 43 10/1/15 44 11/1/15 45 12/1/15 46 1/1/16 47 2/1/16 48 3/1/16 49 4/1/16 50 5/1/16 51 6/1/16 52 7/1/16 53 8/1/16 54 9/1/16 55 10/1/16 56 11/1/16 57 12/1/16 58 1/1/17 59 2/1/17 60 3/1/17 61 4/1/17 62 5/1/17 63 6/1/17 7/1/17 65 8/1/17 55 9/1/17 67 10/1/17 58 11/1/17 69 12/1/17 70 1/1/18 71 2/1/18 72 3/1/18 73 4/1/18 74 5/1/18 6/1/18 76 7/1/18 77 8/1/18 9/1/18 79 10/1/18 80 11/1/18 12/1/18 1/1/19 83 2/1/19 G 22227% 6.22% 1.67% - 13.15% -5.87% 7.44% 11.11% 9.58% -0.99% 6.04% 1.36% 8.86% -5.67% 4.97% 2.41% 7.13% 2.62% 4.91% 4.34% 7.53% -2.68% 2.04% -0.73% -1.96% 14.97% 6.47% -0.62% 24.06% 4.24% - 4.30% 8.21% 4.05% 4.80% 5.77% 0.81% -0.32% 12.10% 1.61% -0.64% 5.38% -1.60% -0.41% 5.91% -4.59% -10.39% 7,64% 11.50% 1.32% 11.19% 1.83% 6.41% -7.70% -7.37% -6.779 27.16% 3.14% 3.25% D 18:9333 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.6196 3.82% 8.4DN 2.82% 7.28% 5.28 E SIRET -9.488 -17.20 -5.60% 12.40 -7.50% -10.62% 4.99% -8.87% 5.27% -2.42% 2.36% - 14.81% 9.86% -4.80% 11.69% -0.64% 6.40N 6.50% 0.61% -12.83% - 17:38% -7.87% -2 80% -11.67% 11.95% 5.05% 12.36% -1.96% 1.48% 31.02% 1.62% 0.28 0.53% 9.57% -4.35% 1285 2.80% 8.75% 22.65 14.71% 16 923 0.001 0.00% 0.0096 0.01% 0.01% 0.02% 0.02% 0.01% 0.01% 0.02% 0.02% 0.02% 0.02% 0.02% 0.01% 0.03% 0.04% 0.04% 0.03% 0.05% 0.06% 0.06% 0.07% 0.09% 0.09% 0.09% 0.08% 0.09% 0.11% 0.11% 0.12% 0.14% 0.14% 0.1456 0.16% 0.16% 0.15% 0.19% 0.18% 0.19 DIZIN 0.18% TOM 4.57% 13.24% 0.48 -20.225 5.77% -11.13% 14 43% 4.59% 8.59% return Sheet1 C F - 78 9/1/18 79 10/1/18 80 11/1/18 81 12/1/18 82 1/1/19 83 2/1/19 84 3/1/19 85 4/1/19 86 5/1/19 87 6/1/19 88 7/1/19 89 8/1/19 90 9/1/19 91 10/1/19 92 11/1/19 93 12/1/19 94 1/1/20 95 2/1/20 96 3/1/20 97 4/1/20 98 5/1/20 99 6/1/20 100 7/1/20 101 8/1/20 102 9/1/20 103 104 105 106 207 108 109 110 111 112 B -7A/0% -7.37% -6.77% 27.16% -3.14% 3.25% 16.02% -8.24% 8.75% 0.64% -4.41% -4.09% 7.62% 5.21% 1.79% -1.63% -4.68% -13.34% 22.73% 9.96% 0.88% 11.71% 15.58% -10.68% 0.46% -20.22% 5.77% -11.13% 14.43% -4.59% 8.59% 8.19% -7.86% 6.68% -1.42% -4.85% -2.27% 2.35% 1.36% 2.61% 8.71% -6.22% 3.50% 26.89% -1.28% 12.96% 14.71% 9.05% -8.76% 3.58% D -5.61% 3.82% -8.40% 2.82% 7.28% 5.28% 10.73% -5.30% 8.31% 1.72% 1.17% 0.85% 3.12% 5.59% 4.17% 7.95% -4.83% -2.65% 13.63% 2.25% 11.06% 0.74% 10.01% -6.74% -3.74% E 1.28% 2.80% -8.75% 22.65% 14.71% 16.92% -3.14% -4.08% 11.05% 8.55% 5.39% 0.24% -7.41% 4.60% 2.85% 3.54% -10.75% -15.41% 34.25% 14.27% 4.83% 9.77% 13.43% -5.08% -3.40% 0.15) 0.19% 0.18% 0.19% 0.21% 0.18% 0.19% 0.21% 0.21% 0.18% 0.19% 0.16% 0.18% 0.15% 0.12% 0.14% 0.13% 0.12% 0.12% 0.00% 0.01% 0.01% 0.01% 0.01% 0.01% 110 117 11. 119 120 The Excel workbook titled "Return.xlsx" (available on the class web page) contains monthly returns on 4 stocks (FB: Facebook, AMZN: Amazon, MSFT: Microsoft, CMG: Chipotle) and one- month US T-bills for the period May 2012 -Sept. 2020. 1. For the 4 stocks and T-bill, calculate the following statistics using the return data/sample from 2012 to 2020: (20 points) a. Find the arithmetic mean, variance, and standard deviation of monthly returns. Also, state the annualized mean and standard deviation, assuming monthly compounding. (5 points) b. Find the skewness and kurtosis of monthly returns. Also interpret the findings. (5 points) c. Find the pair-wise covariances (in the form of a covariance matrix) for each set of assets. (5 points) d. Find the pair-wise correlations (in the form of a correlation matrix) for each set of assets. (5 points) 2. Find the average monthly risk premium earned by each of the four stocks over this period of time and the Sharpe Ratio for each of the four stocks? (Hint: A risk premium is defined as return of an investment in excess of risk-free rate and Sharpe ratio is defined as risk premium of an investment divided by the standard deviation of the investment. It is well- known that T-bill can be considered a proxy for risk-free investment. You have calculated average monthly return for cach stock and that for the T-bill investment. So, you have all the information required for the calculation of average monthly risk premium for each of the four stocks.) (5 points) P22 x x B D E G H 1 Date FB return AMZN return MSFT return CMG return T-bil return 2 5/1/12 5.07% 7.25% 4 80% -8.02% 0.01% 3 6/1/12 -30.19% 2.17% -3.66% -23.06% 0.00% 4 7/1/12 -16.81% 6.42% 4.58% -1.26% 0.00% 5 8/1/12 19.93% 2.44% -3.44% 10.01% 0.01% 6 9/1/12 -2.54% -8.43% -4.10% -19.84% 0.01% 7 10/1/12 32.64% 8.23% -6.73% 3.63% 0.01% 8 11/1/12 -4.93% -0.47% 0.34% 12.77% 0.01% 9 12/1/12 16.38% 5.83% 2.77% 3.21% 0.01% 10 1/1/13 -12.04% -0.46% 1.28% 3.19% 0.00% 11 2/1/13 -6.13% 0.84% 2.91% 2.87% 0.00% 12 3/1/13 8.56% -4.76% 15.69% 11.45% 0.00% 13 4/1/13 -12.32% 6.06% 5.44% -0.60% 0.00% 14 5/1/13 2.18% 3.15% -1.03% 0.93% 0.00% 15 6/1/13 47.91% 8.47% -7.82% 13.15% 0.00% 16 7/1/13 12.20% -6.72% 4.90% -0.99% 0.00% 17 8/1/13 21.65% 11.27% -0.36% 5.05% 0.00% 18 9/1/13 -0.04% 16.44% 6.40% 22.89% 0.00% 19 10/1/13 -6.37% 8.13% 7.68% -0.59% 0.00% 20 11/1/13 16.25% 1.31% -1.39% 1.70% 0.00% 21 12/1/13 14.49% 10.06% 1.15% 3.60% 0.00% 22 1/1/14 9.41% 0.95% 1.24% 2.40% 0.00% 2/1/14 - 12.01% -7.11% 7.00% 0.50% 0.00% 24 3/1/14 -0.76% -9.58% -1.44% -12.24% 0.00% 25 4/1/14 5.89% 2.77% 1.34% 9.75% D.DOX 26 5/1/14 6.30% 3.91% 1.86% 8.30% 0.DOX 27 6/1/14 7.97% -3.63% 3.50% 13.509 0.DOS 28 7/1/14 2.99% 8.32% 5.26% 0.91% 0.00% 29 8/1/14 5.64% -4.90% 2.05% -1.78% 0.00% 30 9/1/14 -5.12% 5.27% 1.2756 4.29% 0.00% 10/1/14 3.61% 10.86% 183% 4.02% ODOX 32 11/1/14 0.41% -8.35% -2.84% 3.15% 0.00% 33 12/1/14 -2.70% 14.24% 13.02% 3.70% 0.00% 34 1/1/15 4.03% 7.23% 8.54% -6.32% 0.BOX 35 2/1/15 4.1296 2.12% -7.27% -2.17% 0.00% 36 3/1/15 4.20% 13.35% 19.63% 4.49% 0.00N 37 4/1/15 0.53% 1.77% 3.6696 -0.94% 0.00% 5/1/15 3.31% 1.13% 5.78% 1.71% 0.OON 39 6/1/15 9.61% 23.519 5.78% 22.68% 0.00% 40 7/1/15 -4.55 -4.34% 6.813 -20356 0.00% 8/1/15 0.19% -0.19% 17ON 0.95% 0.00% 9/1/15 1343% 22.27% 58.93% 11.11% 000N return Stett + Ready K71 Vfx G H 42 13 23 2.23% 0.40% 7.21% -4.71% 6.72% 3.05% 1.05% -3.81% 8.45% 1.76% 1.70% 2.12% -9.60% -2.85% 13.27% 4.01% 9/1/15 43 10/1/15 44 11/1/15 45 12/1/15 46 1/1/16 47 2/1/16 48 3/1/16 49 4/1/16 50 5/1/16 51 6/1/16 52 7/1/16 53 8/1/16 54 9/1/16 55 10/1/16 56 11/1/16 57 12/1/16 58 1/1/17 59 2/1/17 60 3/1/17 61 4/1/17 62 5/1/17 63 6/1/17 7/1/17 65 8/1/17 55 9/1/17 67 10/1/17 58 11/1/17 69 12/1/17 70 1/1/18 71 2/1/18 72 3/1/18 73 4/1/18 74 5/1/18 6/1/18 76 7/1/18 77 8/1/18 9/1/18 79 10/1/18 80 11/1/18 12/1/18 1/1/19 83 2/1/19 G 22227% 6.22% 1.67% - 13.15% -5.87% 7.44% 11.11% 9.58% -0.99% 6.04% 1.36% 8.86% -5.67% 4.97% 2.41% 7.13% 2.62% 4.91% 4.34% 7.53% -2.68% 2.04% -0.73% -1.96% 14.97% 6.47% -0.62% 24.06% 4.24% - 4.30% 8.21% 4.05% 4.80% 5.77% 0.81% -0.32% 12.10% 1.61% -0.64% 5.38% -1.60% -0.41% 5.91% -4.59% -10.39% 7,64% 11.50% 1.32% 11.19% 1.83% 6.41% -7.70% -7.37% -6.779 27.16% 3.14% 3.25% D 18:9333 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.6196 3.82% 8.4DN 2.82% 7.28% 5.28 E SIRET -9.488 -17.20 -5.60% 12.40 -7.50% -10.62% 4.99% -8.87% 5.27% -2.42% 2.36% - 14.81% 9.86% -4.80% 11.69% -0.64% 6.40N 6.50% 0.61% -12.83% - 17:38% -7.87% -2 80% -11.67% 11.95% 5.05% 12.36% -1.96% 1.48% 31.02% 1.62% 0.28 0.53% 9.57% -4.35% 1285 2.80% 8.75% 22.65 14.71% 16 923 0.001 0.00% 0.0096 0.01% 0.01% 0.02% 0.02% 0.01% 0.01% 0.02% 0.02% 0.02% 0.02% 0.02% 0.01% 0.03% 0.04% 0.04% 0.03% 0.05% 0.06% 0.06% 0.07% 0.09% 0.09% 0.09% 0.08% 0.09% 0.11% 0.11% 0.12% 0.14% 0.14% 0.1456 0.16% 0.16% 0.15% 0.19% 0.18% 0.19 DIZIN 0.18% TOM 4.57% 13.24% 0.48 -20.225 5.77% -11.13% 14 43% 4.59% 8.59% return Sheet1 C F - 78 9/1/18 79 10/1/18 80 11/1/18 81 12/1/18 82 1/1/19 83 2/1/19 84 3/1/19 85 4/1/19 86 5/1/19 87 6/1/19 88 7/1/19 89 8/1/19 90 9/1/19 91 10/1/19 92 11/1/19 93 12/1/19 94 1/1/20 95 2/1/20 96 3/1/20 97 4/1/20 98 5/1/20 99 6/1/20 100 7/1/20 101 8/1/20 102 9/1/20 103 104 105 106 207 108 109 110 111 112 B -7A/0% -7.37% -6.77% 27.16% -3.14% 3.25% 16.02% -8.24% 8.75% 0.64% -4.41% -4.09% 7.62% 5.21% 1.79% -1.63% -4.68% -13.34% 22.73% 9.96% 0.88% 11.71% 15.58% -10.68% 0.46% -20.22% 5.77% -11.13% 14.43% -4.59% 8.59% 8.19% -7.86% 6.68% -1.42% -4.85% -2.27% 2.35% 1.36% 2.61% 8.71% -6.22% 3.50% 26.89% -1.28% 12.96% 14.71% 9.05% -8.76% 3.58% D -5.61% 3.82% -8.40% 2.82% 7.28% 5.28% 10.73% -5.30% 8.31% 1.72% 1.17% 0.85% 3.12% 5.59% 4.17% 7.95% -4.83% -2.65% 13.63% 2.25% 11.06% 0.74% 10.01% -6.74% -3.74% E 1.28% 2.80% -8.75% 22.65% 14.71% 16.92% -3.14% -4.08% 11.05% 8.55% 5.39% 0.24% -7.41% 4.60% 2.85% 3.54% -10.75% -15.41% 34.25% 14.27% 4.83% 9.77% 13.43% -5.08% -3.40% 0.15) 0.19% 0.18% 0.19% 0.21% 0.18% 0.19% 0.21% 0.21% 0.18% 0.19% 0.16% 0.18% 0.15% 0.12% 0.14% 0.13% 0.12% 0.12% 0.00% 0.01% 0.01% 0.01% 0.01% 0.01% 110 117 11. 119 120
