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