[Python] Pandas - how to calculate interest based on conditions and add cumulatively to...

I am trying to develop an account summary table based on charges and monthly payments. The condition is that whenever a payment is paid over 7 days late (or not at all), it triggers an interest calculation based on that row. This interest value is then added to the amount owed in the next row, which - if not cleared - will then affect the calculation of interest in each row following.

The interest is calculated on the balance owed amount after the payment month using the following formula of balance owed x ((0.2/360) x days in previous month). And will continue to be charged until the balance is cleared in full.

The dataset starts with a list of fees and list of payments for that month and will be updated over time. However, I can't figure out how to get these calculations to apply on a row by row basis.

I have tried first to calculate balance owed by using the formula

df['balance owed'] = (fees-payments).cumsum()


If the payment is late (after 7 days) and clears the balance, the previous monthly fee will be used instead for the interest calculation.

Then for the interest I tried using shift to fill the blanks, however this only works on existing values and the balance owed never gets updated to include the interest. Also, I'm not sure what would happen if someone had multiple payments in the month as I think that would throw off using shift to calculate the interest.

df['Interest'] = np.where((df['Balance owed'] > 200), (df['Balance Owed'].shift(1)*((0.2/365)*df.month.shift(1))), 0)


Starting:

date description fee payment balance owed interest comment month
1-Jan Fee 200 200 31
3-Jan Payment 200 0 31
1-Feb Fee 200 200 28
1-Mar Fee 200 400 31
3-Mar Payment 200 200 31
1-Apr Fee 200 400 30

Final Result:

date description fee payment balance owed interest comment month
1-Jan Fee 200 200 31
3-Jan Payment 200 0 31
1-Feb Fee 200 200 28
1-Mar Fee 200 400 3.07 Interest for February 31
3-Mar Payment 200 206.17 31
1-Apr Fee 200 400 3.50 Interest for March 30
3-Apr Payment 403.5 0 30

Any help would be appreciated.

Continue reading...