Question 3, Excel, Please Show me How to do it on excel Due Tonight, Thank you

please help due tonight

This homework asks you to claculate the return, standard deviation, and beta of 3 individual stocks, S&P 500 (proxy of the market portfolio), and 1 portfolio we construct from the individual stocks. Please fill in the answer of each question in the corresponding spreadsheet. The section for the answers are marked by red outside boarders. Please round all the numbers to the third degit. The total points you can get for this homework is 4.5 points. Dec. 14th Due Date: Q1 Calculate the return of the 3 stocks and S&P500 (0.3 points each). Plot the return of any 1 of the 3 stocks and the market return against the time (month) axis (0.1 points). (1.3 points total) Reminder: Simply ignore the dividend yield. Calculate return as follow: P: - P:-1 Ret = * 100% P,-1 Q2 Calculate the standard deviation and beta of the returns of the 3 stocks and S&P500 (0.2 points each). (1.6 points total) Reminder: For standard deviation, just use the built-in function =STDEV.P( For beta, use the add-in analysis toolpak, and select regression. What we need are the coefficients. Put the market risk premium as the X variable and the stock risk premium in your interest as the Y variable. The coefficient for the X variable is our beta. Don't forget that in CAPM, you don't regress market return on stock return. You regress market risk premium on individual stock risk premium. Month \ Equity Premium (%) January-01 S&P500 Return of your portfolio AAPL JPM AMZN Risk premium of your portfolio N/A N/A N/A N/A N/A N/A AAPL 0.000 0.000 0.000 0.000 your portfolio February-01 JPM AMZN S&P500 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 March-01 Standard Deviation 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 April-01 Beta May-01 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 June-01 0.000 0.000 0.000 Correlation w/ market 0.000 S&P 500 July-01 August-01 September-01 0.000 0.000 0.000 0.000 AAPL 0.000 0.000 0.000 0.000 JPM AMZN 0.000 0.000 0.000 0.000 October-01 November-01 0.000 0.000 0.000 0.000 your portfolio December-01 January-02 February-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 Answer to portfolio std. and beta: 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 March-02 April-02 May-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 June-02 0.000 0.000 0.000 0.000 July-02 August-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 September-02 October-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 November-02 0.000 0.000 0.000 0.000 Answer to correlation: December-02 January-03 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 February-03 0.000 0.000 0.000 0.000 March-03 April-03 May-03 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 June-03 0.000 0.000 0.000 0.000 July-03 August-03 September-03 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 October-03 0.000 0.000 0.000 0.000 November-03 December-03 0.000 0.000 0.000 0.000 January-04 February-04 March-04 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 April-04 0.000 0.000 0.000 0.000 0.000 May-04 0.000 0.000 0.000 June-04 0.000 0.000 0.000 0.000 July-04 August-04 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 September-04 3-Month T- Level of the S&P 500 Bill Rate Date Date 20010131 AAPL Price Alternate Ticker Price Alternate Ticker Price Alternate Date Ticker Date Index Date (%) 2 21.625 20010131 JPM 54.99 20010131 AMZN 17.3125 20010131 1366.01 20010131 4.88 20010228 AAPL 20010228 JPM 46.66 18.25 20010228 AMZN 10.1875 20010228 1239.94 20010228 4.42 20010330 AAPL 20010330 20010330 4 22.07 20010330 JPM 44.9 20010330 AMZN 10.23 1160.33 3.87 20010430 AAPL 20010531 AAPL 25.49 20010430 JPM 20010531 JPM 47.98 20010430 AMZN 15.78 20010430 1249,46 20010430 3.62 19.95 49.15 20010531 AMZN 16.69 20010531 1255,82 20010531 3.49 20010629 JPM 20010629 AAPL 23.25 44.6 20010629 AMZN 14.15 20010629 1224.42 20010629 20010731 3.51 1211.23 20010731 AAPL 18.79 20010731 JPM 43.3 20010731 AMZN 12.49 20010731 3.36 20010831 AAPL 18.55 20010831 JPM 39.4 20010831 AMZN 8.94 20010831 1133,58 20010831 2.64 20010928 AAPL 10 15.51 20010928 JPM 34.15 20010928 AMZN 5.97 20010928 1040.94 20010928 2.16 20011031 AAPL 20011031 JPM 20011031 AMZN 20011031 20011031 11 17.56 35.36 6.98 1059.78 1.87 20011130 AAPL 20011130 JPM 20011130 AMZN 12 21.3 37.72 11.32 20011130 1139.45 20011130 1.69 20011231 AAPL 13 21.9 20011231 JPM 36.35 20011231 AMZN 10.82 20011231 1148.08 20011231 1.65 24.72 14 20020131 AAPL 20020131 JPM 34.05 20020131 AMZN 14.19 20020131 1130.2 20020131 1.72 20020228 AMZN 15 20020228 AAPL 21.7 20020228 JPM 29.25 14.1 20020228 1106.73 20020228 1.79 20020328 AAPL 20020328 JPM 1147.39 16 23.67 35.65 20020328 AMZN 14.3 20020328 20020328 1.71 20020430 AAPL 20020430 JPM 20020430 17 24.27 35.1 20020430 AMZN 16.69 1076.92 20020430 1.73 20020531 AAPL 18 23.3 20020531 JPM 35.95 20020531 AMZN 18.23 20020531 1067.14 20020531 1.70 20020628 20020731 20020628 JPM 20020628 AMZN 19 20020628 AAPL 17.72 33.92 16.25 989.81 20020628 1.68 20020731 AAPL 24.96 20020731 JPM 20020731 AMZN 14,459 20 15.26 911.62 20020731 1.62 21 20020830 AAPL 14.75 20020830 JPM 26.4 20020830 AMZN 14.94 20020830 916.07 20020830 1.63 20020930 AAPL 20020930 22 14.5 20020930 JPM 18.99 20020930 AMZN 15.93 815.29 20020930 1.58 20021031 AAPL 20021031 JPM 20021031 23 16.07 20.75 20021031 AMZN 19.36 20021031 885.76 1.23 20021129 AAPL 25.17 936.31 24 15.5 20021129 JPM 20021129 AMZN 23.35 20021129 20021129 1.19 20021231 AAPL 20021231 JPM 20021231 25 14.33 24 20021231 AMZN 18.89 20021231 879.82 1.17 20030131 JPM 855.7 26 20030131 AAPL 14.36 23.34 20030131 AMZN 21.85 20030131 20030131 1.17 20030228 27 20030228 AAPL 15.01 20030228 JPM 20030331 JPM 22.68 20030228 AMZN 22.01 841.15 20030228 1.13 20030331 AAPL 23.71 28 14.14 20030331 AMZN 26.03 20030331 848.18 20030331 1.13 20030430 JPM 29 20030430 AAPL 14.22 29.35 20030430 AMZN 28.69 20030430 916.92 20030430 1.07 20030530 20030630 20030530 20030630 20030530 AAPL 17.95 20030530 JPM 35.89 963.59 0.92 30 32.86 20030530 AMZN 20030630 AAPL 20030731 AAPL 20030630 JPM 20030630 AMZN 31 19.06 34.18 36.32 974.5 0.90 20030731 AMZN 32 21.08 20030731 JPM 35.05 41.64 20030731 990.31 20030731 0.95 33 20030829 AAPL 22.61 20030829 JPM 34.22 20030829 AMZN 46.32 20030829 1008.01 20030829 0.94 20.72 34 20030930 AAPL 20030930 JPM 34.33 20030930 AMZN 48.43 20030930 995.97 20030930 0.92 20031031 AAPL 35 22.89 20031031 JPM 35.9 20031031 AMZN 54.43 20031031 1050.71 20031031 0.93 20031128 36 20031128 AAPL 20.91 20031128 JPM 35.4 20031128 AMZN 53.97 20031128 1058.2 0.90 20031231 AAPL 20031231 JPM 20031231 37 21.37 36.73 20031231 AMZN 52.62 1111.92 20031231 0.88 20040130 AAPL 20040130 JPM 38 22.56 38.89 20040130 AMZN 50.4 20040130 1131.13 20040130 0.93 20040227 AAPL 20040227 JPM 20040227 AMZN 39 23.92 41.02 43.01 20040227 1144.94 20040227 0.94 20040331 AAPL 20040331 AMZN 40 27.04 20040331 JPM 41.95 43.28 20040331 1126.21 20040331 0.94 20040430 AMZN 1107.3 41 20040430 AAPL 25.78 20040430 JPM 37.6 43.6 20040430 20040430 1.02 20040528 AAPL 1120.68 1140.84 1101.72 42 28.06 20040528 JPM 36.84 20040528 AMZN 48.5 20040528 20040528 1.27 20040630 JPM 20040630 AAPL 43 32.54 38.77 20040630 AMZN 54.4 20040630 20040630 1.33 20040730 AAPL 44 32.34 20040730 JPM 37.33 20040730 AMZN 38.92 20040730 20040730 1.48 45 20040831 AAPL 34.49 20040831 JPM 39.58 20040831 AMZN 38.14 20040831 1104.24 20040831 1.65 46 20040930 AAPL 38.75 20040930 JPM 39.73 20040930 AMZN 40.86 20040930 1114.58 20040930 1.76 20041029 JPM 34.13 20041029 47 20041029 AAPL 52.4 38.6 20041029 AMZN 1130.2 20041029 2.07 20041130 AAPL 48 67.05 20041130 JPM 37.65 20041130 AMZN 39.68 20041130 1173.82 20041130 2.19 20041231 JPM 39.01 49 20041231 AAPL 64.4 20041231 AMZN 44.29 20041231 1211.92 20041231 2.33 This homework asks you to claculate the return, standard deviation, and beta of 3 individual stocks, S&P 500 (proxy of the market portfolio), and 1 portfolio we construct from the individual stocks. Please fill in the answer of each question in the corresponding spreadsheet. The section for the answers are marked by red outside boarders. Please round all the numbers to the third degit. The total points you can get for this homework is 4.5 points. Dec. 14th Due Date: Q1 Calculate the return of the 3 stocks and S&P500 (0.3 points each). Plot the return of any 1 of the 3 stocks and the market return against the time (month) axis (0.1 points). (1.3 points total) Reminder: Simply ignore the dividend yield. Calculate return as follow: P: - P:-1 Ret = * 100% P,-1 Q2 Calculate the standard deviation and beta of the returns of the 3 stocks and S&P500 (0.2 points each). (1.6 points total) Reminder: For standard deviation, just use the built-in function =STDEV.P( For beta, use the add-in analysis toolpak, and select regression. What we need are the coefficients. Put the market risk premium as the X variable and the stock risk premium in your interest as the Y variable. The coefficient for the X variable is our beta. Don't forget that in CAPM, you don't regress market return on stock return. You regress market risk premium on individual stock risk premium. Month \ Equity Premium (%) January-01 S&P500 Return of your portfolio AAPL JPM AMZN Risk premium of your portfolio N/A N/A N/A N/A N/A N/A AAPL 0.000 0.000 0.000 0.000 your portfolio February-01 JPM AMZN S&P500 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 March-01 Standard Deviation 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 April-01 Beta May-01 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 June-01 0.000 0.000 0.000 Correlation w/ market 0.000 S&P 500 July-01 August-01 September-01 0.000 0.000 0.000 0.000 AAPL 0.000 0.000 0.000 0.000 JPM AMZN 0.000 0.000 0.000 0.000 October-01 November-01 0.000 0.000 0.000 0.000 your portfolio December-01 January-02 February-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 Answer to portfolio std. and beta: 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 March-02 April-02 May-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 June-02 0.000 0.000 0.000 0.000 July-02 August-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 September-02 October-02 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 November-02 0.000 0.000 0.000 0.000 Answer to correlation: December-02 January-03 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 February-03 0.000 0.000 0.000 0.000 March-03 April-03 May-03 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 June-03 0.000 0.000 0.000 0.000 July-03 August-03 September-03 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 October-03 0.000 0.000 0.000 0.000 November-03 December-03 0.000 0.000 0.000 0.000 January-04 February-04 March-04 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 April-04 0.000 0.000 0.000 0.000 0.000 May-04 0.000 0.000 0.000 June-04 0.000 0.000 0.000 0.000 July-04 August-04 0.000 0.000 0.000 0.000 0.000 0.000 0.000 0.000 September-04 3-Month T- Level of the S&P 500 Bill Rate Date Date 20010131 AAPL Price Alternate Ticker Price Alternate Ticker Price Alternate Date Ticker Date Index Date (%) 2 21.625 20010131 JPM 54.99 20010131 AMZN 17.3125 20010131 1366.01 20010131 4.88 20010228 AAPL 20010228 JPM 46.66 18.25 20010228 AMZN 10.1875 20010228 1239.94 20010228 4.42 20010330 AAPL 20010330 20010330 4 22.07 20010330 JPM 44.9 20010330 AMZN 10.23 1160.33 3.87 20010430 AAPL 20010531 AAPL 25.49 20010430 JPM 20010531 JPM 47.98 20010430 AMZN 15.78 20010430 1249,46 20010430 3.62 19.95 49.15 20010531 AMZN 16.69 20010531 1255,82 20010531 3.49 20010629 JPM 20010629 AAPL 23.25 44.6 20010629 AMZN 14.15 20010629 1224.42 20010629 20010731 3.51 1211.23 20010731 AAPL 18.79 20010731 JPM 43.3 20010731 AMZN 12.49 20010731 3.36 20010831 AAPL 18.55 20010831 JPM 39.4 20010831 AMZN 8.94 20010831 1133,58 20010831 2.64 20010928 AAPL 10 15.51 20010928 JPM 34.15 20010928 AMZN 5.97 20010928 1040.94 20010928 2.16 20011031 AAPL 20011031 JPM 20011031 AMZN 20011031 20011031 11 17.56 35.36 6.98 1059.78 1.87 20011130 AAPL 20011130 JPM 20011130 AMZN 12 21.3 37.72 11.32 20011130 1139.45 20011130 1.69 20011231 AAPL 13 21.9 20011231 JPM 36.35 20011231 AMZN 10.82 20011231 1148.08 20011231 1.65 24.72 14 20020131 AAPL 20020131 JPM 34.05 20020131 AMZN 14.19 20020131 1130.2 20020131 1.72 20020228 AMZN 15 20020228 AAPL 21.7 20020228 JPM 29.25 14.1 20020228 1106.73 20020228 1.79 20020328 AAPL 20020328 JPM 1147.39 16 23.67 35.65 20020328 AMZN 14.3 20020328 20020328 1.71 20020430 AAPL 20020430 JPM 20020430 17 24.27 35.1 20020430 AMZN 16.69 1076.92 20020430 1.73 20020531 AAPL 18 23.3 20020531 JPM 35.95 20020531 AMZN 18.23 20020531 1067.14 20020531 1.70 20020628 20020731 20020628 JPM 20020628 AMZN 19 20020628 AAPL 17.72 33.92 16.25 989.81 20020628 1.68 20020731 AAPL 24.96 20020731 JPM 20020731 AMZN 14,459 20 15.26 911.62 20020731 1.62 21 20020830 AAPL 14.75 20020830 JPM 26.4 20020830 AMZN 14.94 20020830 916.07 20020830 1.63 20020930 AAPL 20020930 22 14.5 20020930 JPM 18.99 20020930 AMZN 15.93 815.29 20020930 1.58 20021031 AAPL 20021031 JPM 20021031 23 16.07 20.75 20021031 AMZN 19.36 20021031 885.76 1.23 20021129 AAPL 25.17 936.31 24 15.5 20021129 JPM 20021129 AMZN 23.35 20021129 20021129 1.19 20021231 AAPL 20021231 JPM 20021231 25 14.33 24 20021231 AMZN 18.89 20021231 879.82 1.17 20030131 JPM 855.7 26 20030131 AAPL 14.36 23.34 20030131 AMZN 21.85 20030131 20030131 1.17 20030228 27 20030228 AAPL 15.01 20030228 JPM 20030331 JPM 22.68 20030228 AMZN 22.01 841.15 20030228 1.13 20030331 AAPL 23.71 28 14.14 20030331 AMZN 26.03 20030331 848.18 20030331 1.13 20030430 JPM 29 20030430 AAPL 14.22 29.35 20030430 AMZN 28.69 20030430 916.92 20030430 1.07 20030530 20030630 20030530 20030630 20030530 AAPL 17.95 20030530 JPM 35.89 963.59 0.92 30 32.86 20030530 AMZN 20030630 AAPL 20030731 AAPL 20030630 JPM 20030630 AMZN 31 19.06 34.18 36.32 974.5 0.90 20030731 AMZN 32 21.08 20030731 JPM 35.05 41.64 20030731 990.31 20030731 0.95 33 20030829 AAPL 22.61 20030829 JPM 34.22 20030829 AMZN 46.32 20030829 1008.01 20030829 0.94 20.72 34 20030930 AAPL 20030930 JPM 34.33 20030930 AMZN 48.43 20030930 995.97 20030930 0.92 20031031 AAPL 35 22.89 20031031 JPM 35.9 20031031 AMZN 54.43 20031031 1050.71 20031031 0.93 20031128 36 20031128 AAPL 20.91 20031128 JPM 35.4 20031128 AMZN 53.97 20031128 1058.2 0.90 20031231 AAPL 20031231 JPM 20031231 37 21.37 36.73 20031231 AMZN 52.62 1111.92 20031231 0.88 20040130 AAPL 20040130 JPM 38 22.56 38.89 20040130 AMZN 50.4 20040130 1131.13 20040130 0.93 20040227 AAPL 20040227 JPM 20040227 AMZN 39 23.92 41.02 43.01 20040227 1144.94 20040227 0.94 20040331 AAPL 20040331 AMZN 40 27.04 20040331 JPM 41.95 43.28 20040331 1126.21 20040331 0.94 20040430 AMZN 1107.3 41 20040430 AAPL 25.78 20040430 JPM 37.6 43.6 20040430 20040430 1.02 20040528 AAPL 1120.68 1140.84 1101.72 42 28.06 20040528 JPM 36.84 20040528 AMZN 48.5 20040528 20040528 1.27 20040630 JPM 20040630 AAPL 43 32.54 38.77 20040630 AMZN 54.4 20040630 20040630 1.33 20040730 AAPL 44 32.34 20040730 JPM 37.33 20040730 AMZN 38.92 20040730 20040730 1.48 45 20040831 AAPL 34.49 20040831 JPM 39.58 20040831 AMZN 38.14 20040831 1104.24 20040831 1.65 46 20040930 AAPL 38.75 20040930 JPM 39.73 20040930 AMZN 40.86 20040930 1114.58 20040930 1.76 20041029 JPM 34.13 20041029 47 20041029 AAPL 52.4 38.6 20041029 AMZN 1130.2 20041029 2.07 20041130 AAPL 48 67.05 20041130 JPM 37.65 20041130 AMZN 39.68 20041130 1173.82 20041130 2.19 20041231 JPM 39.01 49 20041231 AAPL 64.4 20041231 AMZN 44.29 20041231 1211.92 20041231 2.33