Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Calculating Beta in Excel

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can someone point me in the right direction on how to calculate the beta of a company? I've learned how to unlever and relever the beta but I'm more interested in how sites like Yahoo and Bloomberg calculate the original Beta. I have an excel database with the S&P values for the past 5 years as well as the stock prices of my company and our peers. I guess I'm asking if there is a statistical addin for excel which will help me calculate our Beta.

    Thanks,
    Rob P.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does this help ?

    http://www.dryden.com/finance/theory...t/preface.html

    doesn't look free though...

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    409
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-24 10:38, Rob P. wrote:
    Can someone point me in the right direction on how to calculate the beta of a company? I've learned how to unlever and relever the beta but I'm more interested in how sites like Yahoo and Bloomberg calculate the original Beta. I have an excel database with the S&P values for the past 5 years as well as the stock prices of my company and our peers. I guess I'm asking if there is a statistical addin for excel which will help me calculate our Beta.

    Thanks,
    Rob P.
    Convert your prices into % monthly returns (don't forget dividends) and then use Tools/Data Analysis/Regression on your stock returns (Y-values) & market returns (X-values). Don't select "Constant is zero". 60 months is ideal. The coefficient of X = slope of best-fit line = beta.

    The pros probably calculate Beta on the basis of excess returns over the risk-free rate, ie. 30-day T-Bill, but as a practical matter it should be similar.

    Good luck.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The pros ought not to be calculating a perpetual equity based on the rate of 30-day t-bill. A long-range blended equity/debt calculation [absolutely] must be calculated with a long term debt rate (more risk, higher rate); otherwise, you're comparing apples with oranges. They should've been using the 30-year t-bond (higher yield instrument), it's been cancelled as of late (helping my mortgage re-fi), but historically, you should be able to get rates of this instrument for the same periods (timing is imperative).

    Cheers, Nate

    [ This Message was edited by: NateO on 2002-02-26 18:34 ]

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    WA state
    Posts
    409
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Agreed, LT Treasuries are better. 30-year Treasury still traded, just no new issuances for now.

  6. #6
    New Member
    Join Date
    Jun 2011
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculating Beta in Excel

    This spreadsheet will help you to calculate stock Beta in Excel

    Quote Originally Posted by Rob P. View Post
    Can someone point me in the right direction on how to calculate the beta of a company? I've learned how to unlever and relever the beta but I'm more interested in how sites like Yahoo and Bloomberg calculate the original Beta. I have an excel database with the S&P values for the past 5 years as well as the stock prices of my company and our peers. I guess I'm asking if there is a statistical addin for excel which will help me calculate our Beta.

    Thanks,
    Rob P.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •