# NxCAGR - Calculates the Compound Annual Growth Rate

Returns the compound annual growth rate.

## Syntax

NxCAGR(X,Freq)

X
is the portfolio simple rate of returns data series (one/two-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.

#### Status

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

## Remarks

1. The CAGR can be used to calculate the average growth of a single investment.
2. The CAGR can be used to compare different investment types with one another.
3. By definition, all values in the input data set (i.e., X) must be greater than -1.0.
4. 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.
5. 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 non-missing values in the input data series sample.
• $\textrm{Freq}$ is the data sampling frequency per year (i.e., 12=monthly).

## Examples

Example 1:

1
2
3
4
5
6
7
8
9
10
11
12
A B
Date Data
1/1/2017 #N/A
2/1/2017 0.030
3/1/2017 0.020
4/1/2017 -0.007
5/1/2017 0.055
6/1/2017 0.028
7/1/2017 0.002
8/1/2017 -0.117
9/1/2017 0.012
10/1/2017 0.021
11/1/2017 0.111

Formula Description (Result)
=NxCAGR(\$B\$2:\$B\$14,12) CAGR (0.567)