# CAGR URL: https://docs.valsight.ai/cagr/ Description: The CAGR function computes the Compound Annual Growth Rate between a current value and the value N periods earlier, taking compounding into account. It works along a chosen level or any non-time dimension. **Category:** [Compare periods](/compare-periods/) ## Overview The **CAGR** function **computes the Compound Annual Growth Rate between a current value and the value N periods earlier**, taking compounding into account. It works along a chosen level (e.g. Year, Month) or any non-time dimension. Use this function when you need to measure compound growth over multiple periods, for example multi-year revenue growth. ## Syntax `CAGR('Node', NumberOfPeriods [, "Level" [, "InvalidHandling"]])` **Example usage:** `CAGR('Revenue', 3)` ## Parameters | Parameter | Description | Type | Required | Default | | ------------------- | ------------------------------------------------------------------------------------------------------- | ------------------------ | -------- | ----------------- | | **Node** | Input node whose values provide the end and start of the CAGR calculation | Node reference | Yes | -- | | **NumberOfPeriods** | How many steps back to compare. Must be a whole number >= 1. Can also be a node without dimensionality. | Number or node reference | Yes | -- | | **Level** | The level along which periods are counted | Level name | No | Time dimension | | **InvalidHandling** | Controls how rows with calculation errors (e.g. division by zero) are treated | Keyword | No | `"FailOnInvalid"` | **InvalidHandling options:** * `"FailOnInvalid"`: Any invalid entry causes an error. This is the default. * `"IgnoreInvalid"`: Invalid entries are silently omitted from the result. If all rows are invalid, the result is empty. ## Output shape | Aspect | Behavior | | -------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | | Dimensionality | Same as input. | | Time range | The first N periods are dropped (no start value N periods back to compare against). Periods with missing start or end values are also dropped. | | Values | CAGR as a ratio: `(end_value / start_value)^(1/N) - 1`. A result of 0.5 means 50% compound annual growth. | | Row count | Reduced. At minimum, the first N periods are removed. | ## Watch out * If **Level** is omitted, the node must include a level from the **time dimension**. * The **Level** must be the **lowest level** in its dimension hierarchy on the input node. You cannot use a higher level (e.g. `"Year"` when the node has `"Month"` data). * The **Level** cannot be a **linked level** of another level on the node. * **NumberOfPeriods** must be a whole number >= 1. If it is a node, that node must have no levels. * A **start value of 0** causes a division-by-zero error. Use `"IgnoreInvalid"` to skip these entries instead of failing. ## Examples ### CAGR along the time dimension This example calculates the compound growth rate over 2 years for each country. Note that missing data points (e.g. US 2028) cause gaps in the result. **Input node:** `'Revenue'` | Year | Country | Value | | ---- | ------- | ----- | | 2025 | DE | 100 | | 2026 | DE | 200 | | 2027 | DE | 300 | | 2028 | DE | 400 | | 2029 | DE | 500 | | 2030 | DE | 600 | | 2025 | US | 100 | | 2026 | US | 200 | | 2027 | US | 250 | | 2029 | US | 300 | | 2030 | US | 400 | **Formula:** `CAGR('Revenue', 2)` **Equivalent to:** `CAGR('Revenue', 2, "Year")` **Equivalent to:** `CAGR('Revenue', 2, "Year", "FailOnInvalid")` | Year | Country | CAGR Result | | ---- | ------- | ----------- | | 2027 | DE | 73.21% | | 2028 | DE | 41.42% | | 2029 | DE | 29.10% | | 2030 | DE | 22.47% | | 2027 | US | 58.11% | | 2029 | US | 9.54% | There is no 2028 value for the US in the original data. This means the CAGR value cannot be calculated for 2028 (no end value) and 2030 (no start value 2 years prior). ### CAGR along a non-time dimension This example computes CAGR along the Product dimension instead of time, comparing each product to its predecessor in the dimension hierarchy. **Dimension: Product** | Level Name | Level Key | | ---------- | --------- | | Product A | 1 | | Product B | 2 | | Product C | 3 | **Input node:** `'Revenue'` | Year | Product | Value | | ---- | --------- | ----- | | 2025 | Product A | 100 | | 2025 | Product B | 250 | | 2025 | Product C | 200 | | 2026 | Product A | 150 | | 2026 | Product B | 250 | | 2026 | Product C | 250 | | 2027 | Product A | 200 | | 2027 | Product C | 300 | **Formula:** `CAGR('Revenue', 1, "Product")` | Year | Product | CAGR Result | | ---- | --------- | ----------- | | 2025 | Product B | 150% | | 2025 | Product C | -20% | | 2026 | Product B | 66.67% | | 2026 | Product C | 0.00% | Product A has no prior level in the dimension hierarchy, so no CAGR can be calculated. Product B and Product C both have prior levels. ## Related Functions | Function | When to use instead | | ------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | [YOY\_REL](/yoy-rel/) | When you only need growth compared to one period back (no compounding). CAGR smooths growth over N periods; YOY\_REL gives the raw single-period change. | | [YOY\_ABS](/yoy-abs/) | When you need the absolute difference to the previous year, not a percentage growth rate. | | [DELTA\_REL](/delta-rel/) | When you need relative change along any dimension (not just time), without compounding. |