As an assistant to Rachel, you are asked to collect the monthly prices of each s
ID: 2739332 • Letter: A
Question
As an assistant to Rachel, you are asked to collect the monthly prices of each stock from Yahoo Finance from 11/23/2010 to 11/2/2015 (market booming time period), and then finish the following tasks : (WMT and CITI - both shown below) show excel formula and/or explain your answers.
1. Calculate monthly returns for each stock
2. Calculate the variance and average return for each stock
3. Construct the covariance matrix of these stocks
4. Use Excel Solver function to build the efficient frontier (with and without short sale)
5. Determine the optimal risk portfolio
6. Draw a graph showing the efficient frontier and the CAL line (one graph for the case with short sale and one graph without short sale)
Please use the following data:
Citi C
Walmart WMT
Date Open Close 11/2/2015 53.450001 53.830002 10/1/2015 49.419998 53.169998 9/1/2015 52.400002 49.610001 8/3/2015 58.619999 53.48 7/1/2015 56.009998 58.459999 6/1/2015 54.68 55.240002 5/1/2015 53.639999 54.080002 4/1/2015 51.369999 53.32 3/2/2015 52.419998 51.52 2/2/2015 47.200001 52.419998 1/2/2015 54.360001 46.950001 12/1/2014 53.799999 54.110001 11/3/2014 53.419998 53.970001 10/1/2014 51.68 53.529999 9/2/2014 51.779999 51.82 8/1/2014 48.59 51.650002 7/1/2014 47.419998 48.91 6/2/2014 47.599998 47.099998 5/1/2014 47.810001 47.57 4/1/2014 47.720001 47.91 3/3/2014 47.799999 47.599998 2/3/2014 47.84 48.630001 1/2/2014 52.029999 47.43 12/2/2013 52.959999 52.110001 11/1/2013 48.869999 52.919998 10/1/2013 48.849998 48.779999 9/3/2013 49.369999 48.509998 8/1/2013 52.84 48.330002 7/1/2013 48.5 52.139999 6/3/2013 52.060001 47.970001 5/1/2013 46.48 51.990002 4/1/2013 44.32 46.66 3/1/2013 41.560001 44.240002 2/1/2013 42.48 41.970001 1/2/2013 40.91 42.16 12/3/2012 34.959999 39.560001 11/1/2012 37.57 34.57 10/1/2012 33 37.389999 9/4/2012 29.530001 32.720001 8/1/2012 27.299999 29.709999 7/2/2012 27.469999 27.129999 6/1/2012 25.860001 27.41 5/1/2012 32.919998 26.51 4/2/2012 36.400002 33.040001 3/1/2012 33.52 36.549999 2/1/2012 31.27 33.32 1/3/2012 27.129999 30.719999 12/1/2011 27.08 26.309999 11/1/2011 28.92 27.48 10/3/2011 25.16 31.59 9/1/2011 30.93 25.620001 8/1/2011 39.34 31.049999 7/1/2011 41.529999 38.34 6/1/2011 40.990002 41.639999 5/2/2011 4.6 41.150002 4/1/2011 4.46 4.59 3/1/2011 4.7 4.42 2/1/2011 4.86 4.68 1/3/2011 4.78 4.82 12/1/2010 4.28 4.73 11/23/2010 4.15 4.2Explanation / Answer
1. Monthly returns for each stock over the mentioned period is calculated as below : Close price - Open Price = Return
2. Calculation of variance & average return for each stock.
The excel formula for calculation of the variance is VAR (Value 1, value 2,...Value n) with values being the monthly return amounts as above.
Therefore using this formula in excel variance for Citi C is 31.52 & variance for Walmart is 10.78.
Average of both the stocks for the period is sum of the return divided by number of months.
Average for Citi C = 0.76 & average for Walmart is 0.01.
Citi C Walmart Date Open Close Returns Date Open Close Returns Nov-15 53.45 53.83 0.38 Nov-15 57.29 57.61 0.32 Oct-15 49.42 53.17 3.75 Oct-15 64.76 57.24 (7.52) Sep-15 52.4 49.61 (2.79) Sep-15 63.8 64.84 1.04 Aug-15 58.62 53.48 (5.14) Aug-15 71.84 64.73 (7.11) Jul-15 56.01 58.46 2.45 Jul-15 71.6 71.98 0.38 Jun-15 54.68 55.24 0.56 Jun-15 74.69 70.93 (3.76) May-15 53.64 54.08 0.44 May-15 78.2 74.27 (3.93) Apr-15 51.37 53.32 1.95 Apr-15 82.28 78.05 (4.23) Mar-15 52.42 51.52 (0.90) Mar-15 83.93 82.25 (1.68) Feb-15 47.2 52.42 5.22 Feb-15 84.79 83.93 (0.86) Jan-15 54.36 46.95 (7.41) Jan-15 86.27 84.98 (1.29) Dec-14 53.8 54.11 0.31 Dec-14 86.72 85.88 (0.84) Nov-14 53.42 53.97 0.55 Nov-14 76.35 87.54 11.19 Oct-14 51.68 53.53 1.85 Oct-14 76.51 76.27 (0.24) Sep-14 51.78 51.82 0.04 Sep-14 75.42 76.47 1.05 Aug-14 48.59 51.65 3.06 Aug-14 73.32 75.5 2.18 Jul-14 47.42 48.91 1.49 Jul-14 75.27 73.58 (1.69) Jun-14 47.6 47.1 (0.50) Jun-14 76.58 75.07 (1.51) May-14 47.81 47.57 (0.24) May-14 79.73 76.77 (2.96) Apr-14 47.72 47.91 0.19 Apr-14 76.76 79.71 2.95 Mar-14 47.8 47.6 (0.20) Mar-14 74.49 76.43 1.94 Feb-14 47.84 48.63 0.79 Feb-14 74.19 74.7 0.51 Jan-14 52.03 47.43 (4.60) Jan-14 78.72 74.68 (4.04) Dec-13 52.96 52.11 (0.85) Dec-13 80.89 78.69 (2.20) Nov-13 48.87 52.92 4.05 Nov-13 76.97 81.01 4.04 Oct-13 48.85 48.78 (0.07) Oct-13 73.87 76.75 2.88 Sep-13 49.37 48.51 (0.86) Sep-13 73.48 73.96 0.48 Aug-13 52.84 48.33 (4.51) Aug-13 78.42 72.98 (5.44) Jul-13 48.5 52.14 3.64 Jul-13 74.99 77.94 2.95 Jun-13 52.06 47.97 (4.09) Jun-13 75.06 74.49 (0.57) May-13 46.48 51.99 5.51 May-13 77.85 74.84 (3.01) Apr-13 44.32 46.66 2.34 Apr-13 75 77.72 2.72 Mar-13 41.56 44.24 2.68 Mar-13 70.78 74.83 4.05 Feb-13 42.48 41.97 (0.51) Feb-13 70.11 70.78 0.67 Jan-13 40.91 42.16 1.25 Jan-13 68.93 69.95 1.02 Dec-12 34.96 39.56 4.60 Dec-12 72.11 68.23 (3.88) Nov-12 37.57 34.57 (3.00) Nov-12 75.13 72.02 (3.11) Oct-12 33 37.39 4.39 Oct-12 73.8 75.02 1.22 Sep-12 29.53 32.72 3.19 Sep-12 72.77 73.8 1.03 Aug-12 27.3 29.71 2.41 Aug-12 74.7 72.6 (2.10) Jul-12 27.47 27.13 (0.34) Jul-12 69.6 74.43 4.83 Jun-12 25.86 27.41 1.55 Jun-12 65.43 69.72 4.29 May-12 32.92 26.51 (6.41) May-12 58.95 65.82 6.87 Apr-12 36.4 33.04 (3.36) Apr-12 61.08 58.91 (2.17) Mar-12 33.52 36.55 3.03 Mar-12 59.36 61.2 1.84 Feb-12 31.27 33.32 2.05 Feb-12 61.79 59.08 (2.71) Jan-12 27.13 30.72 3.59 Jan-12 59.97 61.36 1.39 Dec-11 27.08 26.31 (0.77) Dec-11 58.64 59.76 1.12 Nov-11 28.92 27.48 (1.44) Nov-11 55.82 58.9 3.08 Oct-11 25.16 31.59 6.43 Oct-11 51.88 56.72 4.84 Sep-11 30.93 25.62 (5.31) Sep-11 53.2 51.9 (1.30) Aug-11 39.34 31.05 (8.29) Aug-11 52.79 53.19 0.40 Jul-11 41.53 38.34 (3.19) Jul-11 53.19 52.71 (0.48) Jun-11 40.99 41.64 0.65 Jun-11 54.98 53.14 (1.84) May-11 4.6 41.15 36.55 May-11 55.08 55.22 0.14 Apr-11 4.46 4.59 0.13 Apr-11 52.25 54.98 2.73 Mar-11 4.7 4.42 (0.28) Mar-11 52.16 52.05 (0.11) Feb-11 4.86 4.68 (0.18) Feb-11 56.37 51.98 (4.39) Jan-11 4.78 4.82 0.04 Jan-11 54.23 56.07 1.84 Dec-10 4.28 4.73 0.45 Dec-10 54.42 53.93 (0.49) Nov-10 4.15 4.2 0.05 Nov-10 54.2 54.09 (0.11)Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.