Need help calculating penalty on overdue interest amounts (excel example attached)

[AkisM]

To cut the long story short you may refer to the sample excel I'm attaching (it contains all input data + 1 additional column highlighted as green, which is the column I am trying to calculate, and I manually entered the values of that column using excel formulas to give you an idea of the calculation that needs to be done).

However, here is a written explanation as well in hopes that it helps you understand my problem better:

A loan is granted to a company. At the end of every quarter, all interest accrued must be repaid. If not, then a penalty is calculated on the unpaid interest. So if there is unpaid interest from the first quarter it will be penalized on the second quarter, then again on the third quarter along with any new unpaid interests that occurred in the 2nd, then again on the fourth quarter along with any new unpaid interests that occurred in the 3rd, etc. For simplicity, in my example all interest is always unpaid, so there is always a calculation at the end of each quarter.

As you can see the formula gets bigger and bigger for each new quarter, so I'm not sure how to express it in alteryx. I imagine it would need some sort of iterative macro, but I'm not very familiar with them unfortunately.

Would appreciate any assistance. All columns in the excel except the last one (calculated column), are given.

[Christina_H · Accepted solution]

Based on the data you've provided, this gives the correct interest calculation for each quarter.  It doesn't need a macro, I used an append and filter to match up each quarter with all earlier quarters.

The only difference to your Excel formula is that I've used the max To date for each quarter plus one day, which from what you've provided is always the same as the min From date from the following quarter.

[AkisM]

Works perfectly fine @Christina_H , thank you. Don't know why I didn't think of the append solution to create all necessary date pairs.