Hello!
I have a list of customers with loan amounts and 2 dates -- a deposit and withdrawal date. For each one, I'm trying to calculate the total amount of interest for their loan amount between those 2 dates. What I'm finding difficult is that, the interest rate varies every day (LIBOR rate) so I think I need to calculate the interest for each day per customer and sum it up. I'm wondering if there's a good way to do this? I attached a workflow with sample input data.
Thank you!
Solved! Go to Solution.
Hi @AW25
You're correct in thinking that you have to calculate the interest daily and sum it up. This workflow does exactly that. First it generates a record [InterestDate] for each day between the deposit and withdrawal dates for each customer. Then it joins to your LIBOR table based on [InterestDate]. The multi-row formula calculates the amount of interest for the day. The Summation tool takes the last values for each customer and the last formula calculates the total interest.
Here are the assumptions that I made
1. Interest is compounded daily.
2. The interest rate in your LIBOR table is a yearly rate, expressed in percent
3. The interest rate is converted to a daily rate by dividing by 365. If you need to take leap years into account, you'll need to modify the multi-row formula
4. No interest is paid on the withdrawal day. If you need to change this, change the condition expression in Generate Rows tool
Dan
That's perfect Dan! It looks like the Generate Rows and Multi Row Formula tools are the key in this solution. Thank you!
Hello, I am using this solution but I am needing help in the multi-row formula to account for leap years. Can you help me correct my formula to account for that? Thanks in advance!
Hi @Lucky1852
After the join, add a formula tool with the following config
Here's the formula
if mod(DateTimeYear([Field1]),4) = 0 And mod(DateTimeYear([Field1]),400) != 0 then 366 else 365 endif
change the Multirow formula to this
if isnull([Row-1:EndAmount]) then [Amount]+[Amount]*[USD1MTD156N]/[DaysinYear]/100 else [Row-1:EndAmount]+([Row-1:EndAmount]*[USD1MTD156N]/[DaysinYear]/100) endif
Dan
Thank you for the help, this formula worked perfectly for me.