NxMCR - Calculate the Up/Down Market Capture Ratio

Returns the upside and downside market capture ratio (MCR).

Syntax

NxMCR(X,Index,Freq,Down)

X
is the portfolio simple rate of returns data series (a one-dimensional array of cells (e.g., rows or columns)).
Index
is the index/benchmark simple returns data (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.
Down
is an integer switch to select the MCR type: (0 = Upside MCR (default), 1 = Downside MCR).
Value Description
0 Up-market capture ratio (default)
1 Down-market capture ratio.

Status

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

Remarks

1. The up-market capture ratio is the statistical measure of an investment manager's overall performance in up/bull markets. It is used to evaluate how well an investment manager performed relative to an index during periods when that index has risen.
2. The up-market capture ratio is calculated by dividing the manager's returns by the returns of the index during the up-market.
3. The up-market capture ratio is calculated as follows: $$\textrm{Up-MCR} = \frac{\underset{\forall i, r_i^b>0}{\textrm{CAGR}(r^m)}}{\underset{\forall i, r_i^b>0}{\textrm{CAGR}(r^b)}}$$ Where:
• $r^b$ is the index or benchmark simple return.
• $r^m$ is the portfolio or strategy simple return data set.
• $\forall i, r_i^b>0$ designates the periods in the data set where the benchmark was up (bull).
4. The down-market capture ratio is a statistical measure of an investment manager's overall performance in down/bear markets. It is used to evaluate how well an investment manager performed relative to an index during periods when that index has dropped.
5. The down-market capture ratio is calculated as follows: $$\textrm{Down-MCR} = \frac{\underset{\forall i, r_i^b<0}{\textrm{CAGR}(r^m)}}{\underset{\forall i, r_i^b<0}{\textrm{CAGR}(r^b)}}$$ Where:
• $r^b$ is the index or benchmark simple return.
• $r^m$ is the portfolio or strategy simple return data set.
• $\forall i, r_i^b<0$ designates the periods in the data set where the benchmark was down (bear).

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)
=NxCAGR(\$B\$2:\$B\$14,\$C\$2:\$C\$14, 0) Up-MCR (1.351929)
=NxCAGR(\$B\$2:\$B\$14,\$C\$2:\$C\$14, 1) Down-MCR (0.746671)