Homework Assignment 2

 

Calculating growth rates and betas for financial data series

 

You should complete this homework assignment before the date indicated on the homework assignments page. You are allowed to ask for help from other students or to compare answers with other students, although you should try to do as much as possible of this on your own.

 

This assignment should take between one and two hours to complete.

 

This is a detailed assignment that requires you to use an Excel Workbook labeled Beta Calculation HW and to retrieve and use raw data from Yahoo’s stock price historical data source at http://finance.yahoo.com. 

 

The purpose of this homework is to show you how to, using a data series, (a) calculate real continuous growth rates, and (b) calculate and compare a relative measures of risk,  the Beta.

 

Note: Keep this sheet and data in good order. We will eventually use it for more advanced risk and other calculations.

 

You will want to review the formula for the Beta that I have provided to you in Lecture 2 Compounding Discounting and Risk.  For much of this I am not going to tell you what you need to do.  I want you to figure it out. You will have to use many of Excel’s built in functions to do this.

 

Here are the steps to completing your homework.

 

  1. Open the Excel workbook labeled Beta Calculation HW and look sheet labeled Beta Calculation.  This will be your working sheet. Although there is no data this sheet is already formatted so be careful not to change the formatting of cells in this sheet. On this sheet, you are going to download two years of weekly data for the S&P 500 ETF SPY and for three stocks, Dendreon DNDY, Johnson and Johnson JNJ, and a stock that you choose. Then you are going to calculate continuous growth rates and Betas relative to the S&P500 for your three stocks.
  2. Now go to the Yahoo finance at http://finance.yahoo.com. [Note: As of the date this homework was assigned, you cannot complete this assignment using Google Chrome because it does not allow you to download to an Excel spreadsheet. So use IE or compatible]. On this site you will need to find historical prices for the stocks. The format of yahoo’s finance site changes often but at the time this was prepared you would do the following: On the Investing tab at the top of the home page select Stocks. On the page that appears, select Historical Quotes under Research Tools. A default page should appear showing historical prices for a stock like General Electric GE. You are going to use this page to download your data.
  3. We are going to normalize our Beta calculations to the S&P500. But rather than use actual S&P500 data, we are instead going to use data for the ETF tracking stock SPY, which is priced at 10% of the S&P500 quotation (arbitrage keeps it very close to this value). So your first step will be to override the GE default historical prices with historical prices for SPY (don’t worry about the date range yet). Do that and make sure that you are looking for data for SPY.
  4. For all of the data, use a starting date of  September 6, 2006 and an ending date of September 2, 2008. Set those dates and check the Weekly box for the data series, then Get Prices. Weekly data beginning on September 2, 2008 working backwards will appear (the first closing value should be around 124).
  5. On the bottom of the page you will see a link that says Download to Spreadsheet. Use that link to download this data to a blank Excel spreadsheet (do not load the data into Beta Calculation HW spreadsheet – it is too easy to mess up that spreadsheet). Check your data in the new spreadsheet. There should be 105 observations with the observation dates clearly labeled.
  6. You will want to copy some of this data to the appropriate labeled columns in the Beta Calculation HW spreadsheet. You are going to copy only the date and Adj Close columns (Adjusted Close adjusts historical data for splits and dividend payments). There is a problem though. The data are listed from most current to oldest as you go down the column and because we are going to calculate historical growth rates you need to invert this column. Since the only data that you are going to copy over are the dates and Adj Closes you might minimize mistakes by deleting the middle five columns. Don’t eliminate the date column. Then using the Sort function, sort all columns by sorting the date column as Ascending. This should flip your data upside down. Check and make sure that your most current datum is at the bottom of its column and that the SPY value of around 124 is down there with it. Obviously this homework is gibberish if you don’t do this right. Copy the date and SPY Adj Close columns to their appropriate labeled columns in the Beta Calculation HW spreadsheet. You should still have 105 dates and observations. Although eventually you are going to repeat this procedure for DNDN, JNJ, and the stock that you choose, you will next do some operations on the data that you have downloaded for SPY.
  7. First, in the adjacent appropriate column, use Excel’s LN function and the COPY and PASTE feature to calculate the natural logs of the historical data for SPY.
  8. As discussed in Lecture 2 Compounding Discounting and Risk take the difference in logs for each observation to calculate the 104 observations for the continuous weekly growth rates in the column labeled CGR (this column should be set to default at four decimal places). Your first CGR number should have a value like 0.0172, which you would interpret to mean that the continuous weekly growth rate is 1.7%. If we were using discrete rather than continuous growth rates we would take the geometric mean, but we don’t have to do that here. Take a look at the data. Remember, these data are not annualized. (Also remember that the market, although volatile, was pretty flat during this period).
  9. At the bottom of the spreadsheet you will see a row labeled AVERAGE. Use Excel’s AVERAGE function to calculate the average weekly continuous compounding growth rate (i.e. the geometric mean, given how we have set this up) of SPY in the yellow cell in the CGR column. You should get an answer that is very close to one (because the market was static on net during this period).
  10. Below this you will find a row labeled ANN AVG. To calculate the annual average take value in the previous cell and multiply it times 52. If you have done everything right, you should get a negative number near zero for an answer. Although trivial here, this won’t be the answer for your three stocks. Note also that this “return” on an investment in SPY does not include dividends, which exist for SPY and you may have noticed them when you accessed the original data before download.
  11. Now using the Excel VAR and STDEV commands, calculate the variance and standard deviation of the continuous growth rate. The standard deviation, which will equal the square root of the variance, should have a value of around 0.02. Note that we have calculated the variance of the continuous growth rate and not the unconverted price data.
  12. Now find and download the data for C, DNDN, and the stock of your choice, then calculate the same values for all of them and compare them to SPY. What does this tell you?
  13. Using SPY as the base, and using the formula provided in Lecture 2 Compounding Discounting and Risk calculate the covariances (either using the Excel COV formula or putting in the formula yourself) and Betas of the three stocks.  My two examples should clearly provide an example of a high Beta and a low Beta and that is the result you should get.
  14. How do you interpret these Betas? You will note that the Beta calculation for C looks about right, but the same for DNDN does not. Look at the price history for DNDN. It’s hugely volatile (look at April of 2007). There must be some deficiency with the Beta and there is, which will be discussed in class. (Hint: the correlation coefficient between the two is 0.09). We will need to find better measures of volatility.

 

This homework is complete. You may have noticed that we didn’t use the time columns in this homework. Keep the spreadsheet because we will return to it later in the class to make some more sophisticated risk and volatility estimates.