[FIXED] Numpy Rate function on large dataset returning nulls

Issue

I am using the numpy rate function in order to mimic the Excel Rate function on loans.

The function returns the correct result when working with a subset of my dataframe (1 million records).

However, when working with the entire dataframe (over 10 million records), it returns null results for all.

Could this be a memory issue? If that is the case, how can it be solved? I have already tried to chunk the data and use a while/for loop to calculate, but this didn't solve the problem.

This worked (not when I looped through the 10 million records though):

test = df2.iloc[:1000000,:]
test = test.loc[:,['LoanTerm',Instalment,'LoanAmount']]
test['True_Effective_Rate'] = ((1+np.rate(test['LoanTerm'],-test['Instalment'],test['LoanAmount'],0))**12-1)*100

I am trying to get this to work:

df2['True_Effective_Rate'] = ((1+np.rate(df2['LoanTerm'],-df2['Instalment'],df2['LoanAmount'],0))**12-1)*100

I see a similar question has been asked in the past where all the values returned are nulls when one of the parameter inputs are incorrect.

Using numpy.rate, on numpy array returns nan's unexpectedly

My dataframe doesn't have 0 values though. How can I prevent this from happening?

Solution

You can use apply to calculate this value once per row, so only invalid rows will be nan, not the entire result.

import pandas as pd
import numpy_financial as npf # i get a warning using np.rate
i = {
 'LoanAmount': [5_000,20_000,15_000, 50_000.0, 14_000,1_000_000,10_000],
 'LoanTerm': [72, 12,60, 36,72,12,-1],
 'Instalment': [336.0,5000.0,333.0,0.0,-10,1000.0,20],}
df = pd.DataFrame(i)
df.apply(lambda x: npf.rate(nper=x.LoanTerm,pv=x.LoanAmount,pmt=-1*x.Instalment,fv=0),axis=1)

This will be slower for large datasets since you cannot take advantage of vectorisation.

You can also filter your dataframe entries to be only the valid values. It is hard to reproduce what is invalid, since you are not sharing the inputs but in my example above both loan term and installment must be >0.

valid = df.loc[(df.Installment > 0) & (df.LoanTerm > 0)]
npf.rate(nper=valid.LoanTerm,pv=valid.LoanAmount,pmt=-1*valid.Installment,fv=0)

Answered By - oli5679