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 period (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)

Files Examples

Related Links

References

Comments

  • Check out the documentation in the GitHub Wiki, for more in-depth and up-to-date instructions. 

    0
    Comment actions Permalink

Please sign in to leave a comment.

Was this article helpful?
0 out of 0 found this helpful