Dynamically Calculating Effective Rate

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.

Inputs.xlsx

Workflow

Macros

Batch Macro

Help

Dynamic Processing

Accepted answers

CoG

Revised Version without new feature (Product from Summarize Tool)

2023-11-13_15-09_Effective Rate.yxzp

All comments

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.

exponent math.yxzp

CoG

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

2023-11-13_15-09_Effective Rate.png

2023-11-13_15-09_Effective Rate.yxzp

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