NxCAPM - Calculates the Capital Asset Pricing Model (CAPM) Beta

Returns the Capital Asset Pricing Model (CAPM) beta.

Syntax

NxCAPM($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 NxCAPM function is available starting with NumXL version 1.68 CAMEL.

Remarks

  1. The Capital Asset Pricing Model (CAPM) describes the relationship between systematic risk and expected return for assets.
  2. 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.
  3. The beta measures how much risk the investment will add to a portfolio that looks like the market.
  4. The sample data ($R^i$, $R^b$, or $R_f$) may include missing values.
  5. 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$).
  6. Observations (i.e., rows) with missing values in X or Y are removed.
  7. 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.

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)
=NxCAPM(\$B\$2:\$B\$14,\$C\$2:\$C\$14,\$D\$2:\$D\$14) CAPM Beta (1.098847)

Files Examples

Related Links

References

Comments

Please sign in to leave a comment.

Was this article helpful?
2 out of 2 found this helpful