PPMT not behaving as expected

[VikkioKPMG]

Hello,

My apologies if this isn't the right place to post, I am a simple user rather than a developer. I just couldn't find a more appropriate location for this issue.

I have been using ppmt to calculate the amortisation schedule on a portfolio of loans, however the output from the function is incorrect. An extremely simple example with constants rather than variables:

npf.ppmt(0.1479, 297, 300, -270.51)

or

npf.ppmt(rate=0.1479, per=297, nper=300, pv=-270.51)

Returns:

2463.202029

This is obviously incorrect - how can a loan of 270 dollars pay a principal of 2463 dollars in just one period?

For reference, the correct output (obtained through Excel and a Texas Instruments financial calculator) is 24.03.

Thank you.

[garfieldthecat]

In your example, are the periods monthly, annual or what?
Your formula would be correct for a 300-year loan charging 14.79% per year - but I can't think of many 300-year loans!
if instead the loan is over 300 months, then your formula implies the loans charge 14.79% per month, ie almost 180% per year, which doesn't sound right, either!
If you have a 300-month loan charging 12% per year, then the rate needs to be 1%, etc.

Anyway, even if you probably used the formula incorrectly, the formula still returns an incorrect result - Excel returns 23.04. In fact, testing functions in extreme scenarios, even in those which don't make a lot of sense from a practical standpoint, is key.

If you look at the code: https://github.com/numpy/numpy-financial/blob/master/numpy_financial/_financial.py you'll see, on line #477, that the ppmt() function is very banal: it calculates the total instalment for the loan (which remains constant throughout) and then subtracts the result of the ipmt() function so the error is there, rather than in ppmt(). ppmt(), in turn, calls _rbl(0 which calls fv(), defined on line 42, so I'd guess the error is in one of those functions. I don't have the time to look into the details now but I figured I'd point you in the right direction if you want to debug this.

[VikkioKPMG]

I did not apply the formula incorrectly. In my case, the periods are weeks and the interest rate is already quoted weekly.

In any case, as you have confirmed, the function returns an incorrect result, which is exactly the point of my original post.

As I mentioned, I am not a developer, but I will do my best to try and debug. Thanks!

[garfieldthecat]

I asked because, in my experience, it is a relatively common mistake (see eg this other issue https://www.iso.org/iso-8601-date-and-time-format.html ).

Wow, almost 15% per week! 769% per year!!! May I ask what this is? Payday loans are shorter than 300 weeks!

[VikkioKPMG]

They are a bunch of RTO loans. I didn't really bother double checking what example I used in my post as the portfolio has over 2m of them and I need to analyse them on an aggregate basis. The shape looked wrong so I just picked the first one and re-run the profile in Excel to double check I wasn't going crazy.

[garfieldthecat]

Practical advice, if I may: it will probably be easier and faster not to use ppmt and ipmt, but to use only pmt() to calculate the total instalment, then calculate interest and principal manually.
Also see here on how to make the pmt() function faster: #36
If you have millions of loans it can make a difference.

None of this directly solves the issue you correctly raised, however, but it may be a practical way around it.

[geoffwright240]

I was curious about this question so went hunting to see if I could find anything obvious that would lead to this incorrect result. This is just for discussion as my search has taken me into an area that I am unfamiliar with.

Does the error arise due to fixed point decimal precision?

I get a similar 'error' when I use other financial calculators so this is not just a numpy-financial issue.

When you go digging into the PPMT, IPMT functions you eventually get to a npf.fv call that seems to blow up when the numbers get large.

I have simplified the calculations from VikkioKPMG's original question so don't try to link it back to the exact numbers he/she had in the original question.

In the examples below, you take out a loan for $100, the periodic interest rate is 10% but there is a fixed PMT of $10, which is exactly equal to the interest charge.

If you work through the FV calculation by hand on a piece of paper, you will see that you repeatedly add the same two terms each period; that is, there is a stable analytical solution to the problem which is simply that the FV will be identically equal to PV. (Or, if you'd prefer, this is an interest-only loan so you need to pay the principal back at the end of the loan.)

That is exactly what you will see if you set the NPER in the equation below to anything up to about 300 (on my computer). After 300, things start to get wonky.

npf.fv(0.1,300,-10,100)
Out: -100.0

Take for example, NPER = 400. The function now returns FV is equal to 0.

npf.fv(0.1,400,-10,100)
Out: 0.0

As I say, you get the same problem in other financial calculators and when you examine the underlying calculations, you will find that there are some terms used in the underlying calculation that become very large as NPER goes to infinite (or 300+ in the case of my computer).

This all leads me to believe that those large numbers are getting truncated in some way and that is creating an instability in the underlying calculation, which we know should have a stable analytical solution.

As I say, I am not deep into the numerical precision of bits and bytes but my guess is that this has something to do with it so that is where I will look next unless someone more enlightened can point me in a different direction.

If this is the case, is there not a way to check that truncation has occurred and warn the user?

[garfieldthecat]

@geoffwright240 very good catch! Also worth following https://quant.stackexchange.com/questions/63877/future-value-the-functions-in-excel-and-numpy-financial-dont-work-when-number ; Excel has the same behaviour, too

[geoffwright240]

@garfieldthecat - thank you so much for helping to get this cleared up. That stackexchange discussion seems to confirm what I was thinking, although perhaps my terminology was off. Now that I know to be on the lookout for integer overflow, I have a lot of reading in front of me.

Having said all of that, I am surprised by the the wraparound behaviour in Julia and generally by the "silent" failure of Numpy when it encounters an overflow error. There is more for me to dig into here but it just seems sort of irresponsible to fail in this hard-to-detect way.

https://stackoverflow.com/questions/32014215/get-numpy-to-warn-on-integer-overflow

(I wonder what other sort of unexploded ordinance is lurking in all those massive spreadsheets floating around out there.)

[charris]

I am surprised by the the wraparound behaviour in Julia

Also NumPy and C, it is modular arithmetic. Some early computers (VAX) had hardware that would detect such things, but it seems to have gone away over the years. Detecting it in software would slow things down.

[geoffwright240]

This has all been very informative for me, thanks everyone!

I appreciate that software detection will slow things down but in some cases it might be better to be slow and correct than fast and incorrect.

I can take a closer look to see how to implement this but would it make sense to allow the user to enable warning similar to np.seterr(over='raise')? Any thoughts?

[Kai-Striega]

This hasn't been discussed in several years. I'm going to close this issue.