11

Calculate Jensen’s Alpha with Excel

Jensen’s Alpha is a risk-adjusted performance benchmark that tells you how by much the returns of an actively managed portfolio are above or below market returns.

Originating in the late 1960s, Jensen’s Alpha (often abbreviated to Alpha) was developed to evaluate the skill of active fund managers in stock picking.

  • A positive Alpha means that a portfolio has beaten the market, while a negative value indicates underperformance
  • A fund manager with a negative alpha and a beta greater than one has added risk to the portfolio but has poorer performance than the market

Careful stock picking and financial engineering means that investors can add alpha to a portfolio without adversely affecting beta.

Jensen's Alpha Excel

According to the Capital Asset Pricing Model, Alpha is defined by this equation

alpha = rs – [rf + β (rb - rf)]

where rs is the expected portfolio return, rf is the risk-free rate, β is the portfolio beta, and rb is the market return.  Beta describes the volatility of the portfolio with respect to that of the wider market, and is calculated with this equation

Stock Beta EquationThe market return is usually described by the expected return of an index fund, like the FTSE or S&P500.

Calculate Alpha with Excel

Thse steps describe how you can calculate Alpha with Excel (there’s a link to download the tutorial spreadsheet at the bottom).  The screegrabs describe the formulae used in the spreadsheet.

Step 1: Put the returns of your portfolio and the benchmark index into Excel, and calculate the average returns

Jensens AlphaStep 2. Define your risk free rate. If the returns specified in Step 1 are monthly returns, then your risk free rate has to be on a monthly basis.

Step 3. Calculate the portfolio Beta, and then the Alpha.

Jensen's Alpha in Excel

Download Excel Spreadsheet to Calculate Jensen’s Alpha with Excel


11 Responses to "Calculate Jensen’s Alpha with Excel"

  1. Tom says:

    The F9 cell Alpha formula is wrong : it should be “B20-E6-F8*(C20-E6)” rather than “B20-E6+F8*(C20-E6)”

  2. CK says:

    Isn’t this a monthly alpha calculation? How would you annualize?

  3. Fred says:

    Your calculation is based on the assumption that the risk free rate was the same for every period which is unlikely to be the case. How would you modify the calculation to take into account varying risk free rates over the time period covered?

  4. Bill says:

    Given the monthly and yearly calculation does this then mean a daily alpha would be(1+alpha)^250-1?

  5. PB3 says:

    If I have data for 9 years worth of back testing, how do I calculate the average annual alpha? Here are my inputs:
    Beta: 0.88
    Portfolio Return: 925%
    Risk Free Return: 16.76%
    Market Return:16.13%

    Is calculating alpha over such a long period accepted?

  6. AK says:

    Is it possible to find Alpha through a regression, if so how? I need to find if alpha is significant, t-stat will do.
    thanks

Leave a Reply

Submit Comment
What is 7 + 15 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)
© 2014 Invest Excel. All rights reserved. XHTML / CSS Valid.

Facebook