Search code examples
pythonpandaseconomics

Pandas capitalization of compound interests

I am writing an emulation of a bank deposit account in pandas. I got stuck with Compound interest (It is the result of reinvesting interest, so that interest in the next period is then earned on the principal sum plus previously accumulated interest.)

So far I have the following code: 

import pandas as pd
from pandas.tseries.offsets import MonthEnd
from datetime import datetime

# Create a date range
start = '21/11/2017'
now = datetime.now()
date_rng = pd.date_range(start=start, end=now, freq='d')

# Create an example data frame with the timestamp data
df = pd.DataFrame(date_rng, columns=['Date'])

# Add column (EndOfMonth) - shows the last day of the current month
df['LastDayOfMonth'] = pd.to_datetime(df['Date']) + MonthEnd(0)

# Add columns for interest, Sasha, Artem, Total, Description
df['Debit'] = 0
df['Credit'] = 0
df['Total'] = 0
df['Description'] = ''

# Iterate through the DataFrame to set "IsItLastDay" value
for i in df:
    df['IsItLastDay'] = (df['LastDayOfMonth'] == df['Date'])

# Add the transaction of the first deposit
df.loc[df.Date == '2017-11-21', ['Debit', 'Description']] = 10000, "First deposit"

# Calculate the principal sum (It the summ of all deposits minus all withdrows plus all compaund interests)
df['Total'] = (df.Debit - df.Credit).cumsum()

# Calculate interest per day and Cumulative interest
# 11% is the interest rate per year
df['InterestPerDay'] = (df['Total'] * 0.11) / 365
df['InterestCumulative'] = ((df['Total'] * 0.11) / 365).cumsum()

# Change the order of columns
df = df[['Date', 'LastDayOfMonth', 'IsItLastDay', 'InterestPerDay', 'InterestCumulative', 'Debit', 'Credit', 'Total', 'Description']]

df.to_excel("results.xlsx")

The output file looks fine, but I need the following:

  1. The "InterestCumulative" column adds to the "Total" column at the last day of each months (compounding the interests)
  2. At the beggining of each month the "InterestCumulative" column should be cleared (Because the interest were added to the Principal sum).

enter image description here

How can I do this?

Solution

  • You're going to need to loop, as your total changes depending on previous rows, which then affects the later rows. As a result your current interest calculations are wrong.

    total = 0
cumulative_interest = 0

total_per_day = []
interest_per_day = []
cumulative_per_day = []
for day in df.itertuples():
    total += day.Debit - day.Credit
    interest = total * 0.11 / 365
    cumulative_interest += interest

    if day.IsItLastDay:
        total += cumulative_interest

    total_per_day.append(total)
    interest_per_day.append(interest)
    cumulative_per_day.append(cumulative_interest)

    if day.IsItLastDay:
        cumulative_interest = 0

df.Total = total_per_day
df.InterestPerDay = interest_per_day
df.InterestCumulative = cumulative_per_day

    This is unfortunately a lot more confusing looking, but that's what happens when values depend on previous values. Depending on your exact requirements there may be nice ways to simplify this using math, but otherwise this is what you've got.

    I've written this directly into stackoverflow so it may not be perfect.