# CAGR

## Basic Overview

AVAILABLE SINCE 5.7.0

 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 Node: An input node with values to use for start and end dates in the calculation NumberOfPeriods: The number of dimension levels previous to each level to compare values between, e.g. if the current level is 2022 and the NumberOfPeriods = 2 then the start level will be 2020 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 dimensionThe value entered for [Level] is not used by the entered nodeThe value entered for [Level] is higher in the dimension than the level used in the nodee.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 nodeThe NumberOfPeriods value is not a whole number equal to 1 or greater

## 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