Returns the compound annual growth rate.
Syntax
NxCAGR(X,Freq)
 X
 is the portfolio simple rate of returns data series (one/twodimensional 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.
Status
The NxCAGR function is available starting with NumXL version 1.68 CAMEL.
Remarks
 The CAGR can be used to calculate the average growth of a single investment.
 The CAGR can be used to compare different investment types with one another.
 By definition, all values in the input data set (i.e., X) must be greater than 1.0.
 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.
 The CAGR is computed as follows:
$$\textrm{CAGR}=\sqrt[k]{\frac{V_E}{V_B}}1$$
$$\textrm{CAGR}=\sqrt[k]{\prod_{i=1}^{N}{(1+r_i)}}1$$
$$k = \frac{N}{\textrm{Freq}}$$
Where:
 $V_E$ is the portfolio ending value (respectively).
 $V_B$ is the portfolio beginning value (respectively).
 $k$ is the time span (in years) between the portfolio’s beginning and ending values.
 $N$ is the number of nonmissing values in the input data series sample.
 $\textrm{Freq}$ is the data sampling frequency per year (i.e., 12=monthly).
Examples
Example 1:


Formula  Description (Result) 

=NxCAGR(\$B\$2:\$B\$14,12)  CAGR (0.567) 
Files Examples
Related Links
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
Check out the documentation in the GitHub Wiki, for more indepth and uptodate instructions.
Please sign in to leave a comment.