Skip to main content
Skip table of contents

CAGR

Basic Overview

Description

The Compound Annual Growth Rate (CAGR) function measures an investment's annual growth rate over time, with the effect of compounding taken into account.

Signature

CAGR(Node, NumberOfPeriods [, Level])

Parameters

  • Node1: An input node with values to use for start and end dates in the calculation

  • NumberOfPeriods: The number of dimension level values previous to each level value taken into comparison, e.g. if the current level value is 2022 and the NumberOfPeriods is 2, then the start level value will be 2020

    • NumberOfPeriods can also be a node.

  • Level: The name of the level to find the first values in respect to (see limitations for values allowed). If no [Level] is provided, the time dimension will be used.

Limitations

If any of the following points occurs, an error will appear:

  • If no value is entered for [Level], and the entered node does not include a level from the time dimension

  • The value entered for [Level] is not used by the entered node

  • The value entered for [Level] is higher in the dimension than the level used in the node

    • e.g. if the node uses ‘Month’ the inputted level is ‘Year’

  • The entered value for [Level] is a linked level of another level used by the entered node

  • The NumberOfPeriods value is not a whole number equal to 1 or greater

  • If NumberOfPeriods is a node, it cannot have any levels available.

Example

Input:

Node A

Year

Country

Revenue

2020

DE

100

2021

DE

200

2022

DE

300

2023

DE

400

2024

DE

500

2025

DE

600

2020

US

100

2021

US

200

2022

US

250

2024

US

300

2025

US

400

To find the CAGR value based on the two years before for each country, the following configuration of the function would be used:

CAGR('Node A', 2) = CAGR('Node A', 2, “Year”)

Year

Country

CARG Value

2022

DE

73.21%

2023

DE

41.42%

2024

DE

29.10%

2025

DE

22.47%

2022

US

58.11%

2024

US

9.54%

Please note there is no 2023 value for the US in the original data. This means the CARG values can not be calculated for the year 2023 (as there is no end value) and 2025 (as there is no start value).

The logic works using the location of levels relative to each other in the dimension. NOT using the rows in the provided node data table. For example in the following dimension these levels exist:

Dimension: Product

Level Name

Level Key

Product A

1

Product B

2

Product C

3

Which is used in the following Node:

Node B

Year

Product

Revenue

2020

Product A

100

2020

Product B

250

2020

Product C

200

2021

Product A

150

2021

Product B

250

2021

Product C

250

2022

Product A

200

2022

Product C

300

Then using the function CAGR('Node B', 1, “Product”) will produce the results:

Year

Product

CAGR Value

2020

Product B

150%

2020

Product C

-20%

2021

Product B

66.67%

2021

Product C

0.00%

As Product A has no prior level in the dimension hierarchy, no CAGR can be provided. Product B and Product C both have prior levels.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.