Calculating two mutually dependent Series in pandas

Viewed 36

I'm using pandas to track loan balances. The loan balance is affected by repayments or additional borrowing, and by interest (which is applied daily, on the previous day's balance).

So far, I've done this by using itertuples (example below). This works, but how can it be vectorised? The difficulty I'm having is that the interest applied for each day depends on the balance, but the balance depends on yesterday's balance and the interest applied yesterday.

How can I use .apply or something similar to calculate the two dependent Series of balance and interest at once?

Thanks!

import pandas as pd

# Set up a df with values of balance_change (repayments or additional borrowing)
# and rate (percentage annual interest rate on that day)
df = pd.DataFrame({"balance_change": [0, 100, 0, 0], "rate": [4, 4, 4, 3]})
df.index = pd.to_datetime(
    pd.Index(["2020-05-14", "2020-05-15", "2020-05-16", "2020-05-17"])
)

all_results = pd.DataFrame()
yesterday_balance = 0

for row in df.itertuples():
    # Convert annual percentage into daily ratio interest rate
    daily_interest_rate = (1 + (row.rate) / 100) ** (1 / 365) - 1

    # Calculate interest to be applied today, apply this and any repayments/additional borrowing
    interest_added_today = yesterday_balance * daily_interest_rate
    balance = yesterday_balance + row.balance_change + interest_added_today

    # Put results in a dataframe
    this_row = pd.DataFrame(
        {
            "date": [row.Index],
            "payments": row.balance_change,
            "annual_interest_rate": row.rate,
            "calculated_daily_interest": [interest_added_today],
            "calculated_balance": [balance],
        }
    )

    # Build up a larger DataFrame for all dats
    all_results = pd.concat([all_results, this_row])

    # Update yesterday_balance
    yesterday_balance = balance

0 Answers
Related