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.