Calculating Beta in Excel

Rob P.

New Member
Joined
Feb 23, 2002
Messages
29
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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. :)
 
Upvote 0
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
 
Upvote 0
Agreed, LT Treasuries are better. 30-year Treasury still traded, just no new issuances for now.
 
Upvote 0
This spreadsheet will help you to calculate stock Beta in Excel

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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top