CODEWITHSUNDEEP
pythonpandasfor-loopamortization
user11144539
user11144539

Reputation: 23

Is there a better way to create a loan amortization table in Python?

I'm trying to create a simple amortization of a loan. For some reason, my code stops working after a few periods. Starting on period 2006-04-30, my code is not recognizing the beginning balance and is not calculating interest on this balance nor is it calculating an ending balance. Any suggestions are greatly appreciated.

import numpy as np
import pandas as pd
from datetime import datetime

data = pd.DataFrame({'date': pd.date_range("2006-01-01", "2011-12-31", freq='M')})
data['date'] = pd.to_datetime(data['date'])
data.set_index(data['date'], inplace=True)

orig_balance = 1000000
interest_rate = .06
day_count = 30/360
data['monthly_principal_pmt'] = 10000
date_cut_off = '2006-01-31'

data['beginning_balance']  = 0

for row in data:
    
    data.at[date_cut_off,'beginning_balance'] = orig_balance
    
    data['ending_balance'] = np.maximum(data['beginning_balance'] - data['monthly_principal_pmt'],0)
    
    data['monthly_interest_pmt'] = data['beginning_balance'] * day_count * interest_rate 
    
    data['beginning_balance'] = data['ending_balance'].shift()

Upvotes: 0

Views: 469

Answers (1)

user11144539
user11144539

Reputation: 23

Thank you for the help! this now works:

import numpy as np
import pandas as pd
from datetime import datetime

data = pd.DataFrame({'date': pd.date_range("2006-01-01", "2011-12-31", freq='M')})
data['date'] = pd.to_datetime(data['date'])
data.set_index(data['date'], inplace=True)

orig_balance = 1000000
interest_rate = .06
day_count = 30/360
data['monthly_principal_pmt'] = 10000
date_cut_off = '2006-01-31'

data['beginning_balance']  = 0

for yr in range(1,72):
    
    data.at[date_cut_off,'beginning_balance'] = orig_balance
    
    data['ending_balance'] = np.maximum(data['beginning_balance'] - data['monthly_principal_pmt'],0)
    #data['ending_balance'] = data['beginning_balance'] - data['monthly_principal_pmt']

    
    
    data['monthly_interest_pmt'] = data['beginning_balance'] * day_count * interest_rate 
    
    data['beginning_balance'] = data['ending_balance'].shift()

Upvotes: 0

Related Questions