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
- 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 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:
|
|
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 0-691-04289-6
- Tsay, Ruey S.; Analysis of Financial Time Series John Wiley & SONS. (2005), ISBN 0-471-690740
Comments
Check out the documentation in the GitHub Wiki, for more in-depth and up-to-date instructions.
Please sign in to leave a comment.