Dynamically Calculating Effective Rate

[ConnorM]

Hi All,

I'm trying to figure out if calculating an effective interest rate dynamically using daily resetting index is possible using Alteryx.

My goal is to recognize that if index is "A" in input 1 then reference only A Index's data from input 2, if "B" reference B's data and so on.

Additionally, in order to calculate the effective rate, I need to compound the daily rate of Index A for x days (x days can be calculated from input 1).

I've attached sample inputs 1&2 to give an idea of my problem. Of course, in reality both inputs have a far greater amount of data.

Sheet 3 in the attached provides an example as to how the effective rate is calculated if needed.

I've accomplished this in VBA, however, I'm unable to find a proper solution within Alteryx.

[CoG · Accepted solution]

Revised Version without new feature (Product from Summarize Tool)

[apathetichell]

You can throw this in the formula tool at the end and create a new field:

(exp([Sum_exponent])-1)*360/(datetimediff([End],[Start],"days"))*100

this showed up as 10.4122588854 in Alteyrx for me. In excel I got 

10.4122589%

[ConnorM]

Thank you for this, after adjusting my workflow around this macro I'm getting promising results in line with my current excel calculation.