Calculating Amount on the basis of Tiered Rate

[Gandalf_NotGrey]

Hello, I have a dataset which looks like this - (Excel also attached)

I want to calculate Fees based on rates in columns C, D, and E, on the Amount in column B.

For example - first amount is 1,487,668,829.34

In this, I will calculate the fees as below - 

For first 250,000,000 --> 250,000,000*0.00019 = 47500

Next 250,000,000 to 1,000,000,000 --> 750,000,000*0.000116 = 87000

Remaining amount of (1,487,668,829.34 - 1,000,000,000)*0.000113 = 55106.57

Total Fees = 189,606.57

And this amount is more than the Min Fee amount in Row 1 of 1250, so we are good with this.

How can I build this in Alteryx to calculate the Fees for each value in the Amount column?

[aatalai · Accepted solution]

@Gandalf_NotGrey I did it with a formula tool using the max of minim fee and the recalculated amount

Max([Minimum Fee],

IIF([Amount]>250000000, 250000000*[0 to 250m], [Amount]*[0 to 250m])

+ IF [Amount]<250000000 then 0 
elseif [Amount]<1000000000 then ([Amount]- 250000000)*[250m to 1bn]

else (1000000000 - 250000000)*[250m to 1bn] endif

+ 

IIF([Amount]<1000000000, 0, ([Amount]-1000000000)*[1bn and above]))

Please let me know how you get on

[Gandalf_NotGrey]

Thank You @aatalai for your response. I will have a look and let you know if this works. Much Appreciate your time and effort on this.

[Gandalf_NotGrey]

Thanks so much @aatalai for sharing this, worked perfectly. Appreciate it.

[aatalai]

@Gandalf_NotGrey glad to hear it