Calculation of Beta Using Real Data
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.