Calculating Beta in Excel

Rob P. · Feb 24th, 2002, 04:38 PM

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.

Chris Davison · Feb 24th, 2002, 09:47 PM

Does this help ?

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

doesn't look free though...

Scott R · Feb 26th, 2002, 11:13 PM

Quote:

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.

NateO · Feb 27th, 2002, 12:26 AM

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 ]

Scott R · Feb 27th, 2002, 04:02 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 24th, 2002, 04:38 PM	#1
Rob P. Join Date: Feb 2002 Posts: 22	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.

Feb 24th, 2002, 09:47 PM	#2
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Does this help ? http://www.dryden.com/finance/theory...t/preface.html doesn't look free though...

Feb 27th, 2002, 12:26 AM	#4
NateO MrExcel MVP Admin Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,635	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 ]

Feb 27th, 2002, 04:02 PM	#5
Scott R Join Date: Feb 2002 Location: Spokane, WA Posts: 250	Agreed, LT Treasuries are better. 30-year Treasury still traded, just no new issuances for now.