Calculate Variable Interest per Customer

[AW25]

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!

[danilang · Accepted 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

[AW25]

That's perfect Dan! It looks like the Generate Rows and Multi Row Formula tools are the key in this solution. Thank you!

[Lucky1852]

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! 

[danilang]

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

[Lucky1852]

Thank you for the help, this formula worked perfectly for me.