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

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!

Sample Index Time-Series.xlsx

Time Series

Accepted answers

danilang

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.