Instructions Open the file "project part1.xlsx", attached with this assignment. Replace my name in the blue cell with the names of your group members I will compare your answers in the yellow cells against my answers. DO NOT MOVE THE YELLOW OR BLUE CELLS. If the yellow cells are moved, you will not get any points for what's in them. You can fill in any data/formulas in the empty cells, but do not create new columns or new rows. Look at the appendix (on the last pages of this PDF handout). Choose ANY THREE of these stocks. Try to choose stocks from at least two different industries Fill in the tickers of your chosen stocks in the yellow cells indicated. For example, in the yellow cell next to "Stock 1 ticker:", fill in the ticker of the first stock you chose. Download monthly data on prices and dividends from finance.yahoo.com from Decem- ber 1 2012 to January 1 2019 on the three stocks you have chosen Assume that you trade at the close price and that all dividends are only available to you on the first trading day of the month after they are "paid". For instance, a dividend paid on July 11, 2014 is not available to you until the first trading day in August 2014 Do not use the "Adjusted Close" 1 1. Calculate the monthly return on each of your three stocks for each month in 2013 through 2018. Fill in these returns in the yellow cells under "HPR over month" for each of the three stocks. For example, fill in the returns for your first stock in the range H6:H77. The return from buying on 12/31/2012 and selling on 1/31/2013 goes in cell H6. Use the empty columns in D6:D77, E6:E77, F6:F77, G6:G77 to insert any data you need to calculate the returns. Repeat for the other two stocks in the appropriate spaces 2. Suppose you are standing at the end of 2015. You have access to data that's available at that point, but no "future" data. Using the 36 months of returns for 2013, 2014, and 2015 only, calculate: (a) The expected monthly return on each stock (b) The variance of the monthly return on each stock (c) The standard deviation of the monthly return on each stock (d) The covariance between each pair of stocks (e) The correlation between each pair of stocks Fill the values in the appropriate yellow cells. 3. To think about: which correlation is highest? Which is lowest? Does this agree with your intuition? 4. Now imagine you are standing at the end of 2018. Using the 36 months of returns for 2016, 2017, and 2018 only, calculate: (a) The average monthly return on each stock (b) The variance of the monthly return on each stock (c) The standard deviation of the monthly return on each stock (d) The covariance between each pair of stocks (e) The correlation between each pair of stocks Fill the values in the appropriate yellow cells 5. To think about: are the quantities calculated using data from 2013-2015 a useful indicator of what happens from 2016-2018? Stock 1 ticker: Stock 3 ticker: Stock 2 ticker AMGN TXN HON HPR over month HPR over month Buy on HPR over month 12/31/2012 1/31/2013 1/31/2013 2/28/2013 2/28/2013 3/31/2013 3/31/2013 4/30/2013 4/30/2013 5/31/2013 5/31/2013 6/30/2013 6/30/2013 7/31/2013 7/31/2013 8/31/2013 8/31/2013 9/30/2013 9/30/2013 10/31/2013 10/31/2013 11/30/2013 11/30/2013 12/31/2013 12/31/2013 1/31/2014 1/31/2014 2/28/2014 2/28/2014 3/31/2014 3/31/2014 4/30/2014 4/30/2014 5/31/2014 5/31/2014 6/30/2014 6/30/2014 7/31/2014 7/31/2014 8/31/2014 8/31/2014 9/30/2014 9/30/2014 10/31/2014 10/31/2014 11/30/2014 11/30/2014 12/31/2014 12/31/2014 1/31/2015 1/31/2015 2/28/2015 2/28/2015 3/31/2015 3/31/2015 4/30/2015 4/30/2015 5/31/2015 5/31/2015 6/30/2015 6/30/2015 7/31/2015 7/31/2015 8/31/2015 8/31/2015 9/30/2015 9/30/2015 10/31/2015 10/31/2015 11/30/2015 11/30/2015 12/31/2015 12/31/2015 1/31/2016 1/31/2016 2/29/2016 2/29/2016 3/31/2016 3/31/2016 4/30/2016 4/30/2016 5/31/2016 5/31/2016 6/30/2016 6/30/2016 7/31/2016 7/31/2016 8/31/2016 8/31/2016 9/30/2016 9/30/2016 10/31/2016 10/31/2016 11/30/2016 11/30/2016 12/31/2016 12/31/2016 1/31/2017 1/31/2017 2/28/2017 2/28/2017 3/31/2017 3/31/2017 4/30/2017 4/30/2017 5/31/2017 5/31/2017 6/30/2017 6/30/2017 7/31/2017 7/31/2017 8/31/2017 8/31/2017 9/30/2017 9/30/2017 10/31/2017 10/31/2017 11/30/2017 11/30/2017 12/31/2017 12/31/2017 1/31/2018 1/31/2018 2/28/2018 2/28/2018 3/31/2018 3/31/2018 4/30/2018 4/30/2018 5/31/2018 5/31/2018 6/30/2018 6/30/2018 7/31/2018 7/31/2018 8/31/2018 8/31/2018 9/30/2018 9/30/2018 10/31/2018 10/31/2018 11/30/2018 11/30/2018 12/31/2018 Summary statistics using data up to 2015-end Expected return Variance Standard deviation Value of covariance Value of correlation between stock 1 stock 2 TXN AMGN between stock 1 TXN stock 3 HON between stock 2 AMGN stock 3 HON Summary statistics using data from 2016 up to 2018-end Average return Variance Standard deviation Value of covariance Value of correlation between stock 1 stock 2 TXN AMGN between stock 1 TXN stock 3 HON between stock 2 AMGN stock 3 HON Instructions Open the file "project part1.xlsx", attached with this assignment. Replace my name in the blue cell with the names of your group members I will compare your answers in the yellow cells against my answers. DO NOT MOVE THE YELLOW OR BLUE CELLS. If the yellow cells are moved, you will not get any points for what's in them. You can fill in any data/formulas in the empty cells, but do not create new columns or new rows. Look at the appendix (on the last pages of this PDF handout). Choose ANY THREE of these stocks. Try to choose stocks from at least two different industries Fill in the tickers of your chosen stocks in the yellow cells indicated. For example, in the yellow cell next to "Stock 1 ticker:", fill in the ticker of the first stock you chose. Download monthly data on prices and dividends from finance.yahoo.com from Decem- ber 1 2012 to January 1 2019 on the three stocks you have chosen Assume that you trade at the close price and that all dividends are only available to you on the first trading day of the month after they are "paid". For instance, a dividend paid on July 11, 2014 is not available to you until the first trading day in August 2014 Do not use the "Adjusted Close" 1 1. Calculate the monthly return on each of your three stocks for each month in 2013 through 2018. Fill in these returns in the yellow cells under "HPR over month" for each of the three stocks. For example, fill in the returns for your first stock in the range H6:H77. The return from buying on 12/31/2012 and selling on 1/31/2013 goes in cell H6. Use the empty columns in D6:D77, E6:E77, F6:F77, G6:G77 to insert any data you need to calculate the returns. Repeat for the other two stocks in the appropriate spaces 2. Suppose you are standing at the end of 2015. You have access to data that's available at that point, but no "future" data. Using the 36 months of returns for 2013, 2014, and 2015 only, calculate: (a) The expected monthly return on each stock (b) The variance of the monthly return on each stock (c) The standard deviation of the monthly return on each stock (d) The covariance between each pair of stocks (e) The correlation between each pair of stocks Fill the values in the appropriate yellow cells. 3. To think about: which correlation is highest? Which is lowest? Does this agree with your intuition? 4. Now imagine you are standing at the end of 2018. Using the 36 months of returns for 2016, 2017, and 2018 only, calculate: (a) The average monthly return on each stock (b) The variance of the monthly return on each stock (c) The standard deviation of the monthly return on each stock (d) The covariance between each pair of stocks (e) The correlation between each pair of stocks Fill the values in the appropriate yellow cells 5. To think about: are the quantities calculated using data from 2013-2015 a useful indicator of what happens from 2016-2018? Stock 1 ticker: Stock 3 ticker: Stock 2 ticker AMGN TXN HON HPR over month HPR over month Buy on HPR over month 12/31/2012 1/31/2013 1/31/2013 2/28/2013 2/28/2013 3/31/2013 3/31/2013 4/30/2013 4/30/2013 5/31/2013 5/31/2013 6/30/2013 6/30/2013 7/31/2013 7/31/2013 8/31/2013 8/31/2013 9/30/2013 9/30/2013 10/31/2013 10/31/2013 11/30/2013 11/30/2013 12/31/2013 12/31/2013 1/31/2014 1/31/2014 2/28/2014 2/28/2014 3/31/2014 3/31/2014 4/30/2014 4/30/2014 5/31/2014 5/31/2014 6/30/2014 6/30/2014 7/31/2014 7/31/2014 8/31/2014 8/31/2014 9/30/2014 9/30/2014 10/31/2014 10/31/2014 11/30/2014 11/30/2014 12/31/2014 12/31/2014 1/31/2015 1/31/2015 2/28/2015 2/28/2015 3/31/2015 3/31/2015 4/30/2015 4/30/2015 5/31/2015 5/31/2015 6/30/2015 6/30/2015 7/31/2015 7/31/2015 8/31/2015 8/31/2015 9/30/2015 9/30/2015 10/31/2015 10/31/2015 11/30/2015 11/30/2015 12/31/2015 12/31/2015 1/31/2016 1/31/2016 2/29/2016 2/29/2016 3/31/2016 3/31/2016 4/30/2016 4/30/2016 5/31/2016 5/31/2016 6/30/2016 6/30/2016 7/31/2016 7/31/2016 8/31/2016 8/31/2016 9/30/2016 9/30/2016 10/31/2016 10/31/2016 11/30/2016 11/30/2016 12/31/2016 12/31/2016 1/31/2017 1/31/2017 2/28/2017 2/28/2017 3/31/2017 3/31/2017 4/30/2017 4/30/2017 5/31/2017 5/31/2017 6/30/2017 6/30/2017 7/31/2017 7/31/2017 8/31/2017 8/31/2017 9/30/2017 9/30/2017 10/31/2017 10/31/2017 11/30/2017 11/30/2017 12/31/2017 12/31/2017 1/31/2018 1/31/2018 2/28/2018 2/28/2018 3/31/2018 3/31/2018 4/30/2018 4/30/2018 5/31/2018 5/31/2018 6/30/2018 6/30/2018 7/31/2018 7/31/2018 8/31/2018 8/31/2018 9/30/2018 9/30/2018 10/31/2018 10/31/2018 11/30/2018 11/30/2018 12/31/2018 Summary statistics using data up to 2015-end Expected return Variance Standard deviation Value of covariance Value of correlation between stock 1 stock 2 TXN AMGN between stock 1 TXN stock 3 HON between stock 2 AMGN stock 3 HON Summary statistics using data from 2016 up to 2018-end Average return Variance Standard deviation Value of covariance Value of correlation between stock 1 stock 2 TXN AMGN between stock 1 TXN stock 3 HON between stock 2 AMGN stock 3 HON