How to Calculate Stock Beta in Excel

Stock beta is a measurement of risk of a particular stock relative to the risk of the entire stock market. Before choosing a particular stock for your portfolio, you can check the beta of that particular stock to decide how risky the particular stock is. Though beta is not the only factor to consider before investing in a stock, you can make a wise decision by considering beta as well.

Beta is defined as follows:

β = Covariance(rs, rb)/Variance(rb) where

rs is the return on the stock and rb is the return on a benchmark index. You can choose the benchmark index as you want.

If the beta of a particular stock is one, then that stock has the same risk as that of the market. If the beta value is greater than one, then that particular stock is riskier than its benchmark and vice versa.

Let us calculate the beta of Apple Inc with respect to the benchmark index S&P 500.

Calculate Stock Beta Step

STEP 1. Go to any of the reliable finance sites and download historical data for the period you want. Here I am getting the monthly data of Apple Inc (AAPL) from 1st September,2010 to 7th April 2014 from finance.yahoo.com. Visit the site finance.yahoo.com and in the Search Finance textbox, type AAPL like this:

Yahoo Apple

Click Search Finance button. Then, click Historical Prices from the left side menu (circled in red).

Yahoo Apple Historical Prices

Step 2. In the Start Date: combo box, set the values to Sep, 1 and 2010 and change the frequency to Monthly. Now your screen will look like this:

Yahoo Apple Set Range

Click the Get Prices button. Scroll down to the bottom and you will find a link Download to Spreadsheet like this:

Yahoo Apple Download to Spreadsheet

Click the link and a .csv file will be downloaded to your computer. Open the file and the data will be seen like this:

Yahoo Apple Downloaded

Step 3. To get the S&P 500 index between 1st September,2010 and 7th April 2014, visit the site: http://research.stlouisfed.org/fred2/series/SP500/downloaddata

Select Monthly from the Frequency: drop down and type 2010-09-01 in the Date Range: textbox. Now your screen will look like this:

SP500 data

Click the Download Data button and an Excel file will be downloaded to your computer. If you open the file, your screen will look like this:

SP500 data downloaded

Step 4. Open Excel and save your file as beta.xlsx. Type “Apple Inc. (AAPL)” in A1, “Date” in A3, “AAPL” in B3, “S&P 500” in C3, “AAPL %” in D3, and “S&P 500 %” in E3. You can format these cells and make them bold. Now your screen will look like this:

Beta format data

Step 5. Open the .csv file which contains the historical data of AAPL stock. Copy the values in the column named Date (cells A2 to A45) and paste in the cells A4 to A47 of beta.xlsx.

Step 6. Copy the data in the column named Adj Close (cells G2 to G45) from .csv file. Paste the data in the cells B4 to B47 of beta.xlsx.

Step 7. Open the Excel file that contains the S&P 500 index data. If you analyze the data, you could find that it is given in the ascending order, starting from 01-09-2010 to 01-04-2014. We have to sort the data in the descending order so that we can copy the data easily. Select the data (cells from A19 to B63). Go to Home (main menu) –>Sort & Filter (from the Editing group) and click Sort Newest to Oldest.

Sort newest to oldest

You could find that the data is sorted in descending order with respect to date.

Step 8. Copy data from B20 to B63 and paste it in cells C4 to C47 of the beta.xlsx. Now your screen will look like this:

Beta data sorted

Step 9. To calculate the percentage returns, click inside the cell D4. Enter =(B4-B5)/B5. Click inside the cell E4 and enter =(C4-C5)/C5.

Step 10. Copy the formula in cell D4 and paste it in the cells from D5 to D47 and copy the formula in cell E4 and paste it in the cells from E5 to E47. Now your screen will look like this:

Beta Data percent change

Change the value in the cell C4, D47 and E47 to 0 as #NA will create problems when we create beta value.

Step 11. Type “Numerator” in H1, “Denominator” in H2 and “Beta” in H3. Click inside the cell I1. Go to Formulas (main menu) –>More Functions (from the Function Library) –> Statistical and select the COVARIANCE.P function.

Beta Covariance.P function

You will get a new window like this:

Beta Covariance.P function arguments

Enter D4:D47 in Array1 textbox and enter E4:E47 in Array2 textbox. Click OK.

Step 12. Click inside the cell I2. Go to Formulas (in the main menu) –> More Functions (from the Function Library) –> Statistical and select the VAR.P function.

Beta Var.P function

You will get a window like this:

Beta Var.P function arguments

Enter E4:E47 in the Number1 textbox and click OK.

Step 13. Click inside the cell I3 and enter the formula =I1/I2. You can cross check the beta value using the SLOPE formula as well. Click inside the cell J3 and enter the formula =SLOPE(D4:D47,E4:E47)

You could find that both the formulas give the same result.

As per the calculation, the beta value of AAPL stock is .0397 which is very low. This indicates that the AAPL’s stock price does not vary significantly even if the S&P swings up and down.

Template

You can download the Template here – Download
Previous articleHow To Link Objects In Excel
Next articleGoogle Classroom Tutorial