Calculating Beta in Excel

R

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.
 
Sort by date Sort by votes
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.
Click to expand...
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. :smile:
 
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

Rob P. said:
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.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question Question
Formula to calculate pricing per row after total goes over threshhold
Replies
3
Views
74
Dave Patton
D
Jyggalag
  • Question Question
Debt payment in Excel?
Replies
1
Views
301
Jyggalag
Jyggalag
O
  • Question Question
I am new to VBA and I am trying to figure out how I can use VBA to fill out an online risk calculator using data from excel
Replies
2
Views
403
John_w
J
E
  • Solved
Performance issue using a combination of dropdowns & lookups
Replies
9
Views
875
Akuini
Akuini
V
  • Solved
Stock history formula how to convert GBp to GBP
Replies
2
Views
739
vampsthevampyre
V

Share this page

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