Question 1 (65 points)
The tab Data_Q1 contains monthly close prices for Barrick Gold Corporation (GOLD) and Manulife Financial Corp (MFC) for the period November 2000 through October 2020. Assume the risk-free rate stays constant during the period of analysis and it is 1.2%.
a). Calculate monthly returns for both series of prices. Estimate the expected return, variance of GOLD and MFC and the covariance between the two return series.
b). Create a table that calculates expected returns and standard deviations for portfolios formed by combining the 2 stocks while changing weights in 10 percent increments (i.e. start with 100% in MFC and 0% in GOLD, then next line is 90% in MFC and 10% in GOLD, and so on, until 100% is invested in MFC and 0% in GOLD). Plot the opportunity set of risky portfolio formed by the two stocks, in excel (return, st.dev. graph), by inserting a graph in excel and selecting the right variables. Copy and paste the generated graph in the word document, as well as the table used to generate the graph.
c). What are the expected return and standard deviation of the optimal risky portfolio?
Assume you have only the 2 risky stocks to invest in and the given risk free rate. What is the Sharpe ratio? Enter the right formulas in excel and create a table with the output and copy and paste it in your word assignment document.
d). Assume an investors utility function is U = E(r) ()A2 , where A = 2
Plot utility scores while changing the weight on the optimal risky portfolio in 10 percent increment (i.e. 100% in risk-free asset, 0% in risky portfolio, 90% in risk-free and 10% in risky portfolio, and so on). Insert the excel table in your assignment.
e). Find the optimal complete portfolio for the investor with A = 2. Create a pie chart of the weights of the three assets (risk-free asset, GOLD and MFC), as resulting from your calculation, in investors optimal complete portfolio. What is the weight in the risky portfolio and what are the expected return and standard deviation of the optimal complete portfolio?
Data is (zoom in) (but can be found online):
Note: For each part of the questions in the assignment, please write in your handout the exact formulas you used, even if the calculations were done in excel (meaning, the embedded formulas you used to arrive at your table entries). Clear, correct formulas carry points as well.
Date GOLD MFC 31 12.4 11.08 15.895 16.15 17.684999 17.905001 18.575001 18.385 19.41 20.25 20.01 12.38 12.85 13.59 14.43 14.82 14.72 1/1/2000 12/1/2000 | 1/1/2001 2/1/2001 3/1/2001 4/1/2001 5/1/2001 6/1/2001 7/1/2001 8/1/2001 9/1/2001 10/1/2001 11/1/2001 12/1/2001 11/2002 2/1/2002 14.78 15 16.379999 15.47 16.200001 14.29 16.40001 16.5 15.15 14.8 16.02 17.35 15.59 15.13 15.95 17.1 18.049999 18.559999 20.07 21.799999 18.9 41.759998 40.5 34.529999 35.009998 31.92 30.24 29.4 19.709999 21.120001 15.74 16.969999 19.15 18.620001 19.389999 16.49 12.9375 15.6875 13.85 13.715 13.2 12.585 13.485 13.955 15.09 14.675 13.105 12.34 13.8 13.03 12.735 12.97 13.725 14.47 14.885 14.3 12.07 11.555 10.505 10.775 12.59 10.855 11.575 12.275 11.965 13.32 13.715 14.105 14.67 15.12 14.45 15.165 22.34 22.709999 19.700001 20.450001 23.780001 19.23 20.67 19.75 19.120001 20 21.040001 22.51 24.59 24.219999 21.860001 24.9 23.959999 22.32 22.99 25.030001 24.5 26.219999 29.049999 25.25 11/1/2003 12/1/2003 1/1/2004 21/2004 3/1/2004 4/1/2004 5/1/2004 6/1/2004 7/1/2004 8/1/2004 91/2004 10/1/2004 11/1/2004 12/1/2004 1/1/2005 21/2005 3/1/2005 4/1/2005 5/1/2005 6/1/2005 7/1/2005 8/1/2005 9/1/2005 10/1/2005 11/1/2005 12/1/2005 1/1/2006 21/2006 3/1/2006 4/1/2006 5/1/2006 6/1/2006 7/1/2006 8/1/2006 1/2006 10/1/2006 1/1/2006 12/1/2006 11/2007 21/2007 3/1/2007 4/1/2007 5/1/2007 6/1/2007 7/1/2007 8/1/2007 9/1/2007 10/1/2007 1/1/2007 12/1/2007 11/2008 21/2008 3/1/2008 4/1/2008 5/1/2008 6/1/2008 7/1/2008 8/1/2008 9/1/2008 10/1/2008 11/1/2008 12/1/2008 11/2009 21/2009 3/1/2009 4/1/2009 5/1/2009 6/1/2009 7/1/2009 8/1/2009 9/1/2009 31.440001 30.700001 29.620001 29.870001 28.549999 28.110001 29.129999 29.07 32.900002 32.52 40.279999 4.130001 40.509998 42.049999 51.439999 51.950001 43.450001 38.619999 40.290001 45.5 42.349998 34.73 36.740002 22.74 29.459999 36.77 37.490002 30.200001 32.419998 29.1 38.080002 33.549999 34.900002 34.700001 37.900002 32.43 33.119999 33.790001 33.709999 33.57 34.450001 36.110001 37.360001 37.32 36.540001 38.849998 41.259998 46.389999 41.779999 40.75 37.6 38.709999 37.98 39.16 38.669998 34.709999 36.830002 35.790001 36.689999 20.09 17.18 17.030001 16.549999 10.15 11.2 17.1 21.360001 17.35 24.309999 20.459999 20.940001 20.715 21.895 23.285 22.465 23.1 21.924999 23.200001 23.959999 22.924999 22.975 23.905001 25.15 25.469999 26.66 26. 105 28.764999 29.4 30.475 31.969999 31.385 32.645 33.049999 31.77 31.68 32.650002 32.259998 10/1/2009 11/1/2009 12/1/2009 1/1/2010 21/2010 3/1/2010 4/1/2010 5/1/2010 6/1/2010 7/1/2010 8/1/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/1/2011 2/1/2011 3/1/2011 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/1/2011 11/1/2011 12/1/2011 1/1/2012 2/1/2012 3/1/2012 4/1/2012 5/1/2012 6/1/2012 7/1/2012 8/1/2012 35.93 42.689999 39.380001 34.82 37.66 38.34 43.549999 42.080002 45.41 41.099998 46.759998 46.290001 48.09 51.650002 53.18 47.509998 52.82 51.91 51.009998 47.759998 45.290001 47.57 50.75 46.650002 49.5 52.880001 45.25 49.259998 47.73 43.48 40.43 39.060001 37.57 32.880001 38.52 18.59 17.459999 18.34 18.34 18.299999 19.690001 17.99 16.690001 14.58 15.91 11.14 12.63 12.7 13.91 17.18 17.40001 18.889999 17.690001 17.969999 17.85 17.66 15.89 13.71 11.33 13.21 10.77 10.62 11.72 12.51 13.55 9/1/2012 10/1/2012 11/1/2012 12/1/2012 1/1/2013 21/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 10/1/2013 11/1/2013 12/1/2013 1/1/2014 21/2014 3/1/2014 4/1/2014 5/1/2014 6/1/2014 7/1/2014 8/1/2014 9/1/2014 10/1/2014 11/1/2014 12/1/2014 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 17.629999 19.280001 20.379999 17.83 3/1/2002 4/1/2002 5/1/2002 6/1/2002 7/1/2002 8/1/2002 9/1/2002 10/1/2002 11/1/2002 | 12/1/2002 | 1/1/2003 21/2003 3/1/2003 4/1/2003 5/1/2003 6/1/2003 7/1/2003 8/1/2003 9/1/2003 10/1/2003 9/1/2015 10/1/2015 11/1/2015 12/1/2015 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 121/2016 11/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/2017 8/1/2017 9/1/2017 10/1/2017 11/1/2017 121/2017 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 6.36 7.69 7.34 7.38 9.91 13.8 13.58 19.370001 16.77 21.35 21.860001 17.01 17.719999 17.59 15.02 15.98 18.440001 18.58 18.99 16.719999 16.540001 15.91 16.91 17.99 16.09 14.45 13.78 14.47 14.38 11.52 12.45 13.47 13.19 13.13 11.19 10.42 9/1/2018 10/1/2018 11/1/2018 121/2018 11/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 9/1/2019 10/1/2019 11/1/2019 12/1/2019 11/2020 2/1/2020 3/1/2020 4/1/2020 5/1/2020 6/1/2020 7/1/2020 8/1/2020 9/1/2020 10/1/2020 10/2/2020 15.8 16.59 16.469999 14.98 13.86 13.366 14.13 14.73 14.87 13.67 13.63 13.65 14.11 14.48 17.440001 17.82 19.16 17.92 17.74 17.549999 17.23 18.76 20.620001 19.639999 20.290001 20.1 21.040001 20.860001 21.23 19.040001 18.58 18.870001 18.83 12.55 12.75 13.54 13.39 12.4 13.71 12.72 12.42 15.77 16.26 19.379999 17.33 17.360001 16.799999 18.59 18.52 19.040001 18.32 25.719999 24 26.940001 28.91 29.65 28.110001 27.889999 27.889999 15.8 16.02 17.610001 16.43 16.559999 17.700001 19.33 19.73 18.43 18.98 19.309999 18.75 18.309999 19.870001 20.41 20.18 19.25 18.969999 19.9 19.09 16.08 17.41 17.01 18.209999 18.35 18.59 17.73 16.23 17.879999 15.72 16.51 14.19 16.08 16.879999 16.91 18.41 16.790001 18.18 18.08 16.610001 18.360001 18.620001 19.68 20.290001 19.469999 16.84 12.54 12.6 12.41 13.62 13.42 14.75 13.91 14.19 14.19 15.32 16.07 17.469999 16.110001 18.299999 18.08 18.389999 14.66 15.55 15.07 14.67 11.87 15.41 16.450001 16.120001 15.56 11.89 10.75 12.78 14.95 26.610001 27.870001 31.459999 27.370001 27.24 30.48 30.610001 29.6 30.799999 33.48 30.719999 13.02 13.67 10.96 10.72 17.48 17.9 17.120001 20.209999 18.83 13.02 11.86 10.89 10.72 11.21 10.66 7.06 6.95 17.969999 18.559999 18.299999 19.469999 Date GOLD MFC 31 12.4 11.08 15.895 16.15 17.684999 17.905001 18.575001 18.385 19.41 20.25 20.01 12.38 12.85 13.59 14.43 14.82 14.72 1/1/2000 12/1/2000 | 1/1/2001 2/1/2001 3/1/2001 4/1/2001 5/1/2001 6/1/2001 7/1/2001 8/1/2001 9/1/2001 10/1/2001 11/1/2001 12/1/2001 11/2002 2/1/2002 14.78 15 16.379999 15.47 16.200001 14.29 16.40001 16.5 15.15 14.8 16.02 17.35 15.59 15.13 15.95 17.1 18.049999 18.559999 20.07 21.799999 18.9 41.759998 40.5 34.529999 35.009998 31.92 30.24 29.4 19.709999 21.120001 15.74 16.969999 19.15 18.620001 19.389999 16.49 12.9375 15.6875 13.85 13.715 13.2 12.585 13.485 13.955 15.09 14.675 13.105 12.34 13.8 13.03 12.735 12.97 13.725 14.47 14.885 14.3 12.07 11.555 10.505 10.775 12.59 10.855 11.575 12.275 11.965 13.32 13.715 14.105 14.67 15.12 14.45 15.165 22.34 22.709999 19.700001 20.450001 23.780001 19.23 20.67 19.75 19.120001 20 21.040001 22.51 24.59 24.219999 21.860001 24.9 23.959999 22.32 22.99 25.030001 24.5 26.219999 29.049999 25.25 11/1/2003 12/1/2003 1/1/2004 21/2004 3/1/2004 4/1/2004 5/1/2004 6/1/2004 7/1/2004 8/1/2004 91/2004 10/1/2004 11/1/2004 12/1/2004 1/1/2005 21/2005 3/1/2005 4/1/2005 5/1/2005 6/1/2005 7/1/2005 8/1/2005 9/1/2005 10/1/2005 11/1/2005 12/1/2005 1/1/2006 21/2006 3/1/2006 4/1/2006 5/1/2006 6/1/2006 7/1/2006 8/1/2006 1/2006 10/1/2006 1/1/2006 12/1/2006 11/2007 21/2007 3/1/2007 4/1/2007 5/1/2007 6/1/2007 7/1/2007 8/1/2007 9/1/2007 10/1/2007 1/1/2007 12/1/2007 11/2008 21/2008 3/1/2008 4/1/2008 5/1/2008 6/1/2008 7/1/2008 8/1/2008 9/1/2008 10/1/2008 11/1/2008 12/1/2008 11/2009 21/2009 3/1/2009 4/1/2009 5/1/2009 6/1/2009 7/1/2009 8/1/2009 9/1/2009 31.440001 30.700001 29.620001 29.870001 28.549999 28.110001 29.129999 29.07 32.900002 32.52 40.279999 4.130001 40.509998 42.049999 51.439999 51.950001 43.450001 38.619999 40.290001 45.5 42.349998 34.73 36.740002 22.74 29.459999 36.77 37.490002 30.200001 32.419998 29.1 38.080002 33.549999 34.900002 34.700001 37.900002 32.43 33.119999 33.790001 33.709999 33.57 34.450001 36.110001 37.360001 37.32 36.540001 38.849998 41.259998 46.389999 41.779999 40.75 37.6 38.709999 37.98 39.16 38.669998 34.709999 36.830002 35.790001 36.689999 20.09 17.18 17.030001 16.549999 10.15 11.2 17.1 21.360001 17.35 24.309999 20.459999 20.940001 20.715 21.895 23.285 22.465 23.1 21.924999 23.200001 23.959999 22.924999 22.975 23.905001 25.15 25.469999 26.66 26. 105 28.764999 29.4 30.475 31.969999 31.385 32.645 33.049999 31.77 31.68 32.650002 32.259998 10/1/2009 11/1/2009 12/1/2009 1/1/2010 21/2010 3/1/2010 4/1/2010 5/1/2010 6/1/2010 7/1/2010 8/1/2010 9/1/2010 10/1/2010 11/1/2010 12/1/2010 1/1/2011 2/1/2011 3/1/2011 4/1/2011 5/1/2011 6/1/2011 7/1/2011 8/1/2011 9/1/2011 10/1/2011 11/1/2011 12/1/2011 1/1/2012 2/1/2012 3/1/2012 4/1/2012 5/1/2012 6/1/2012 7/1/2012 8/1/2012 35.93 42.689999 39.380001 34.82 37.66 38.34 43.549999 42.080002 45.41 41.099998 46.759998 46.290001 48.09 51.650002 53.18 47.509998 52.82 51.91 51.009998 47.759998 45.290001 47.57 50.75 46.650002 49.5 52.880001 45.25 49.259998 47.73 43.48 40.43 39.060001 37.57 32.880001 38.52 18.59 17.459999 18.34 18.34 18.299999 19.690001 17.99 16.690001 14.58 15.91 11.14 12.63 12.7 13.91 17.18 17.40001 18.889999 17.690001 17.969999 17.85 17.66 15.89 13.71 11.33 13.21 10.77 10.62 11.72 12.51 13.55 9/1/2012 10/1/2012 11/1/2012 12/1/2012 1/1/2013 21/2013 3/1/2013 4/1/2013 5/1/2013 6/1/2013 7/1/2013 8/1/2013 9/1/2013 10/1/2013 11/1/2013 12/1/2013 1/1/2014 21/2014 3/1/2014 4/1/2014 5/1/2014 6/1/2014 7/1/2014 8/1/2014 9/1/2014 10/1/2014 11/1/2014 12/1/2014 1/1/2015 2/1/2015 3/1/2015 4/1/2015 5/1/2015 6/1/2015 7/1/2015 8/1/2015 17.629999 19.280001 20.379999 17.83 3/1/2002 4/1/2002 5/1/2002 6/1/2002 7/1/2002 8/1/2002 9/1/2002 10/1/2002 11/1/2002 | 12/1/2002 | 1/1/2003 21/2003 3/1/2003 4/1/2003 5/1/2003 6/1/2003 7/1/2003 8/1/2003 9/1/2003 10/1/2003 9/1/2015 10/1/2015 11/1/2015 12/1/2015 1/1/2016 2/1/2016 3/1/2016 4/1/2016 5/1/2016 6/1/2016 7/1/2016 8/1/2016 9/1/2016 10/1/2016 11/1/2016 121/2016 11/2017 2/1/2017 3/1/2017 4/1/2017 5/1/2017 6/1/2017 7/2017 8/1/2017 9/1/2017 10/1/2017 11/1/2017 121/2017 1/1/2018 2/1/2018 3/1/2018 4/1/2018 5/1/2018 6/1/2018 7/1/2018 8/1/2018 6.36 7.69 7.34 7.38 9.91 13.8 13.58 19.370001 16.77 21.35 21.860001 17.01 17.719999 17.59 15.02 15.98 18.440001 18.58 18.99 16.719999 16.540001 15.91 16.91 17.99 16.09 14.45 13.78 14.47 14.38 11.52 12.45 13.47 13.19 13.13 11.19 10.42 9/1/2018 10/1/2018 11/1/2018 121/2018 11/2019 2/1/2019 3/1/2019 4/1/2019 5/1/2019 6/1/2019 7/1/2019 8/1/2019 9/1/2019 10/1/2019 11/1/2019 12/1/2019 11/2020 2/1/2020 3/1/2020 4/1/2020 5/1/2020 6/1/2020 7/1/2020 8/1/2020 9/1/2020 10/1/2020 10/2/2020 15.8 16.59 16.469999 14.98 13.86 13.366 14.13 14.73 14.87 13.67 13.63 13.65 14.11 14.48 17.440001 17.82 19.16 17.92 17.74 17.549999 17.23 18.76 20.620001 19.639999 20.290001 20.1 21.040001 20.860001 21.23 19.040001 18.58 18.870001 18.83 12.55 12.75 13.54 13.39 12.4 13.71 12.72 12.42 15.77 16.26 19.379999 17.33 17.360001 16.799999 18.59 18.52 19.040001 18.32 25.719999 24 26.940001 28.91 29.65 28.110001 27.889999 27.889999 15.8 16.02 17.610001 16.43 16.559999 17.700001 19.33 19.73 18.43 18.98 19.309999 18.75 18.309999 19.870001 20.41 20.18 19.25 18.969999 19.9 19.09 16.08 17.41 17.01 18.209999 18.35 18.59 17.73 16.23 17.879999 15.72 16.51 14.19 16.08 16.879999 16.91 18.41 16.790001 18.18 18.08 16.610001 18.360001 18.620001 19.68 20.290001 19.469999 16.84 12.54 12.6 12.41 13.62 13.42 14.75 13.91 14.19 14.19 15.32 16.07 17.469999 16.110001 18.299999 18.08 18.389999 14.66 15.55 15.07 14.67 11.87 15.41 16.450001 16.120001 15.56 11.89 10.75 12.78 14.95 26.610001 27.870001 31.459999 27.370001 27.24 30.48 30.610001 29.6 30.799999 33.48 30.719999 13.02 13.67 10.96 10.72 17.48 17.9 17.120001 20.209999 18.83 13.02 11.86 10.89 10.72 11.21 10.66 7.06 6.95 17.969999 18.559999 18.299999 19.469999
