NxJensen - Calculates Jensen's Measure (Alpha)

Returns the Jensen's alpha measure in annual percentage rate (APR).

Syntax

NxJensen($R^i$,$R^b$, $R_f$, Freq)

$R^i$
is the portfolio simple rate of returns data series (a one-dimensional array of cells (e.g., rows or columns)).
$R^b$
is the index/benchmark simple returns data (a one-dimensional array of cells (e.g., rows or columns)).
$R_f$
is the risk-free simple returns data (a single value or a one-dimensional array of cells (e.g., rows or columns)). If missing, a zero (0) risk-free return is assumed.
Freq
is the data sampling frequency per year (i.e., number of data points in one year) (e.g., 12 = monthly, 4 = quarterly, etc.). If missing, a monthly frequency is assumed.

Status

The NxJensen function is available starting with NumXL version 1.68 CAMEL.

Remarks

1. In finance, Jensen's alpha (or Jensen's Performance Index, ex-post alpha) determines the abnormal return of a security or portfolio of securities over the theoretical expected return.
2. The Capital Asset Pricing Model (CAPM) describes the relationship between systematic risk and expected return for assets.
3. Per the CAPM, the formula for calculating the expected return of an asset is:
$$E[R^i] = R_f + \beta \times (E[R^b] - R_f)$$
Where:
• $E[R^i]$ is the expected return on investment.
• $R_f$ is the risk-free rate of return.
• $E[R^b]$ is the expected return on the overall market.
• $\beta$ is the CAPM beta of the investment.
4. Jensen's alpha is a risk-adjusted performance measure representing the average return on a portfolio or investment, above or below that predicted by the CAPM, given the portfolio's or investment's beta and the average market return.
5. Jensen's alpha accounts for the risk-free rate of return.
6. By definition, all values in the input data set (i.e., X) must be greater than -1.
7. The input data series may include missing values (e.g., #N/A, #VALUE!, #NUM!, empty cell) but will not be included in the calculations.
8. The sample data ($R^i$, $R^b$, or $R_f$) may include missing values.
9. The number of rows of the response variable ($R^i$) must equal the number of rows of the explanatory variable ($R^b$ or $R_f$).
10. Observations (i.e., rows) with missing values in $R^i$, $R^b$, or $R_f$ are removed.
11. If the risk-free rate of return ($R_f$) argument contains one value, it is assumed the value is the risk-free annual rate of return.
12. If the risk-free rate or return argument ($R_f$) contains multiple values (i.e., array), its size must be equal to the size of the portfolio return ($R^i$).

Examples

Example 1:

1
2
3
4
5
6
7
8
9
10
11
12
A B C D
Date Fund $R^b$ $R_f$
1/1/2017 #N/A #N/A 0.0006
2/1/2017 0.030 0.020 0.0006
3/1/2017 0.020 -0.040 0.0007
4/1/2017 -0.007 -0.007 0.0008
5/1/2017 0.055 0.055 0.0009
6/1/2017 0.028 0.028 0.0008
7/1/2017 0.002 0.002 0.0008
8/1/2017 -0.117 -0.10 0.0009
9/1/2017 0.012 0.02 0.001
10/1/2017 0.021 0.021 0.0011
11/1/2017 0.111 0.05 0.0011

Formula Description (Result)
=NxJensen(\$B\$2:\$B\$14,\$C\$2:\$C\$14,\$D\$2:\$D\$14,12) Jensen Alpha (0.129528)