Interest rate calculations

[mtbc]

I was thinking how tricky interest rate calculations might be for institutions. One typically wants to quote an annual percentage but give interest more often than that. In terms of how the payments add up to the annual total, we certainly don't want that sum to be less than the quoted percentage but we would also rather it be as low as possible. Yet, when we pay interest, it's not like we pay exactly a twelfth root or whatever, or probably even calculate roots as precisely as we could, and we somehow round each payment to probably just a couple of places depending on the currency we're using. Further, absent withdrawals, the interest payments increase steadily due to compounding. In practice, I wonder how institutions deal with such payment calculations where each is rounded yet what matters is the sum of the payments.

Comments

[armiphlage]

In Canada, it's mandatory that all interest rates be presented as an annual rate. So, "12% per annum, compounded monthly on the first of the month".

https://www.canada.ca/en/financial-consumer-agency/services/industry/commissioner-decisions/decision-55719-512Q304.html

[mtbc]

Mmm, that's exactly the kind of thing I wonder how they handle. E.g., if everything's rounded somehow to cents, and I have $1.00 and after a year expect to have $1.12, then is the bank going to give me 1¢ after the first month even though that reflects a rate of more than 12% if compound rather than simple interest? I suppose, maybe, but they'd also like to give me as little as possible overall.

[thewayne]

Interest and dates are tricky things, especially when programmers don't use standard libraries and think they can do better themselves.

I ran into a truly astounding situation a year or so ago. Someone I know who has a masters degree in teaching maths was trying to create their own formula for calculating monthly interest rates in Excel, and it wasn't quite right. They wanted me to look at it, and the numbers were truly wonky. I dig into the formula, and he was applying the APR MONTHLY! NOT dividing it by 12, as you would for a basic monthly interest rate calculation.

And this dude has a masters in teaching maths? Not to mention an extensive background in computers?!

Some people....

[armiphlage]

It's stored as floating point; they round to two decimal places when displaying on a screen, or when transferring between accounts or businesses. When paying out as cash, it is rounded to the nearest 5 cents:

https://www.canada.ca/en/revenue-agency/programs/about-canada-revenue-agency-cra/phasing-penny.html

[mtbc]

If rounded fairly conventionally each month then if I have 50¢ and earn monthly interest then a 12% annual rate leaves me with still 50¢ at the end of the year. 60¢ would get me up to 72¢ at the end of the year, effectively a sweet 20%. Perhaps that's actually what happens, hmm.

Nice they're getting rid of the tiny change for cash though.

[mtbc]

in Excel was the first red flag there. (-:

Recently I had one of my minions take some of our handwritten crypto code and replace it with having a popular library do the operation instead, another thing it's too easy to screw up. I suggested the route of: add tests to the existing code, see if they still pass with the new code.

[thewayne]

Hoboy!  Hand-written crypto!  DEFINITELY not a good idea!  Excel is fine for general purpose stuff, but when people rely on it for serious stuff, trouble can ensue.  Like when messing with genetics, or when 15 and 15.0 are different.