How To Calculate Beta in Excel

Beta is one of the most popular risk management tools.  It is widely used by market professionals all over the world.  Beta determines the volatility of a security in correlation to a benchmark index.  Typically, the benchmark that most professionals use is the S&P 500.  Other markets may use different benchmarks such as the Lehman Brothers Aggregate Bond index.  Calculating the beta of a stock or security can be done using Microsoft Excel.  You will need to utilize its SLOPE function for the computation.

  1. Interpreting Beta.  Beta is simple to read.  If a Beta is greater one, then the security or the market is much more volatile than the benchmark.  If it is less than one, then the stock is less volatile.  Beta can be used to calculate yields and returns for a particular stock.
  2. Open excel.  To start calculating the Beta, open a new workbook on Excel.  Create two columns and start encoding the historical data of the stock or security and the benchmark.  Make sure that your data is accurate and truthful as this can greatly impact any result from the calculation.
  3. Formula for data change.  Once you have completed the encoding the data of the stock on both columns, create a third column and enter this formula in the cell: = ((cell2-cell1)/cell1)*100.  Cell 1 is the data for the previous or past date while cell 2 is the data of the current date.  The formula is used to calculate the change of the data of a stock in percentage terms.  Since you entered the formula on the third column, the result will be plotted there.  To use the same formula all the way down to the last point on the third column, just copy the cell with the formula encoded and highlight all the cells going downward.  Once you reach the last point, press enter.
  4. Formula for Beta.  To calculate the Beta, you will need to use Microsoft’s Excel’s SLOPE function.  The formula for this is: = SLOPE (% of equity change range, % range of change of index).  Let us use a fictitious stock as an example.  Say we have a security with a daily change in price; we would put this in cell 1.  An example would be C1:500.  Now on a different cell we can place the daily changes of the benchmark such as the S&P 500.  As an example, let’s put 501.  In this case it will be D1: 501.  Based on the examples the formula will look like this: =SLOPE (C1:500, D1: 501).  The result will be the Beta.

In the case that you are computer illiterate and may have tremendous difficulty in figuring out how to use Excel, have someone knowledgeable plot the data for you.  Whatever the situation, it is easy to calculate and interpret Beta for as long you know the formulas and functions to use.  Make sure you have the correct data for the benchmark and the security.  Without these, you will not be able to do any calculations.


Share this article!

Follow us!

Find more helpful articles: