You are to select two stocks and collect five years of month-end stock price data for each stock (December 31, 2010 – January 31, 2016). Please use Yahoo! Finance adjusted closing prices. Please complete the following, using the related Excel functions to answer questions 2-5: 1. Calculate monthly returns for each stock. 2. Calculate the arithmetic average monthly rate of return for each stock. Excel Function: =Average(monthly returns array) 3. Calculate the geometric average monthly rate of return for each stock. a. First add ‘1’ to each monthly return b. Excel Function: =geomean(monthly returns array) – 1 4. Calculate the standard deviation of monthly returns for each stock. Excel Function: =stdev(monthly returns array) 5. Calculate the correlation between the two stocks. Excel Function: =correl(Stock 1 monthly returns array, Stock 2 monthly returns array) What to turn in: 1. One Excel spreadsheet with: a. The following columns with the appropriate titles in Row A. Make sure that the latest month is listed first: Column 1: Month, Year Column 2: Adjusted Closing Prices for stock 1 Column 3: Adjusted Closing Prices for stock 2 Column 4: Monthly Returns (calculated) for stock 1 – format to percentage Column 5: Monthly Returns (calculated) for stock 2 – format to percentage b. Your answers to the questions 2- 5 above. This should be immediately below monthly prices and returns on your spreadsheet. Please label each answer appropriately.
