How to calculate interest based on conditions and add cumulatively to following rows balance and interest

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/365) 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:

Final Result:

I don't see an easy way to fully vectorise this. There's too much non-trivial row-to-row dependence for the actual interest calculation. That said, you can pre-calculate some vectorised quantities like the effective accruable rate:

import functools
import io
import locale
import typing

import pandas as pd


def load(f: typing.TextIO) -> pd.DataFrame:
    df = pd.read_csv(f, parse_dates=['date'])
    return df.fillna({'fee': 0, 'payment': 0})  # Use 0, not blanks


def accruable_rate(df: pd.DataFrame, yearly_rate: float = 0.20) -> pd.Series:
    one_day = pd.Timedelta(1, unit='D')
    this_year_start = df['date'].dt.to_period('Y').dt.start_time
    next_year_start = this_year_start + pd.DateOffset(years=1)
    year_days = (next_year_start - this_year_start)/one_day
    daily_rate = yearly_rate/year_days  # Daily interest compounded monthly

    # Date of the most recent fee, today if today is a fee day
    fee_date = (
        df.loc[df['fee'] > 0, 'date']
        .reindex(df.index, method='ffill')
    )

    # Fraction of overdue balance to charge as interest; roughly: monthly interest
    return (
        fee_date.diff().fillna(pd.Timedelta(0))
    )/one_day * daily_rate


def accrue(df: pd.DataFrame) -> pd.DataFrame:
    cround = functools.partial(round, ndigits=locale.localeconv()['frac_digits'])
    balance = 0

    def actor(row: pd.Series) -> pd.Series:
        nonlocal balance
        # If a positive balance has been carried forward, apply interest on the number
        # of days since the last payment day (0 if this is not also a payment day).
        interest = cround(row['accruable_rate'] * max(0, balance))
        balance = cround(balance + row['fee'] - row['payment'] + interest)
        return pd.Series([interest, balance])

    return df.apply(actor, axis='columns')


def demo(monetary_locale: str = '') -> None:
    locale.setlocale(category=locale.LC_MONETARY, locale=monetary_locale)

    with io.StringIO(
    '''date,fee,payment
2025-01-01,200,
2025-01-03,,200
2025-02-01,200,
2025-03-01,200,
2025-03-03,,200
2025-04-01,200,
2025-04-03,,403.50
''') as f:
        df = load(f)

    df['accruable_rate'] = accruable_rate(df)
    df[['interest', 'balance']] = accrue(df)

    print(df.to_string(
        formatters={
            'fee': locale.currency,
            'payment': locale.currency,
            'interest': locale.currency,
            'balance': locale.currency,
            'accruable_rate': '{:.2%}'.format,
        },
    ))


if __name__ == '__main__':
    demo()

        date     fee payment accruable_rate interest balance
0 2025-01-01 $200.00   $0.00          0.00%    $0.00 $200.00
1 2025-01-03   $0.00 $200.00          0.00%    $0.00   $0.00
2 2025-02-01 $200.00   $0.00          1.70%    $0.00 $200.00
3 2025-03-01 $200.00   $0.00          1.53%    $3.07 $403.07
4 2025-03-03   $0.00 $200.00          0.00%    $0.00 $203.07
5 2025-04-01 $200.00   $0.00          1.70%    $3.45 $406.52
6 2025-04-03   $0.00 $403.50          0.00%    $0.00   $3.02