I have a dataframe as shown below and I need to calculate interest for each observation and hence I used npf.rate() function from numpy_financial package.
annual_interest = dict({"Id" : [1, 2, 3, 4, 5, 6, 7],
"Period" : [24, 23, 18, 12, 36, 34, 60],
"EMI" : [0, 0, 0, 87548, 58405, 2969, 22877],
"Amount" : [2300, 38400, 31580, 80000, 46500, 68000, 1118691]})
df = pd.DataFrame(annual_interest)
df['int_py'] = df.apply(lambda x : (npf.rate(x['Period'], -x['EMI'], x['Amount'], 0))*12, axis =1)
## Excel code used: =RATE(Period, -EMI, AMOUNT)*12
df['exc'] = [-11.9642098, -11.9724795, -11.99528627, "#NUM!", "#NUM!", 0.293622, 0.083614]
df.head(10)
Here column = int_py is the one I created using npf.rate() and column = exc is the one I used RATE() function from Excel. Both of these function works same however giving completely different answer.
For last 2 observation it is working correctly however for 1st-5 observation I am facing this issue.
Is there something that I missed or I am doing wrong?