How to Calculate Stock Beta in Excel-Replicating Yahoo Stock Beta

In a previous post, we presented a method for calculating a stock beta and implemented it in Python. In this follow-up post, we are going to implement the calculation in Excel. We continue to use Facebook as an example.

What is beta?

Recall that,

In finance, the beta (market beta or beta coefficient) is a measure of how an individual asset moves (on average) when the overall stock market increases or decreases. As such, beta is a useful measure of the contribution of an individual asset to the risk of the market portfolio when it is added in small quantity. Thus, beta is referred to as an asset’s non-diversifiable risk, its systematic risk, market risk, or hedge ratio. Beta is not a measure of idiosyncratic risk. Read more

Formula for calculating stock beta

We utilize the following equation to calculate the stock beta,

Quantitative analysis of stock beta

where

  • rS is the stock return,
  • rM is the market return,
  • Cov denotes the return covariance and,
  • Var denotes the return variance.

Excel workbook for calculating stock beta

We downloaded 5 years of monthly Facebook data from Yahoo Finance. We implemented the above equation in Excel and obtained a beta of 1.21

How to calculate stock beta in Excel

The picture below shows Facebook beta calculated by Yahoo Finance. It is 1.26. Our result agrees well with Yahoo’s result, although there is a small difference.

stock beta calculation using Excel

Let us know what you think where the difference comes from.

Further questions

What's your question? Ask it in the discussion forum

Have an answer to the questions below? Post it here or in the forum

LATEST NEWSFrench strikers maintain pressure to reject pension plan
French strikers maintain pressure to reject pension plan

PARIS (AP) — French train and metro drivers, refinery workers, garbage collectors and others were holding further strikes on Wednesday against President Emmanuel Macron’s plan to raise the retirement age to 64, in efforts to keep up pressure on the government amid the ongoing parliamentary…

Stay up-to-date with the latest news - click here
LATEST NEWSDollar hits 3-mth high as Powell flags higher rates
Dollar hits 3-mth high as Powell flags higher rates

LONDON — The dollar scaled multi-month highs against most other major currencies on Wednesday, after Federal Reserve Chair Jerome Powell warned that U.S. interest rates might need to go up even faster and higher than expected to rein in stubborn inflation. Higher rates benefit the…

Stay up-to-date with the latest news - click here
LATEST NEWSGold near one-week low as Fed’s Powell strikes hawkish tone
Gold near one-week low as Fed’s Powell strikes hawkish tone

Gold prices hovered near a one-week low on Wednesday after Federal Reserve Chair Jerome Powell said that U.S. interest rates might need to go higher than expected to control sticky inflation. Spot gold was flat at $1,813.79 per ounce by 0907 GMT, after hitting its…

Stay up-to-date with the latest news - click here
LATEST NEWSChina accuses Washington of trying to block its development
China accuses Washington of trying to block its development

BEIJING (AP) — Is the United States out to sabotage China? Chinese leaders think so. President Xi Jinping accused Washington this week of trying to isolate his country and hold back its development. That reflects the ruling Communist Party’s growing frustration that its pursuit of…

Stay up-to-date with the latest news - click here
LATEST NEWSStocks slump, dollar flies on hawkish Powell
Stocks slump, dollar flies on hawkish Powell

LONDON — Stocks fell, the dollar hit multi-month highs and U.S. and German short-term bond yields were parked at their highest levels since at least 2008 as Federal Reserve chair Jerome Powell put big rate hikes back on the table to tame inflation. Ahead of…

Stay up-to-date with the latest news - click here

2 Comments

Leave a Reply