NxSharpe - Calculates the Sharpe Ratio

Returns the Sharpe ratio.

Syntax

NxSharpe(X,Freq, $R_f$)

X
is the portfolio's rate of returns data series (a one-dimensional array of cells (e.g., rows or columns)).
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.
$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.

Status

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

Remarks

1. The shape ratio is the average return earned over the risk-free rate per volatility or total risk unit.
2. The Sharpe ratio is expressed as follows: $$\textrm{Sharpe Ratio} = \frac{R_p-R_f}{\sigma}$$ Where:
• $R_p$ is the return of a given portfolio or strategy.
• $R_f$ is the risk-free return.
• $\sigma$ is the standard deviation or volatility of the portfolio excess return.
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 or T-note.
4. The Sharpe ratio can evaluate a portfolio’s past performance (ex-post) where actual returns are used in the formula and help explain whether a portfolio's excess returns are due to smart investment decisions or a result of too much risk.
5. By definition, all values in the input data set (i.e., X) 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.
8. If the risk-free rate of return argument contains multiple values, their size must be equal to the size of X.

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)
=NxSharpe(\$B\$2:\$B\$14,12, 0.02) Sharpe (Fund) (0.816652)
=NxSharpe(\$C\$2:\$C\$14,12, 0.02) Sharpe (Index) (0.176527)