Learning Exercise

Calculation of Beta Using Real Data

This assignment lets students use data available off Yahoo and an excel spreadsheet to compute the beta of any firm they choose.
Course: Introductory/intermediate finance
Share

This site provides monthly stock prices and index values that can be used with excel to compute company betas. The... see more

Exercise

Assignment: Compute the beta of a firmGo to yahoo finance : http://finance.yahoo.com/?u


1. At the top of the page a number of indexes are listed. Choose "S&P500" or "DOW"When you are taken to a graph, choose "5year" at the bottom of the graphAt the bottom of the 5yr table choose "monthly"You will now see a table of monthly figures for the index. At the bottom of this table choose "Download spreadsheet format"Highlight the entire spreadsheet, copy it and paste it into a new excel spreadsheet.


2. Return to the yahoo site above.Click on symbol lookup and type in the name of a company, for example, "sears".Repeat the steps above and paste the monthly stock prices into the same spread sheet as the index values.



3.Compute the monthly returns for both the index and firm. ((ending-beginning)/beginning). The returns on the market should be to the left of the returns on the firm so that the charting wizard will put the Rm on the x axis.


4.Graph the returns. Use the scatter type chart. Do not connect the dots with lines.Click on one of the dots on the chart. All the dots will turn yellow. Right click, choose "add trendline" from the drop down menu.Under the "type" tab choose "linear"Under the "options" tab check that you want the equation displayed on the chart. Click ok.


5. The coefficient of x is the slope of the line and is the beta for the firm.

Disciplines

Audience

Requirements

excel: the student should be able to compute a simple return and to chart two columns of numbers.

Topics

risk and returnCAPM

Learning Objectives

Better understanding how beta is computed. See the disbursion of the data and how inexact the process is. See the options available (choice of index, time period) and how this may influence beta