Excel Assignment

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.