Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Calculate Beta Coefficient

CAPM - Cost of Equity

What is Beta Coefficient?

The Beta coefficient formula is a financial metric that measures how likely the price of a stock/security will change in relation to the movement in the market price. The Beta of the stock/security is also used for measuring the systematic risks associated with the specific investment.

The beta is the degree of change in the outcome variable for every 1 unit change in the predictor variable. A standardized beta compares the strength of the effect of each individual independent variable to the dependent variable. The greater the absolute value of the Beta Coefficient, the stronger will be the impact.

Beta formula is used in CAPM model to calculate the Cost of Equity as shown below –

Cost of Equity = Risk Free Rate + Beta x Risk Premium

Beta Coefficient Meaning

The Beta is calculated in the CAPM model (Capital Asset Pricing Model) for computing the rate of return of a stock or portfolio.

Beta Values

The  Beta calculation in excel is a form analysis since it represents the slope of the security’s characteristic line i.e. straight line indicating the relationship between the rate of return on a stock and the return from the market. This can further be ascertained with the help of the below Beta formula:

Beta coefficient

The meanings of beta coefficient –

  • If the coefficient is 1 it indicates the price of the stock /security is moving in line with the market
  • If coefficient
  • If the coefficient > 1; the returns from the security are more likely to respond to market movements thereby also making it volatile.

Beta Coefficient Example

If Apple Inc’s (AAPL) beta is 1.46 it indicates that the stock is highly volatile and is 46% more likely to respond to movement in the markets. On the other hand, say Coca-Cola has a β coefficient of 0.77 indicating the stocks are less volatile and 23% less likely to respond towards movement in the market.

As a trend, it has been observed that utility stock have a CAPM Beta of less than 1. On the other hand, technology stocks have a Beta coefficient of greater than 1 indicating a likelihood of higher returns with more associated risks.

Beta Coefficient Calculation

Here we will take an example to calculate beta of MakeMyTrip (MMTY) and the Market index as NASDAQ.

You may download the fully solved Beta Calculation Excel Worksheet from here.

There are three Beta formulas – variance/covariance method, slope function in excel and regression formula. We will see each of the beta coefficient formula below –

Step 1 – Download Historical prices and NASDAQ index data from the past 3 years

I have downloaded the data from yahoo finance.

  1. For NASDAQ dataset, please visit this link Yahoo Finance
  2. For Makemytrip prices, please visit this URL here.

NASDAQ Historical Prices Download

Step 2 – Sort the Prices as given below

Sort the dates and adjusted closing prices in the ascending order of dates.You can delete the remaining columns as we don’t need those for beta calculations in excel.

Beta Calculation - Step 2

Step 3 – Prepare the beta coefficient excel sheet as per below

Beta Calculation - Step 3

Step 4 – Calculate Daily Returns

Return formula

Beta Calculation - Step 4

Step 5 – Calculate Beta Formula using the Variance-Covariance method

In this, you need to use the two formulas (variance and covariance in excel) as shown below

Beta Calculation using Variance Covariance

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

Step 6 – Calculate Beta using SLOPE Function in excel

Beta Calculation using SLOPE Method

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

Step 7 – Calculate Beta Coefficient Regression

To use this regression function, select Data analysis from the Data Tab of your Excel Worksheet.Data Analysis

If you are unable to locate Data Analysis in Excel, then you need to install the Analysis ToolPak. This process is relatively easy: Go to FILE -> Options -> Add-Ins -> Analysis ToolPak -> Go -> Check Analysis ToolPak -> OK

Select Data Analysis and click on Regression

Data Analysis - Regression

Choose the Y Input Range and X Input Range

Regression Range

Once you click OK, you get the following Summary Output

Regression Output

You will get the same Beta in each of the three methods.

Advantages of Beta Coefficient Regression

The following are the some of the advantages of Beta regression:

  1. It is used for beta regression is to estimating the Cost of Equity in Valuation models. CAPM estimates an asset’s Beta based on the systematic risk of the market. The cost of equity derived by the CAPM reflects a reality through which how investors have diversified their portfolios for reducing the impact of the unsystematic risks.
  2. It offers an easy to use beta calculation in excel which standardizes a risk measure across multiple firms with varied capital structures and fundamentals.

Disadvantages of Beta Coefficient Regression

The following are the some of the disadvantages of Beta regression:

  1. There is a heavy reliance on past returns and does not consider updated information/other factors which can impact the returns in the future.
  2. Beta regression as more return is garnered, the measure of Beta changes and so will the cost of equity.
  3. Though systematic risks are inherent to the market in explaining asset returns, the portion of unsystematic risks is ignored.

Negative Beta

A negative beta formula means an investment that moves in the opposite direction against the stock market. When the market rises, the negative beta tends to fall down and when the market falls, the negative-beta will tend to rise. This is generally true for gold stocks and gold bullion. Since Gold is a more secure store of value than currency, a crash in the market prompts investors to liquidate their stocks and convert into currency (for zero beta) or purchase gold in case of negative beta coefficient.

A negative beta is not highlighting the fact that there is an absence of risk but it means that the investment offers a hedge against an unforeseen market downturn. However, if the market continues to rise, a negative-beta coefficient strategy is losing money through opportunity risk (loss of a specific chance to earn higher returns) and also inflation risk (rate of return not keeping pace with the prevailing inflation in the country).

Recommended Articles

This has been a guide to Beta Coefficient, formulas, and calculations. Here we calculate beta in excel of MakeMyTrip using three methods – Variance-Covariance, SLOPE Function and Regression Function. You may also have a look at these other recommended articles to learn more about valuations –

  • Calculate WACC
  • Risk Premium Formula
  • Financial Modeling Templates
  • Risk-Adjusted Return

The post Calculate Beta Coefficient appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.

This post first appeared on Free Investment Banking Tutorials |WallStreetMojo, please read the originial post: here

Share the post

Calculate Beta Coefficient


Subscribe to Free Investment Banking Tutorials |wallstreetmojo

Get updates delivered right to your inbox!

Thank you for your subscription