NxTreynor - Calculates the Treynor Ratio

Returns the Treynor ratio.

Syntax

NxTreynor($R_p$, $R_f$, Freq, $\beta$)

$R_p$
is the portfolio rate of returns data series (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.
$\beta$
is the portfolio's Capital Asset Pricing Model (CAPM) beta.

  Status

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

Remarks

  1. The Treynor ratio is a performance metric for determining how much excess return was generated for each unit of risk taken by the portfolio.
  2. The Treynor ratio is expressed as follows: $$\textrm{Treynor Ratio} = \frac{R_p-R_f}{\beta}$$ Where:
    • $R_p$ is the return of a given portfolio or strategy.
    • $R_f$ is the risk-free return.
    • $\beta$ is the CAPM Beta of the portfolio.
  3. The risk-free rate of return is the return of an investment with zero risks, meaning it's the return investors could expect for taking no risk. The risk-free rate could be a U.S. Treasury rate or yield, such as a one-month T-bill.
  4. The Treynor ratio measures how successful an investment is in providing compensation to investors for taking on investment risk.
  5. By definition, all values in the input data set (i.e., $R_p$) must be greater than -1.0.
  6. The input data series may include missing values (e.g., #N/A, #VALUE!, #NUM!, empty cell), but they will not be included in the calculations.
  7. If the risk-free rate of return argument contains one value, it is assumed the value is the annual percentage risk-free rate of returns.

Examples

Example 1:

 
1
2
3
4
5
6
7
8
9
10
11
12
A B C
Date Fund Index
1/1/2017 #N/A #N/A
2/1/2017 0.030 0.020
3/1/2017 0.020 -0.040
4/1/2017 -0.007 -0.007
5/1/2017 0.055 0.055
6/1/2017 0.028 0.028
7/1/2017 0.002 0.002
8/1/2017 -0.117 -0.10
9/1/2017 0.012 0.02
10/1/2017 0.021 0.021
11/1/2017 0.111 0.05



Formula Description (Result)
=NxTreynore(\$B\$2:\$B\$14,12, 0.02, 1.1) Treynor (Fund) (0.146876)
=NxTreynore(\$C\$2:\$C\$14,12, 0.02, 1.0) Treynor (Index) (0.028069)

Files Examples

Related Links

References

Comments

Please sign in to leave a comment.

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