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!

2018-11-30 VariableInterestRates.yxzp

danilang

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

VariableInterestRates_Solution.yxmd