Computing CAGR on every field

[Mwatts23]

Hi,

I hope someone can help. I want to compute the CAGR per year for the following fields: Region, Company, Country and Segment.

I want my based year in 2021 that I want to compute for 5 years.

Example: Beginning Value $100 (yr 2021) / End Value $150 (yr 2022)

                Beginning Value $100 (yr 2021) / End Value $175 (yr 2023)

                Beginning Value $100 (yr 2021) / End Value $225 (yr 2024) and so on...

I find it very challenging on how this process works.  Please let me know if you need additional information.

Best regards,

Michael

[KilianL · Accepted solution]

Hi @Mwatts23 ,

that was a nice little challenge.

I used the CAGR formula from here: https://www.investopedia.com/terms/c/cagr.asp

Steps taken:

1. aggregate by field of interest, e.g. company

2. pick base value

3. add base value to table

4. get number of years (n)

5. calculate CAGR following the formula

I added an example how to do this dynamically for any number of fields, see attached.

Please mark this as the solution if it answers your question, it will help others to find solutions quicker.

Kind Regards,
Kilian
Solutions Engineer - Alteryx

[Mwatts23]

Hi @KilianL,

Thank you so much! this community is just amazing as always! the CAGR is what I expected to be.

Now, if I want to join it back to the main data, do I just need to transpose the Name and Value fields before joining?

This community is fun and full of mentors. Thank you @KilianL .

Best regards,

Michael

[KilianL]

Hi @Mwatts23 ,

joining it back to the main data does not work out, because I aggregated the data by field for the CAGR calculation. This was an assumption I made. What is the desired output you want to see?

I quickly added a calculation without any aggregation, but the data does not make sense to me in that case. Some years are missing and some values are duplicated.

For example:

AMER A Brazil Enterprise 2021 12
AMER A Brazil Enterprise 2021 13

[Mwatts23]

Hi @KilianL ,

You are right. I just thought if there is a way to join it back to the main file 😅. Sorry, but I have another question in relation to this data.

From the above screenshot, how can you insert a new profit for 2023, 2024 and 2025 for each region using this

calculation for example in AMER Region.

2023 profit:  (1,197 * 1.25) + 1197 = 2,693

2024 profit:  (2,693 * .87) + 2693 = 5,036

2025 profit:  (5,036 * .56) + 5036 = 7,856

Thank you for taking time on my queries.

Best regards,

Michael

[KilianL · Accepted solution]

Hi @Mwatts23 ,

to include values from other rows in your calculation, you can use the multi-row formula tool.

Given your examples, this might look like: ([Row-1:Profit] * [Row-1:CAGR]) + [Row-1:Profit]

[Mwatts23]

Thank you @KilianL  for this information.