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 onedimensional array of cells (e.g., rows or columns)).
 Index
 is the index/benchmark simple returns data (a onedimensional 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 Upmarket capture ratio (default) 1 Downmarket capture ratio.
Status
The NxMCR function is available starting with NumXL version 1.68 CAMEL.
Remarks
 The upmarket capture ratio measures 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.
 The upmarket capture ratio is calculated by dividing the manager's returns by the index's returns during the upmarket.
 The upmarket capture ratio is calculated as follows: $$\textrm{UpMCR} = \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).
 The downmarket capture ratio measures 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 it dropped.
 The downmarket capture ratio is calculated as follows: $$\textrm{DownMCR} = \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:


Formula  Description (Result) 

=NxCAGR(\$B\$2:\$B\$14,\$C\$2:\$C\$14, 0)  UpMCR (1.351929) 
=NxCAGR(\$B\$2:\$B\$14,\$C\$2:\$C\$14, 1)  DownMCR (0.746671) 
Files Examples
Related Links
 Wikipedia  Compound annual growth rate
 Investopedia  UpMarket Capture Ratio
 Investopedia  DownMarket Capture Ratio
References
 Hamilton, J .D.; Time Series Analysis, Princeton University Press (1994), ISBN 0691042896
 Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0471690740
Comments
Please sign in to leave a comment.