Calculate the Beta Coefficient of a stock

Beta CoefficientAs part of the Corporate Finance subject of my MBA we have to calculate the beta coefficient (aka Beta) of a company’s share price. The is applied economics and market mathematics, but it simply is a number which indicates how closely (or not) a company’s share price moves in relation to a broader market (or an index like the S&P ASX200 or the Dow Jones Industrial Average).

Anyway, part of the process is to create an excel format spreadsheet to aid with the calculation. I’ve done it and thought I share it with you. It will work with any spreadsheet program that can open .xls files

Update 1: 31 July 2009 the new version is available for testing by requesting it in a comment below. Once the testing is complete I’ll make it freely available to registered users.

Update 2: 7 July 2011 For a limited time you can get the Beta Coefficient Calculator without registering. Enter a valid email address in this form to receive a link to download the file. You’ll also get an email about new versions and bug fixes (with an opt-out link).

Update 3: 23 July 2011 If the form doesn’t work for you, please comment below and I’ll email it to you. The form is quicker but some people can’t get the download.

Enter your email address to download Beta Coefficient Calculator by Wealth Esteem

Version 0.9 beta_8
* Enabled automatic download via web form
* Added French and German to Translation tab (alpha code there)

Version 0.9 beta_7
* Fixed bug in date text description
* Updated sample data to 6 Jun 2011
* Changed sample data to BHP Billiton
* Made Instructions tab text translatable
* Cosmetic fixes

Version 0.9 beta_6
* Explained Translation tab in instructions and asked for volunteers
* Updated sample data to 2011
* Updated copyright date to 2011

Version 0.9 beta_5
* Added Change Log tab
* Moved text of index symbol & stock symbol to translation tab

Version 0.9 beta_4
* Added explanation of index and stock symbol fields on Beta Coefficient tab
* Updated copyright date to 2010

Version 0.9 beta_3
* Added email address to instructions tab

Version 0.9 beta_2
* Added logic to translate number of periods to a date range and show that range to users in Beta Coefficient tab at C11
* Swapped order of Security and Index tabs
* Fixed bug in coefficient calculation
* Introduced version numbering and control

Version 0.9 beta_1
* Updated instructions tab

Version 0.9 beta
* Initial public version

Support and feedback via the comments below.

93 thoughts on “Calculate the Beta Coefficient of a stock”

  1. hi, is there any chance I can get to see your beta calculation with the formulas on the excel spreadsheet?
    pls, it’s an emergency so I hope to hear from u soon.
    Ann

  2. @Ann I’ve emailed you a spreadsheet. The old spreadsheet I had up was corrupt so I removed it, then promptly changed laptops and lost it.

    In the meantime the best spreadsheet I’ve found for Beta Coefficient Calculation is at Money-Zine.com. You can download a spreadsheet from there.

  3. Hey Paul Zag! Long time no speak!
    Was wondering if you still had that beta spreadsheet. Finishing off my BBus (Financial Planning) and need to calculate Beta for a company… and do all the other gear. Anyways, if you could shoot thru an email, it’d be great. I checked the website you suggested, but it’s just got the CAPM calculator that requires you to already have a value for beta…. which of course is what I’m trying to ascertain.

    Hope to hear from you soon mate.
    Cheers
    BR

  4. I’ve just about finished rebuilding the spreadsheet and am making available for testing. Everyone who’s left a comment so far should have received a spreadsheet from me, but the latest version has only been available this week so @Allan and @kim got it.

    Anybody else who wants to test it can comment here and I’ll email them the current version.

    Once it goes live it will be available for free but you’ll have to register with a valid email address to get it. I wont be spamming you, just want to know who’s using my work.

  5. Hi Paul. Would you mind resending the beta spread sheet please to my new email address above, my partner mistakenly deleted it. Many thanks Kim

  6. Please make your excel spreadsheet for Beta calculations available to me. I am taking an intermediate excel course along with an introductory finance course–and need practice!

  7. Paul,

    If you could possibly send the spreadsheet to me, I would so appreciate it. I have a group assignment with an online course, and no one in the group is understanding the examples given to us. Thanks!

  8. Paul, is it possible to add me to the list of people in need of the spreadsheet?
    Thanks a lot.
    The longer you leave this post the more people you get!

  9. can you email me anything that can help calculate stock beta. i’m working on a finance project in which i am given the company’s excess returns and the excess returns market portfolio. i have to find the stock beta and correlation coefficient. it would greatly appreciated.

    also if you have instructions for creating a characteristic line graph in excel that would be awesome!-thanks

  10. Paul:

    I’m coming late to the party. Regardless, I’d appreciate being emailed a copy of your Excel spreadsheet for calculating Beta for stocks. Thanks in advance.

    Jay

  11. Dear Paul,
    Am working on an assignment and would be a great help if you could send me a copy of your Beta calculator. Have been struggling a lot to calculate the beta values.

    Thanks,
    Arjo

  12. Hola Pablo,
    Estoy haciendo un trabajo y sería una gran ayuda si me pueden enviar una copia de hoja de cálculo Beta.
    Gracias,
    Luis Fernando

  13. Hi Paul

    Your Beta spreadsheet is popular! Is it possible that I also get a copy of your Beta Calculation spreadsheet? Much appreciated, thanks, Feroze

Leave a Reply

Your email address will not be published. Required fields are marked *