Thanks:  0
Likes:  0

# Thread: Calculating Beta in Excel

1. 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. Does this help ?

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

doesn't look free though...

3. 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. 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. Agreed, LT Treasuries are better. 30-year Treasury still traded, just no new issuances for now.

6. ## Re: Calculating Beta in Excel

This spreadsheet will help you to calculate stock Beta in Excel

Originally Posted by Rob P.
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.

## User Tag List

#### Posting Permissions

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