OthersPortfolio Management

Beta Formula In Excel (With Marketxls)

Written by admin
Sat Jul 29 2017
low beta
See how MarketXLS helps you take advantage in the markets.
Download Option Templates →
low beta
 

This article explains how to use MarketXLS Beta formula in Excel to get stock beta values fast in Excel. What does beta mean, how to interpret it and the concept of negative beta?

Beta for stocks

What beta really tells us is the volatility of a stock. It tells us how large or small is the moment of the stock relative to a neutral level (generally the stock index). Beta does not provide any indication of the trend or the direction of the movement. In simple terms, it means that if you are looking for a stable company whose share price does not move a lot then you want the beta to be as small as possible. And if you’re looking for a company or a stock that moves quite a lot then you are looking for beta which is as high as possible.

So the companies with large beta have the potential to go on upside quicker and vice versa. These stocks may make you make more money but also lose more money a lot quicker than the stocks that have lower beta.

Typically day traders will look for companies with the higher beta so they can gain as much as possible in short period of time.Basically, the market itself has a beta of 1. Stocks that have a higher volatility will have a higher beta. Let’s say that the beta of one stock is 1.2, that means typically this stock is 20% more volatile than the stock market. That’s why generally when the stock market is not doing very good people generally sell off stocks with higher beta and it invests in the stocks with lower beta.

Below are the companies in S&P 500 that have the highest beta values as on 27th July 2017 calculated with the beta formula in excel

high beta

And these are the companies with the lowest Beta values 27th July 2017

low beta

 

How to calculate beta in Excel – Beta formula in Excel

To calculate Beta, calculate the slope of series of returns of the stock and of the index. Excel provides a formula =Slope(Series1, Series2) to do that. However, MarketXLS exposes the function called =Beta(“Symbol”) to just return the current value of the beta against the respective index.

Beta formula in Excel

Interpret the Beta of a Stock

When the beta of a stock is less than 1 it that there is less systematic risk than the market. AAPL with the beta of 1.4 would go up/do down even more than the remainder of the market. And when the market goes down, AAPL goes down even further than the market. That is why we can say that it has more systematic risk than average. If a firm has a beta of zero, it would mean that it has no systematic risk. So, it does not react to the movements in the market at all.

Negative Beta

It is also possible that you have stocks that have a negative beta. The stocks with negative beta would typically move in the opposite direction of the market. These stocks serve as hedges when the market is going in the direction you expect it to.

Below are some of the stocks from S&P stocks list that have the negative beta as on today.

 

negative beta

Negative beta is very rare and in most cases, you will see stocks that have a positive beta.

 

References: Investopedia, Wiki

 

Interested in building, analyzing and managing Portfolios in Excel?
Download our Free Portfolio Template
Download Option Templates
Real gdpReal personal consumption expReal private investmentReal govt expenditureReal net exportsReal exportsReal importsFederal receiptsFederal outlaysFederal surplus or deficitFederal debtReal private investment nonresidentialReal private investment residentialReal potential gdpReal personal incomeReal personal consumption exp monthlyRpce durable goodsRpce nondurable goodsRpce servicesPersonal savings rateMonetary baseCurrency in circulationBank reservesMoney supply m1Money supply m2Sp500DjiaWilshire indexVixFinancial stress indexCorporate bond index aaCorporate bond index bbbFederal funds rateTreasury rate 3mTreasury rate 1yTreasury rate 5yTreasury rate 10yTips 5yTips 10yBond yield aaaBond yield baaMortgage rate 15yMortgage rate 30yUs dollar weighted averageUsdollar to euroUsdollar to poundYuan to usdollarCanadiandollar to usdollarYen to usdollarCpiCpi wo food energyCpi foodCpi energyChain price indexChain price index wo food energyGdp price deflatorPpi final demandPpi finished goodsPpi materialPpi crude goodsPpi final demand wo food energyPpi finished goods wo food energyHouse price indexHouse price index 20cityCrude oil priceGasoline priceNatural gas priceIndustrial productionCapacity utilisationInventoriesSales retail foodVehicle sales light weightManufacture orders durablesManufacture orders capital goodsLoansConsumer credit outstandingCorporate profitsHousing startsBuilding permitsResidential constructionEmployees nonfarmEmployees privateEmployees goods producingEmployees service providingEmployees governmentUnemployment rateInitial cliamsAverage weeks unemployedJob openingsHiresSeparationsQuitsLayoffs dischargesHours of productionHourly earningsReal outputPopulationLabor forceLabor force participation rate
Search for a stock

Top MarketXLS Rank stocks

Penske Automotive Group Inc. logo

Penske Automotive Group Inc.

108.90
 
USD
 
0.11
 
(0.10%)
91
Rank
Optionable: Yes
Market Cap: 8,953 M
Industry: Auto & Truck Dealerships
52 week range    
72.35   
   119.47
Zim Integrated Shipping Services Ltd logo

Zim Integrated Shipping Services Ltd

63.76
 
USD
 
2.46
 
(4.01%)
80
Rank
Optionable: Yes
Market Cap: 7,792 M
Industry: Deep Sea Freight Transportation
52 week range    
33.71   
   91.23
Kronos Worldwide Inc logo

Kronos Worldwide Inc

18.02
 
USD
 
1.54
 
(9.34%)
80
Rank
Optionable: Yes
Market Cap: 1,897 M
Industry: Specialty Chemicals
52 week range    
12.06   
   18.31
Group 1 Automotive Inc. logo

Group 1 Automotive Inc.

179.61
 
USD
 
5.39
 
(3.09%)
80
Rank
Optionable: Yes
Market Cap: 3,101 M
Industry: Auto & Truck Dealerships
52 week range    
143.00   
   212.23
Universal Logistics Holdings Inc. logo

Universal Logistics Holdings Inc.

24.62
 
USD
 
-0.68
 
(-2.69%)
79
Rank
Optionable: No
Market Cap: 684 M
Industry: Trucking
52 week range    
15.86   
   26.90
NRG Energy Inc. logo

NRG Energy Inc.

46.21
 
USD
 
0.45
 
(0.98%)
79
Rank
Optionable: Yes
Market Cap: 10,725 M
Industry: Utilities - Independent Power Producers
52 week range    
31.94   
   46.71
First BanCorp. logo

First BanCorp.

13.64
 
USD
 
-0.16
 
(-1.16%)
79
Rank
Optionable: Yes
Market Cap: 2,795 M
Industry: Banks - Regional - US
52 week range    
10.88   
   16.62
TriCo Bancshares logo

TriCo Bancshares

42.29
 
USD
 
-0.42
 
(-0.98%)
78
Rank
Optionable: Yes
Market Cap: 1,468 M
Industry: Banks - Regional - US
52 week range    
37.41   
   48.96
ManTech International Corporation logo

ManTech International Corporation

94.25
 
USD
 
-0.18
 
(-0.19%)
78
Rank
Optionable: Yes
Market Cap: 3,860 M
Industry: Software - Application
52 week range    
66.91   
   94.73
Universal Corporation logo

Universal Corporation

60.06
 
USD
 
-2.61
 
(-4.16%)
77
Rank
Optionable: Yes
Market Cap: 1,529 M
Industry: Tobacco
52 week range    
46.24   
   63.46

More Features

Stand with Ukraine

As the situation in Ukraine escalates, many of us in MarketXLS are left with emotions too overwhelming to name. If you’d like to show your support, but aren’t sure how to, we want to help make it easier for you to act.

For any amount donated, we’ll extend your MarketXLS subscription for double of the donated amount. Please send proof of your payment to support@marketxls.com to avail the extention

From all of us at MarketXLS, thank you!