When you invest in financial markets, your primary goal is to generate “Alpha”. It’s an important metric for determining how your portfolio performs compared to the overall market. Hence you always aim for a higher alpha to get an “edge” over others. Alpha is usually used in conjunction with Beta, which measures the overall risk of your portfolio. In this post, we’ll learn a step-by-step process for stock alpha calculation in a spreadsheet. The read-only version of the spreadsheet is available for you to access free of cost from the end of this post.
Stock Alpha Overview
Let’s try understanding Alpha with a simple illustration.
As an active investor in the stock market, suppose you are managing a portfolio that generates an average return of 12% YoY. And let’s say the broader market index (say S&P500) generates an average return of 8% in the same time period.
Since you are generating 4% more returns compared to the market index, your portfolio’s Alpha value is 4%. Clearly, you are doing better than the market index in this case.
Alpha may also have a negative value if you are making lower returns than the index. A portfolio with negative alpha needs to be rebalanced for long-term sustainability in the market.
One of the shortcomings of the above calculation is that it doesn’t consider the “risk” associated with the investment. A portfolio generating an Alpha of 4% is no good if it’s exposed to high risk. That’s when Jansen’s Alpha comes to the rescue as it considers the Beta value of the portfolio while calculating the excess returns over the index. We’ll learn more about it in the following sections.
Also Read: How to Calculate Expectancy in an Excel Sheet?
Stock Alpha Calculation
Below is the formula to calculate Jensen’s Alpha:
Jensen’s Alpha = Actual return – Risk-free return – Beta x (Benchmark return – Risk-free return)
Here, the benchmark return can be considered as the return of standard indices like the S&P500 or Nifty50.
The risk-free return is the interest rate an investor can expect to earn on an investment that carries zero risk. Generally, the risk-free return is considered equal to the interest paid on a government Treasury bill, one of the safest investments an investor can make.
Let’s take an example:
Suppose the actual return of your portfolio is 15% with a Beta of 1.2 and the risk-free return is 4%. The benchmark yielded 8% in the same time period.
Alpha = 0.15 – 0.04 – 1.2 x (0.08-0.04)
= 0.062
= 6.2%
The risk adjusted Jensen’s Alpha is 6.2% while the standard alpha is (15-8) = 7%
Steps to calculate Stock Alpha in a Spreadsheet
The above Jensen’s Alpha formula can be used to calculate the stock alpha in a spreadsheet. This spreadsheet is an extension of the previous spreadsheet we posted for stock beta calculation.
For this tutorial, we’ll use Google spreadsheet for stock alpha calculation. Google spreadsheet allows you to fetch historical stock prices using the Google Finance function, and hence there is no manual work involved to operate this spreadsheet.
Let’s first look at the end state of the spreadsheet and then we can backtrack on how it was built:
Cell B2 contains the symbol of the stock for which the Alpha value is calculated. In this case, it is NSE:ADANIENT. You may change the value in this cell to calculate the alpha value for the stock you want
Cell B3 contains the benchmark symbol of NSE Nifty against which the Alpha is calculated.
Cell B4 contains the risk-free rate. For Indian markets, use the RBI 91-day Treasury bill rate for this purpose. You may find it in this link.
From row 6 onwards, we have fetched the close price for the stock and benchmark symbols using the Google finance function.
If you highlight cells A7 or E7, you’ll see the formula to fetch the data.
=GOOGLEFINANCE(B2, “close”, TODAY()-1000,TODAY())
The above formula takes “NSE:ADANIENT” as input from cell B2, the value “close” instructs the function to only fetch the close price, and the next two parameters ensure that the values are fetched for the past 1000 calendar days. Please note that you should fetch close price for atleast a year to get an accurate value of alpha.
Cell I7 onwards contains the calculation of risk-free returns. The calculation starts with the value 100, and on a daily basis it is incremented with a factor of (risk-free rate/365).
The data should be fetched for the same historical period for the stock symbol, benchmark symbol, and risk-free return.
Cells H2, H3, and H4 contain the aggregated returns for stock, benchmark, and risk-free instrument.
The Beta value is calculated at cell K2 using the below formula:
=covariance.p(C:C,G:G)/VARP(G:G)
The above formula calculates the covariance of stock returns against benchmark returns and then divides it by the variance of benchmark returns to calculate the Beta.
And finally, the alpha value is calculated in cell K3 using the below formula:
=H2-H4-K2*(H3-H4)
Here,
H2 = Stock returns
H4 = Risk-Free returns
K2 = Beta value
H3 = Benchmark returns
Access to Spreadsheet
We hope you got a fair idea of stock alpha calculation in a spreadsheet. You may access the spreadsheet using the below link:
https://docs.google.com/spreadsheets/d/1EbJVSPaaNnR95I5HwR-OlmMN0GOx2G6yIImQERCH9Vo/edit?usp=sharing
The spreadsheet is non-editable but please feel free to copy it as needed. Click on File → Make a copy for copying