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.

[apathetichell]

Sheet 3 is incorrect - right? P4 should be 10 and p5 should be 10.55 - assuming that's correct this is pretty straight forward and I'll upload something in a few minutes.

[CoG]

Do you need anything beyond Calculation 3 (i.e. are you just appending each effective rate to the corresponding row from input 1)?

[apathetichell]

I got  0.1093539191119 - for the continuously compounding rate.

[CoG]

Here is another workflow with a macro that cycles through the Input 1 sheet and calculates the effective rate for all rows.

[ConnorM]

Is your Summarize tool using a Product?

Got an error when opening about having an earlier version than you and the Prod was not recognized but when clicking into the tool I don't/can't see a product taking place.

[ConnorM]

I'll be using the calculated effective rate for downstream calculations however those will be straightforward.

Ideally it would be appended to each respective row.

[CoG]

Oh... Yes it was, that's an easy fix!

[ConnorM]

Yes I believe you're correct, apologies for the rates being off by 1D as I quickly compiled this sample data.

Either way shifting the rates by 1D is fine as I'm looking for the concept of how to accomplish this and can adjust inputs to replicate.

However, where in your workflow are you generating the 0.109353? I'm not seeing it in the ending browse tool.

Lastly, preferably these would by 1D compounding with no current use cases of mine utilizing continuous compounding.

[apathetichell]

I use a summarize tool - but I don't need to summarize product. I take the logs of my periodic rates - add them and then take the exponent to get the continuously compounding rate. I add the LNs and get 

0.00663

 and if I take the exponent of that I can get:

1.00665