WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All In One Bundle
  • Login
Home » Investment Banking Tutorials » Valuation Tutorials » Beta Formula

Beta Formula

By Pritha BanerjeePritha Banerjee | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Beta Formula Calculation

Beta is a measure of the volatility of the stock as compared to the overall stock market. We can calculate beta using three formulas –

  1. Covariance/Variance Method
  2. By Slope Method in Excel
  3. Correlation Method

Top 3 Formula to Calculate Beta

Let us discuss each of the beta formulas in detail –

Beta-Formula

#1- Covariance/Variance Method

Beta Formula = Covariance (Ri, Rm) / Variance (Rm)

Covariance( Ri, Rm) = Σ ( R i,n – R i,avg ) * ( R m,n – R m,avg ) / (n-1)
Variance (Rm) = Σ (R m,n – R m,avg ) ^2 / n

To calculate the covariance, we must know the return of the stock and also the return of the market, which is taken as a benchmark value. We must also know the variance of the market return.

#2 -By Slope Method in Excel

We can also calculate Beta by using the slope function in excel. The Microsoft Excel SLOPE function returns the slope of a regression line based on the data points, which are identified by % change in NASDAQ and % change of the company, which we are calculating.

% change is calculated as below:

Return = Closing Share Price – Opening Share Price / Opening Share Price

slope excel

#3 – Correlation Method

Beta can also be calculated using the correlation method. Beta can be calculated by dividing the asset’s standard deviation of returns by the market’s standard deviation of returns. The result is then multiplied by the correlation of security’s return and the market’s return.

Beta Formula = Σ Correlation (R i, Rm) * σi / σm

Step by Step Beta Calculation

Step 1: First, download Historical prices and NASDAQ index data from the past 3 years.

You can download the data from yahoo finance, as I have done below.

#1 – For NASDAQ Dataset, Please visit this link – (finance.yahoo.com/).

equation step 1

#2 – For Google Prices, Please visit this URL – finance.yahoo.com

equation step 1.2

Step 2: Then Sort the Prices as Done Below.

Then we need to sort the dates of the stock prices and adjusted closing prices in ascending order of dates. We need only these two columns, and the remaining columns can be deleted as we don’t have use of those for beta calculations in excel.

Popular Course in this category
Sale
Investment Banking Training (117 Courses, 25+ Projects)
4.9 (831 ratings)
117 Courses | 25+ Projects | 600+ Hours | Full Lifetime Access | Certificate of Completion
View Course

beta calculation step 2

Step 3: Then, prepare the beta coefficient excel sheet, as shown below. We put both the data in one sheet.

beta calculation step3

Step 4: Then calculate Daily Returns we get.

Return = Closing Share Price – Opening Share Price / Opening Share Price

Step 5: Then, calculate Beta by the Variance-Covariance method.

In this case, we need to use the two formulas (formulas of variance and covariance in excel), as shown below:

beta calculation step5

Using the variance-covariance method, we get the Beta as 0.16548 (Beta Coefficient)

Step 6: Calculate Beta using SLOPE Function available in excel

beta calculation step6

Using this SLOPE function method, we again get the Beta as 1.2051 (Beta Coefficient)

Examples of Beta Formula

Let’s take an example to understand the calculation of the beta equation in a better manner.

You can download this Beta Formula Excel Template here – Beta Formula Excel Template

Using Correlation Method – Example #1

An investor is looking to calculate the beta of company XYZ as compared to the NASDAQ. Based on data over the past three years, the correlation between the firm XYZ and NASDAQ is 0.82. XYZ has a standard deviation of returns of 22.12%, and NASDAQ has a standard deviation of returns of 22.21%.

Solution:

Use the following data for the calculation of the beta.

Excel 1.1

So, the calculation of the beta –

Excel 1

Beta of XYZ = 0.82 x (0.2212 ÷ 0.2221)

Beta of XYZ = 0.817

As we have seen in this case, Company XYZ is considered less risky than the market NASDAQ as its beta of 0.817.

Example #2

We will discuss some examples using data from the industry.

Now we will take an example to calculate the beta of Google and the Market index as NASDAQ. We will calculate the Beta of Google and Amazon in excel– variance/covariance method, slope function. We will see each of the beta coefficient calculations.

Calculation of Beta of Google using correlation and covariance in excel

Excel Google

We will calculate the beta of Google as compared to NASDAQ.

Based on data over the past three years, take the data from Yahoo finance and calculate Beta as below:-

  • Beta = Covariance (Ri, Rm) / Variance (Rm)
  • Beta = 0.165

In this case, Google is considered less volatile than NASDAQ as its beta of 0.165.

Example #3

We will calculate the beta of Amazon as compared to NASDAQ.

Excel Amazon

Based on data over the past three years, take the data from Yahoo finance and calculate Beta as below:

Beta = Covariance (Ri, Rm) / Variance (Rm)

Beta = 0.000135 

In this case, Amazon zero correlation with the market movements.

Relevance and Uses

Beta indicates whether an investment is more volatile or less volatile. Beta, which has a value of 1, indicates that it exactly moves in accordance with the market value.

A higher beta indicates that the stock is riskier, and a lower beta indicates that the stock is less volatile as compared to the market. Mostly Betas generally fall between the values of range 1.0 to 2.0. The beta of a stock or fund is always compared to the market/benchmark. The beta of the market is equal to 1. If a stock is benchmarked against the market and has a beta value greater than 1 (for example, we consider it as 1.6), this indicates that the stock is 60 percent riskier than the market as the beta of the market is 1.

Beta is used in the formulae of capital asset pricing model (CAPM), which is used to calculate the expected return of an asset based on the value of beta and expected market return.

Recommended Articles

This article has been a guide to Beta Formula. Here we learn how to calculate beta using the top 3 methods along with practical examples and a downloadable excel template. You can learn more about financial analysis from the following articles –

  • Formula of Levered Beta 
  • Equity Beta Formula
  • Gini Coefficient
  • Unlevered Beta Formula
1 Shares
Share
Tweet
Share
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Login

Forgot Password?

WallStreetMojo

Download Beta Formula Excel Template

Special Offer - Investment Banking Training (117 Courses, 25+ Projects) View More