1

I'm attempting to build a cash flow loan model table using Pandas. I've generated several of the fields I need such as Beginning Balance, Interest, Principal, Payment, Ending Balance - as shown below:

Beginning Balance Principal Payment Interest Ending Bal
50000.00 144.49 477.83 333.33 49855.51
49855.51 145.46 477.83 332.37 49710.05
49710.05 146.43 477.83 331.40 49563.63

Now I'm trying to generate new columns using some new data and existing columns such as a Net Outstanding Balance, Prepaid Principal, Charge-Off Principal, and a Scheduled Principle Received:

SMM = .0184
Default = .0059
Total_SMM_Loss = .975

cf_table.at[1,'Net Outstanding Balance'] = cf_table.at[1,'Beginning Balance']

cf_table['Scheduled Principle Received'] = cf_table['Principal'] * Total_SMM_Loss

cf_table['Prepaid Principal'] = cf_table['Net Outstanding Balance'] * SMM

cf_table['Charge-Off Principal'] = cf_table['Net Outstanding Balance'] * Default

cf_table.at[2:,'Net Outstanding Balance'] = cf_table['Net Outstanding Balance'] - cf_table['Scheduled Principle Received'] - cf_table['Prepaid Principal'] - cf_table['Charge-Off Principal']

For the Net Outstanding Balance column I'm setting the value of the first cell to 50,000 - the beginning balance of the loan. Next I'm creating the other columns, which some rely on the value of the Net Outstanding Balance.

For cell 2 going down in the Net Outstanding Column I'm trying to insert a new formula that will take the previous value of the Net Outstanding Balance into its calculation while subtracting the values from the Prepaid, Charge-Off and Scheduled Principle columns from the previous row into the formula.

However the following table is what I receive below when applying the code above:

Net Outstanding Balance Prepaid Charge-Off Scheduled Principle
50000.00 920.00 295.00 140.88
NaN NaN NaN 141.82
NaN NaN NaN 142.77
Matthew_H
  • 33
  • 4
  • 1
    Are you looking for [.shift()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.shift.html) and [.diff()](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.diff.html)? – BeRT2me Aug 11 '22 at 17:29
  • 1
    `pandas` shines at vectorized operations etc. Your process, as described, sounds very procedural. Usually, these kinds of compound-interest problems can be vectorized by working out the math and express all rows at once with a combination of `cumprod()` and other simple arithmetic. – Pierre D Aug 11 '22 at 17:35

1 Answers1

0

I've figured out what I was trying to accomplish in the above post. With some help from this post here I was able to put together this code to fill out the rest of this table:

cf_table1.loc[1, 'Net Outstanding Balance'] = 50000

for i in range(2, len(cf_table)):
    cf_table1.loc[i, 'Net Outstanding Balance'] = cf_table1.loc[i-1, 'Net Outstanding Balance'] - cf_table1.loc[i, 'Prepaid Principal'] - cf_table1.loc[i, 'Charge-Off Principal'] - cf_table1.loc[i, 'Scheduled Principle Received']
    cf_table1.loc[i-1, 'Prepaid Principal'] = cf_table1.loc[i-1, 'Net Outstanding Balance'] * SMM
    cf_table1.loc[i-1, 'Charge-Off Principal'] = cf_table1.loc[i-1, 'Net Outstanding Balance'] * Default

cf_table1.head(cf_table1. shape[0] -1)
Net Outstanding Balance Prepaid Charge-Off Scheduled Principle
50000.00 920.00 295.00 140.88
47320.25 895.05 287.00 141.82
46028.40 870.69 279.19 142.77
Matthew_H
  • 33
  • 4