Calculate the monthly return of the stocks of QUALCOMM and INTEL, each, from March 1, 2007, to March 1, 2017. (Yahoo Finance provides monthly returns! Go to Historical Data provided in the middle bar menu of Yahoo Finance page for each company and use the prices given under "adjusted close", which include dividends!)
1. What is the monthly expected return and the standard deviation of a portfolio that is composed of
a) 10% QUALCOMM, 90% INTEL?
b) 20% QUALCOMM, 80% INTEL?
c) 30% QUALCOMM, 70% INTEL?
d) 40% QUALCOMM, 60% INTEL?
e) 50% QUALCOMM, 50% INTEL?
f) 60% QUALCOMM, 40% INTEL?
g) 70% QUALCOMM, 30% INTEL?
h) 80% QUALCOMM, 20% INTEL?
i) 90% QUALCOMM, 10% INTEL?
2. What is the composition of a portfolio that is comprised of QUALCOMM and INTEL stocks and that has the lowest standard deviation?
3. You have $10K. What are the expected value and the standard deviation of a portfolio that is formed by investing $SK at the risk-free rate and $5K on the portfolio you found in question 2 (assume that the monthly risk-free rate is 0.0025)
Solution:
Since the date is very big, it is not possible to show calculations for each line of monthly return. The formula to calculate monthly return is given below:
Monthly Return = (Current Month Adj. Closing Price /Previous Month Adj. Closing Price) - I
Standard Deviation of Stock using Excel: =57DEV(First cell with monthly return: Last cell with monthly return)
Stance /Deviation for Qualcomm:= Z 8395
Return of Qualcomm:
Date | Open | High | Low | Close | Adj Close | Monthly Return |
01/03/2007 | 39.560001 | 44.119999 | 39.040001 | 42.66 | 33.413612 | |
01/04/2007 | 42.68 | 46.330002 | 41.599998 | 43.799999 | 34.306526 | 2.67% |
01/05/2007 | 44.009998 | 47.720001 | 42.139999 | 42.950001 | 33.640751 | -1.94% |
01/06/2007 | 43.529999 | 43.990002 | 40.98 | 43.389999 | 34.093182 | 1.34% |
01/07/2007 | 43.599998 | 45.580002 | 41.27 | 41.650002 | 32.72599 | -4.01% |
01/08/2007 | 41.52 | 42.349998 | 35.23 | 39.889999 | 31.3431 | -4.23% |
01/09/2007 | 39.900002 | 43.060001 | 36.68 | 42.259998 | 33.328506 | 6.33% |
01/10/2007 | 42.049999 | 43.400002 | 40.049999 | 42.73 | 33.699169 | 1.11% |
01/11/2007 | 42.259998 | 42.700001 | 36.599998 | 40.779999 | 32.161301 | -4.56% |
01/12/2007 | 40.619999 | 41.57 | 37.75 | 39.349998 | 31.033529 | -3.51% |
01/01/2008 | 38.23 | 42.66 | 35.169998 | 42.419998 | 33.574108 | 8.19% |
01/02/2008 | 42.27 | 44.849998 | 38.950001 | 42.389999 | 33.550369 | -0.07% |
01/03/2008 | 42.009998 | 42.119999 | 36.91 | 41 | 32.554665 | -2.97% |
01/04/2008 | 41.25 | 44.009998 | 40.650002 | 43.189999 | 34.293556 | 5.34% |
01/05/2008 | 43.200001 | 49.5 | 42.830002 | 48.540001 | 38.541538 | 12.39% |
01/06/2008 | 48.610001 | 50.82 | 44.34 | 44.369999 | 35.346264 | -8.29% |
01/07/2008 | 44.150002 | 56 | 42.689999 | 55.34 | 44.085243 | 24.72% |
01/08/2008 | 55.490002 | 56.880001 | 52.220001 | 52.650002 | 41.942326 | -4.86% |
01/09/2008 | 53.310001 | 53.540001 | 37.82 | 42.970001 | 34.333668 | -18.14% |
01/10/2008 | 42.790001 | 43.240002 | 32.209999 | 38.259998 | 30.570316 | -10.96% |
01/11/2008 | 38.209999 | 38.880001 | 28.16 | 33.57 | 26.822933 | -12.26% |
01/12/2008 | 32.669998 | 36.380001 | 29.34 | 35.830002 | 28.6287 | 6.73% |
01/01/2009 | 35.650002 | 37.709999 | 33.290001 | 34.549999 | 27.738321 | -3.11% |
01/02/2009 | 34.16 | 36.900002 | 32.669998 | 33.43 | 26.839144 | -3.24% |
01/03/2009 | 32.93 | 39.700001 | 32.639999 | 38.91 | 31.384335 | 16.93% |
01/04/2009 | 38.27 | 44.59 | 38.060001 | 42.32 | 34.134789 | 8.76% |
01/05/2009 | 42.310001 | 44.209999 | 39.849998 | 43.59 | 35.159164 | 3.00% |
01/06/2009 | 44.099998 | 46.73 | 42.849998 | 45.200001 | 36.601501 | 4.10% |
01/07/2009 | 45.369999 | 48.720001 | 42.669998 | 46.209999 | 37.419365 | 2.23% |
01/08/2009 | 46.669998 | 48.200001 | 44.639999 | 46.419998 | 37.589405 | 0.45% |
01/09/2009 | 46.040001 | 46.75 | 44 | 44.98 | 36.555603 | -2.75% |
01/10/2009 | 44.610001 | 44.639999 | 40.150002 | 41.330002 | 33.589222 | -8.11% |
01/11/2009 | 41.349998 | 46.25 | 41.080002 | 45 | 36.57185 | 8.88% |
01/12/2009 | 45 | 46.990002 | 44.049999 | 46.259998 | 37.738113 | 3.19% |
01/01/2010 | 46.790001 | 49.799999 | 38.490002 | 39.189999 | 31.97053 | -15.28% |
01/02/2010 | 39.25 | 39.970001 | 36.470001 | 36.68 | 29.922916 | -6.40% |
01/03/2010 | 36.82 | 43.84 | 35.459999 | 41.959999 | 34.381916 | 14.90% |
01/04/2010 | 42.169998 | 43.389999 | 37.779999 | 38.68 | 31.694284 | -7.82% |
01/05/2010 | 38.91 | 39.049999 | 34.52 | 35.560001 | 29.137774 | -8.07% |
01/06/2010 | 35.450001 | 36.810001 | 32.73 | 32.84 | 27.053347 | -7.15% |
01/07/2010 | 32.790001 | 39.66 | 31.629999 | 38.080002 | 31.370024 | 15.96% |
01/08/2010 | 38.330002 | 40 | 37.450001 | 38.299999 | 31.55126 | 0.58% |
01/09/2010 | 38.959999 | 45.75 | 38.919998 | 45.130001 | 37.364391 | 18.42% |
01/10/2010 | 45.509998 | 45.630001 | 42.450001 | 45.16 | 37.389233 | 0.07% |
01/11/2010 | 45.52 | 49.450001 | 44.619999 | 46.82 | 38.76358 | 3.68% |
01/12/2010 | 47.32 | 50.310001 | 47.299999 | 49.490002 | 41.137127 | 6.12% |
01/01/2011 | 49.889999 | 55 | 49.59 | 54.130001 | 44.993992 | 9.38% |
01/02/2011 | 54.299999 | 59.799999 | 53.810001 | 59.580002 | 49.524151 | 10.07% |
01/03/2011 | 59.84 | 59.84 | 50.099998 | 54.830002 | 45.725006 | -7.67% |
01/04/2011 | 55.07 | 58.459999 | 51.450001 | 57.09 | 47.609718 | 4.12% |
01/05/2011 | 57.439999 | 58.59 | 55.419998 | 58.59 | 48.860634 | 2.63% |
01/06/2011 | 58.43 | 58.950001 | 52.130001 | 56.790001 | 47.541054 | -2.70% |
01/07/2011 | 56.790001 | 59.48 | 54.060001 | 54.779999 | 45.858402 | -3.54% |
01/08/2011 | 55.34 | 55.439999 | 45.98 | 51.459999 | 43.079117 | -6.06% |
01/09/2011 | 51.919998 | 54.299999 | 47.599998 | 48.630001 | 40.892952 | -5.07% |
01/10/2011 | 48.209999 | 55.029999 | 46.400002 | 51.599998 | 43.390427 | 6.11% |
01/11/2011 | 49.919998 | 57.970001 | 49.779999 | 54.799999 | 46.081295 | 6.20% |
01/12/2011 | 54.700001 | 55.73 | 51.759998 | 54.700001 | 46.175545 | 0.20% |
01/01/2012 | 55.880001 | 59.5 | 54.560001 | 58.82 | 49.653484 | 7.53% |
01/02/2012 | 59.48 | 63.810001 | 58.790001 | 62.18 | 52.489853 | 5.71% |
01/03/2012 | 62.209999 | 68.870003 | 60.93 | 68.059998 | 57.651264 | 9.83% |
01/04/2012 | 68.25 | 68.830002 | 61.259998 | 63.830002 | 54.06818 | -6.22% |
01/05/2012 | 63.82 | 64.720001 | 55.720001 | 57.310001 | 48.545315 | -10.21% |
01/06/2012 | 55.970001 | 59.689999 | 53.41 | 55.68 | 47.367416 | -2.43% |
01/07/2012 | 55.650002 | 60.189999 | 53.09 | 59.68 | 50.770241 | 7.18% |
01/08/2012 | 60.09 | 63.34 | 58.16 | 61.459999 | 52.284504 | 2.98% |
01/09/2012 | 61.200001 | 65.449997 | 60.279999 | 62.470001 | 53.143719 | 1.64% |
01/10/2012 | 62.73 | 63.75 | 57.290001 | 58.610001 | 50.065807 | -5.79% |
01/11/2012 | 58.709999 | 63.810001 | 58.110001 | 63.619999 | 54.345448 | 8.55% |
01/12/2012 | 63.91 | 64.720001 | 59.439999 | 61.860001 | 52.842026 | -2.77% |
01/01/2013 | 63.580002 | 67.440002 | 62.330002 | 66.019997 | 56.618595 | 7.15% |
01/02/2013 | 66.400002 | 67.449997 | 64.32 | 65.639999 | 56.292702 | -0.58% |
01/03/2013 | 65.339996 | 68.5 | 63.400002 | 66.940002 | 57.407578 | 1.98% |
01/04/2013 | 66.699997 | 67.650002 | 61.25 | 61.599998 | 53.023029 | -7.64% |
01/05/2013 | 61.43 | 66.669998 | 61.43 | 63.48 | 54.64127 | 3.05% |
01/06/2013 | 63.369999 | 64.040001 | 59.459999 | 61.09 | 52.584045 | -3.76% |
01/07/2013 | 61.490002 | 65.190002 | 59.02 | 64.559998 | 55.878975 | 6.27% |
01/08/2013 | 64.779999 | 67.449997 | 64.309998 | 66.279999 | 57.367718 | 2.66% |
01/09/2013 | 66.919998 | 70.370003 | 66.519997 | 67.32 | 58.575191 | 2.10% |
01/10/2013 | 67.400002 | 69.980003 | 65.470001 | 69.489998 | 60.463303 | 3.22% |
01/11/2013 | 69.529999 | 73.889999 | 66.25 | 73.580002 | 64.022026 | 5.89% |
01/12/2013 | 73.529999 | 74.32 | 71.68 | 74.25 | 64.913452 | 1.39% |
01/01/2014 | 73.610001 | 75.900002 | 70.980003 | 74.220001 | 64.887238 | -0.04% |
01/02/2014 | 73.669998 | 76.75 | 72.029999 | 75.290001 | 65.822693 | 1.44% |
01/03/2014 | 74.620003 | 79.720001 | 73.040001 | 78.860001 | 68.943779 | 4.74% |
01/04/2014 | 79.419998 | 81.660004 | 76.769997 | 78.709999 | 69.134033 | 0.28% |
01/05/2014 | 78.529999 | 80.989998 | 78.43 | 80.449997 | 70.662331 | 2.21% |
01/06/2014 | 80.660004 | 80.790001 | 77.910004 | 79.199997 | 69.5644 | -1.55% |
01/07/2014 | 79.349998 | 81.970001 | 73.650002 | 73.720001 | 65.090935 | -6.43% |
01/08/2014 | 73.669998 | 77.309998 | 71.82 | 76.099998 | 67.192345 | 3.23% |
01/09/2014 | 75.919998 | 77 | 74.110001 | 74.769997 | 66.383247 | -1.20% |
01/10/2014 | 74.639999 | 78.529999 | 69.360001 | 78.510002 | 69.703758 | 5.00% |
01/11/2014 | 78.309998 | 78.529999 | 67.669998 | 72.900002 | 64.722992 | -7.15% |
01/12/2014 | 72.459999 | 75.720001 | 69.589996 | 74.330002 | 66.378365 | 2.56% |
01/01/2015 | 74.510002 | 75.300003 | 62.389999 | 62.459999 | 55.778179 | -15.97% |
01/02/2015 | 62.490002 | 72.57 | 62.259998 | 72.510002 | 64.753067 | 16.09% |
01/03/2015 | 71.800003 | 74.089996 | 65.68 | 69.339996 | 61.92218 | -4.37% |
01/04/2015 | 68.470001 | 71.900002 | 66.18 | 68 | 61.079323 | -1.36% |
01/05/2015 | 68.309998 | 71.32 | 67.5 | 69.68 | 62.588341 | 2.47% |
01/06/2015 | 69.510002 | 70.050003 | 62.450001 | 62.630001 | 56.255856 | -10.12% |
01/07/2015 | 63.099998 | 66.050003 | 60.830002 | 64.389999 | 58.2379 | 3.52% |
01/08/2015 | 64.349998 | 64.82 | 52.59 | 56.580002 | 51.174107 | -12.13% |
01/09/2015 | 55.25 | 56.759998 | 52.169998 | 53.73 | 49.005856 | -4.24% |
01/10/2015 | 53.599998 | 61.189999 | 53 | 59.419998 | 54.19556 | 10.59% |
01/11/2015 | 59.700001 | 61.18 | 47.52 | 48.790001 | 44.500198 | -17.89% |
01/12/2015 | 48.75 | 53.439999 | 45.93 | 49.990002 | 46.04483 | 3.47% |
01/01/2016 | 49.43 | 50.599998 | 43.470001 | 45.34 | 41.761795 | -9.30% |
01/02/2016 | 44.709999 | 52 | 42.240002 | 50.790001 | 46.781693 | 12.02% |
01/03/2016 | 51.080002 | 53.52 | 49.669998 | 51.139999 | 47.546188 | 1.63% |
01/04/2016 | 51.080002 | 53.279999 | 49.73 | 50.52 | 46.969753 | -1.21% |
01/05/2016 | 50.689999 | 56.27 | 50.130001 | 54.919998 | 51.060547 | 8.71% |
01/06/2016 | 54.5 | 55.57 | 50.84 | 53.57 | 50.283539 | -1.52% |
01/07/2016 | 52.990002 | 62.82 | 51.16 | 62.580002 | 58.740788 | 16.82% |
01/08/2016 | 62.369999 | 63.689999 | 60.02 | 63.07 | 59.200726 | 0.78% |
01/09/2016 | 62.869999 | 70.400002 | 59.93 | 68.5 | 64.843369 | 9.53% |
01/10/2016 | 68.080002 | 71.620003 | 64.300003 | 68.720001 | 65.051628 | 0.32% |
01/11/2016 | 69 | 69.349998 | 64.32 | 68.129997 | 64.493118 | -0.86% |
01/12/2016 | 67.720001 | 70.239998 | 63.779999 | 65.199997 | 62.202274 | -3.55% |
01/01/2017 | 65.860001 | 67.080002 | 52.830002 | 53.43 | 50.973427 | -18.05% |
01/02/2017 | 53.639999 | 57.310001 | 52.369999 | 56.48 | 53.883198 | 5.71% |
