Details

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

r_s is the return on the stock and r_b 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

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:

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

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:

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

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

Step 3. To get the S&P 500 index between 1st September,2010 and 7th April 2014, visit the site

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:

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:

Step 4. Open Excel 2010 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:

Step 4. 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 5. 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 6. 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.

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

Step 7. 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:

Step 8. 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 9. 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:

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

Step 10. 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.

You will get a new window like this:

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

Step 11. 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.

You will get a window like this:

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

Step 12. 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.