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:

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