- Please Show Work Return Data Month By Month From 1 Sep 6 To 1 (87.78 KiB) Viewed 30 times
QUESTION 2 (4 points) The exam spreadsheet includes five years of price data for 6 U.S. stocks. Fill in the template below. Notes: Return: The returns are approximated as the growth rate of the share prices. Excel function: ELN('Stock price data"!B9/ Stock price data'!B8) Sigma: The sigma measures the variability of the return, which is derived by taking the square root of the variance. Excel function: =SQRT|Variance) Beta: The beta coefficient measures the volatility of an individual stock compared to the systematic risk of the entire market. In statistical terms, beta represents the slope of the line through a regression of data points. E.g. Excel function: ==SLOPE(B17:B76,SH$17:$H$76) H S&P500 A B с D E F 1 QUESTION 2: RETURNS AND BETAS Johnson Exxon- Goldman- and 2 Verizon Mobile Prudential Sachs Hershey Johnson 3 VZ XOM PRU GS HSY JNJ 4 Monthly returns 5 Mean 6 Variance 7 Sigma 8 Beta 9 10 Annual returns 11 Mean 12 Variance 13 Sigma 14 15 Return data, month-by-month 16 1-Sep-06 17 2-Oct-06 181-Nov-06 19 1-Dec-06 20 3-Jan-07
A1 A H x fx SIX STOCKS AND THE S&P 500 B D E F SIX STOCKS AND THE S&P 500 Johnson Exxon- Goldman- and Verizon Mobile Prudential Sachs Hershey Johnson VZ XOM PRU GS HSY JNJ S&P500 99.27 344.92 22.91 57.23 12.62 173.08 <-- Market value in $Billion 2 3 Market 4 value 5 6 7 Date 8 3-Aug-06 9 1-Sep-06 10 2-Oct-06 11 1-Nov-06 12 1-Dec-06 13 3-Jan-07 14 1-Feb-07 15 1-Mar-07 16 2-Apr-07 17 1-May-07 18 1-Jun-07 19 2-Jul-07 20 1-Aug-07 21 4-Sep-07 22 1-Oct-07 23 1-Nov-07 24 3-Dec-07 VZ 25.95 27.39 27.59 27.04 28.82 30.13 29.26 29.66 30.19 34.42 32.55 34.03 33.44 35.35 37.13 34.83 35.21 XOM 60.78 60.27 64.15 69.29 69.13 66.84 64.94 68.35 71.91 75.67 76.32 77.45 78.31 84.55 84.03 81.76 85.91 Price data PRU GS 66.79 142.31 69.37 161.96 69.99 182.04 75.02 186.85 79.05 191.21 82.06 203.84 83.72 193.84 83.10 198.53 87.46 210.37 93.93 222.12 89.52 208.58 81.60 181.57 82.66 169.68 89.84 208.95 89.05 239.38 87.73 218.83 86.70 207.64 HSY 47.07 46.62 46.15 46.44 43.66 44.75 46.61 48.16 48.43 46.69 44.84 40.83 41.46 41.38 38.44 35.85 35.39 JNJ S&P500 55.96 1,303.82 56.20 1,335.85 58.33 1,377.94 57.36 1,400.63 57.46 1,418.30 58.13 1,438.24 55.09 1,406.82 52.75 1,420.86 56.21 1,482.37 55.75 1,530.62 54.29 1,503.35 53.30 1,455.27 54.81 1,473.99 58.28 1,526.75 57.81 1,549.38 60.46 1,481.14 59.53 1,468.36
25 2-Jan-08 26 1-Feb-08 27 3-Mar-08 28 1-Apr-08 29 1-May-08 30 2-Jun-08 31 1-Jul-08 32 1-Aug-08 33 2-Sep-08 34 1-Oct-08 35 3-Nov-08 36 1-Dec-08 37 2-Jan-09 38 2-Feb-09 39 2-Mar-09 40 1-Apr-09 41 1-May-09 42 1-Jun-09 43 1-Jul-09 44 3-Aug-09 45 1-Sep-09 46 1-Oct-09 47 2-Nov-09 48 1-Dec-09 49 4-Jan-10 50 1-Feb-10 51 1-Mar-10 31.61 29.57 29.67 31.84 31.83 29.29 28.51 29.42 26.88 25.25 27.79 28.85 25.79 24.64 26.08 26.57 25.62 26.91 28.53 27.61 26.93 26.74 28.43 29.94 26.97 26.52 28.44 78.59 80.13 77.90 85.71 82.11 81.53 74.40 74.39 72.21 68.92 74.95 74.65 71.52 63.82 64.01 62.66 65.57 66.10 66.55 65.78 65.27 68.18 71.83 65.24 61.65 62.60 64.50 78.35 68.00 72.92 70.55 69.61 55.67 64.27 68.69 67.10 27.96 20.94 29.20 24.84 15.83 18.35 27.86 38.51 35.91 42.71 48.80 48.15 43.64 48.80 48.71 48.93 51.30 59.22 193.02 164.08 159.97 185.45 170.95 169.49 178.69 159.20 124.28 90.09 76.93 82.19 78.62 89.19 103.82 125.83 141.93 144.75 160.32 162.79 181.37 167.42 167.28 166.47 146.63 154.50 168.61 32.49 33.58 34.11 33.85 35.77 29.92 33.57 33.21 36.38 34.26 33.43 32.26 34.62 31.55 32.55 33.85 33.26 34.00 37.73 37.33 36.98 35.96 33.93 34.34 34.95 38.45 41.40 56.35 1,378.55 55.67 1,330.63 58.28 1,322.70 60.28 1,385.59 60.38 1,400.38 58.21 1,280.00 61.95 1,267.38 64.14 1,282.83 63.09 1,166.36 55.86 968.75 53.79 896.24 54.94 903.25 52.97 825.88 46.29 735.09 48.70 797.87 48.48 872.81 51.52 919.14 53.05 919.32 56.87 987.48 56.91 1,020.62 57.33 1,057.08 55.60 1,036.19 59.64 1,095.63 61.13 1,115.10 59.66 1,073.87 60.25 1,104.49 62.35 1,169.43
51 1-Mar-10 52 1-Apr-10 53 3-May-10 54 1-Jun-10 55 1-Jul-10 56 2-Aug-10 57 1-Sep-10 58 1-Oct-10 59 1-Nov-10 60 1-Dec-10 61 3-Jan-11 62 1-Feb-11 63 1-Mar-11 64 1-Apr-11 65 2-May-11 66 1-Jun-11 67 1-Jul-11 68 1-Aug-11 28.44 26.91 25.62 26.09 27.55 27.99 30.89 31.24 30.79 34.42 34.71 35.98 37.56 37.29 36.45 36.75 35.29 35.61 64.50 65.26 58.62 55.33 57.86 57.72 60.34 64.93 68.35 71.85 79.28 84.49 83.11 86.91 82.92 80.85 79.27 74.16 59.22 62.22 56.49 52.53 56.08 49.53 53.04 51.47 50.68 58.71 61.51 65.83 61.58 63.42 63.78 63.59 58.68 47.15 168.61 143.48 142.91 130.04 149.41 135.99 143.59 160.02 155.41 167.37 162.85 163.36 158.19 150.62 140.73 133.09 134.97 117.25 41.40 45.47 45.57 46.68 45.77 45.56 46.66 48.52 46.20 46.55 46.09 52.00 54.02 57.36 55.73 56.85 56.44 55.49 62.35 1,169.43 61.49 1,186.69 56.26 1,089.41 56.99 1,030.71 56.06 1,101.60 55.55 1,049.33 60.36 1,141.20 62.09 1,183.26 60.47 1,180.55 60.77 1,257.64 58.72 1,286.12 60.91 1,327.22 58.74 1,325.83 65.15 1,363.61 67.29 1,345.20 66.52 1,320.64 64.79 1,292.28 63.16 1,140.65