Time-Series: Taking Last Record And Calculating CAGR X Periods Back

[joe_de_foleschampe]

Hi,

I have data that is refreshed every quarter. So there is a new RecordID added every quarter along with index data when our db refreshes every three months.

What I am trying to do is take the most recent (last) data point of the index and go back 15 years or 61 periods to calculate a CAGR (compounded annual growth rate) like the the Excel example which is for 15-years (2019.4 versus 2014.4).

So, I need to be able to do this when the next data set comes in, our db is refreshed and a new RecordID is created for example 2020.1 versus 2015.1.

I have tried Multi-Row and Sample (last), but not getting the results in the attached Excel where I have provided the exponential CAGR calc I am trying to achieve. 

Thanks for any help/ideas! 

[fmvizcaino]

Hi @joe_de_foleschampe ,

I was thinking about your solution and I thought give you something completer that maybe you don't need at all lol

But it does the job!

Best,

Fernando Vizcaino

[danilang · Accepted solution]

Hi @joe_de_foleschampe 

Good solution from @fmvizcaino,  Here are 2 more ways to tackle this

The top branch uses a multi-row tool, configured to look 60 rows back with (Pow(([Index Value]/[Row-60:Index Value]),1/15)-1)*100.  The bottom branch creates a new [Index Period] column with a start value of -59 and performs a self join on [Index Period]=[RecordID] and a similar formula.

The final few rows of output look like this.

Dan

[joe_de_foleschampe]

Excellent! Thank you so much!

[joe_de_foleschampe]

Hi,

Yes, thank you and @fmvizcaino for your solutions! 

I see where all of the solutions can help me crack this and other time-series challenges!

Thanks so much and really appreciate all of your insight and help!

Have a nice day!

[joe_de_foleschampe]

Also, thanks for the syntax on the CAGR calc with Pow, which is helping me with Excel <> Alteryx conversion of functions/arguments!