McGraw-Hill OnlineMcGraw-Hill Higher EducationLearning Center
Student Centre | Instructor Centre | Information Centre | Home
Excel Problems
S&P Market Insight
S&P Projects
Appendix A
Finance Around the World
Chapter Overview
PowerPoint Presentations
Multiple Choice Quiz
Internet Exercises
Excel Problems
Web Links
Feedback
Help Center


Investments 4/c/e
Investments, 4th Canadian Edition, 4/e
Zvi Bodie, Boston University School of Management
Alex Kane, University of California, San Diego
Alan Marcus, Boston College
Stylianos Perrakis, Concordia University
Peter Ryan, University of Ottawa
Index Models and the Arbitrage Pricing Theory

Excel Problems

Prepared by William Lim, University of New Brunswick.

Estimating Beta Coefficients - Click here to download the spreadsheet (154.0K)

The spreadsheet Betas contains 60 months returns for 10 individual stocks. Returns are estimated using the last five years ending in December 2000. The data also contains returns for S&P 500 Index and the observed risk free rates as measured by the 1-year Treasury bill. With this data, monthly risk premiums for the individual securities and the market as measured by the S&P 500 Index can be used with the regression module in Excel. The data also contains returns for an equally weighted portfolio of the individual securities. The regression module is available under Tools Data Analysis. The dependent variable is the individual return as a function of the independent market return.

A sample of the output from the regression is shown below:

Sample Output (219.0K)
The estimated beta coefficient for American Express shows that the estimated beta coefficient is 1.21 and that 48% of the variance in returns for American Express can be explained by the returns on the S&P 500 Index.

Question:
Using the risk premiums for Problem 1 in the Chapter 8 Betas Worksheet, calculate the beta coefficients for the individual securities and the equally weighted portfolio. Discuss the following questions.

  1. Are any of the individual security intercept terms statistically significant?
  2. Which, if any, of the individual beta coefficients are statistically significant?
  3. Compute an average of each of the individual beta coefficients. Compare that beta coefficient from the regression analysis for the equally weighted portfolio.
  4. Compare the R-sqr for the portfolio to individual beta coefficient estimates. Do the R-sqrs behave as you would expect? Explain.

Click here to download solutions (161.0K)





McGraw-Hill/Irwin




Copyright2003 McGraw-Hill Higher Education
Any use is subject to the Terms of Use and Privacy Policy.
McGraw-Hill Higher Education is one of the many fine businesses of The McGraw-Hill Companies.