Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamically Calculating Effective Rate

ConnorM
7 - Meteor

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.

12 REPLIES 12
apathetichell
19 - Altair

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
14 - Magnetar

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

apathetichell
19 - Altair

I got  0.1093539191119 - for the continuously compounding rate.

CoG
14 - Magnetar

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

ConnorM
7 - Meteor

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
7 - Meteor

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
14 - Magnetar

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

ConnorM
7 - Meteor

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
19 - Altair

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

Labels
Top Solution Authors