Finance 254

Assignment #3

Calculating Betas Using Raw Data

Due October 12 by 4 PM in your TA’s mailbox in 340 Wohlers.

Use Excel to complete this assignment.

The estimation of company betas is normally brushed over in many introductory finance texts. This often leads students to view beta as some magical number and its not until later courses in finance that they realize that the choice of different returns and intervals examined can deliver different betas estimates. This internet exercise takes you through the basic calculation of beta using Excel in order to help demystify a company beta. Along the way you will find how to download share price and index data of the internet which can then be manipulated in Excel. The Yahoo Finance site has a wealth of financial information on companies and countries from all over the world. Once you arrive at this site you will find a box titled "Get Quotes" which you need to type in the stock symbol (if you are unsure of the stock symbol go to "Symbol Lookup"). To download financial data from the internet follow the detailed instructions in the text box below:

Instructions for Downloading Stock Data

  • In the Get Quotes box type in the stock symbol (for example Shell is SC and the S&P500 index is ^SPC).
  • Select Chart from the dropdown box to the right and click on the Get Quotes button. Click on monthly link next to Historical Quotes which is located underneath the graphs of stock prices and stock volumes.
  • The first page of data should now be visible on the screen. Scroll down until you come to Download Spreadsheet Format.  Notice the boxes at the top of the page that allow you to change the time span for the data and/or stock (you can change these and click the Get Historical Data button for the new time span).
  • Save the data to a file.
  • Open Excel and then open the file you saved.
  • At this point you should have five columns including the; Date, Open, High, Low and Closing share prices, plus Volume data.
  1. First download into Excel the Shell Transport & Trading Company's (SC), Microsoft’s  (MSFT) and S&P500 (^SPC) monthly data for the three-year period from the 1st July1998 to 30th June 2001 using the instructions outlined above. Using the closing price data calculate the monthly returns. The basic formula for calculating each months returns is shown below:

  1. What is the average monthly return for Shell, Microsoft and the S&P500 index?
  2. Calculate the variance of return for the S&P500 using the population variance formula in Excel (if you are not sure how this is done go to the help menu and search for variance. Step by step instructions will appear for you to follow. This process can be repeated for any other calculation you may wish e.g. correlation and covariance).
  3. Using Excel calculate the covariance between the Shell & S&P500 and the Microsoft & S&P 500 monthly returns.
  4. Now calculate beta for both Shell and Microsoft using the following formula for each stock:

An alternative method of calculating beta is to calculate a regression. Excel has a regression tool which can be found in Data Analysis in Tools. Once you have calculated a regression between Shell and S&P500 you will find the beta shown as X Variable under the heading Coefficients and in the regression output. (Note: If your Excel package does not currently show the Data Analysis option go to Add-ins under Tools and select it to be loaded).  As an alternative to the full-blown regression analysis, use the Excel’s Slope or Linest function to use estimate the slope of the linear regression line, which is beta.

  1. What is the annualized average return for Shell, Microsoft, and the S&P 500?  Please convert your answers to percentages.  (Hint: translate the monthly average return into an Annual Percentage (or Quoted) Rate = APR.)
  2. Now use the CAPM to estimate Shell’s and Microsoft’s required return at the end of June 2001.  Use the annualized average return for the S&P 500 (from the last question) over the 3-year period as the estimate for the market return and the June 2001 3-month T-bill rate of 4% as the risk-free rate.
  3. Noticing that the market return is too low for your tastes in #6 and #7, re-estimate required returns for Shell and Microsoft using the historical average market risk premium of 9.5% and the same T-bill rate of 4%.
  4. Would you have liked either Shell or Microsoft as investments if you thought their expected returns were their annualized average returns from questions #6?  Explain your answer.
  5. Now imagine at the end of June 2001, you did exhaustive analysis and called your Psychic Friend and expected Shell’s stock price to reach $56 in a year and Microsoft’s to reach $85 in a year.  What’s the expected return for each stock, and would you have bought either of these stocks?  Explain your answer.
  6. What is the current price of Shell and Microsoft?  Assuming it’s been 3 months since the end of June, what is each stock’s APR (annualized return)?
  7. Now, download into Excel the NASDAQ’s (ticker: ^IXIC) monthly data for the three-year period from the 1st July1998 to 30th June 2001 using the instructions outlined above. Using the closing price data calculate the monthly returns. 
  8. Calculate the beta and then the required return for Microsoft only (since it trades on the NASDAQ) using the NASDAQ monthly returns as the market proxy.  Use 4% has the risk free rate and the difference between the historical average return for Small-Company Stocks and U.S. Treasury Bills from Table 6-1 on page 171 of your textbook as the market risk premium in the CAPM.