Posted on by Bret Whissel

More on rate-finding

In responding to a question in blog comments about rate-finding, I noticed that the calculator didn’t behave well if the Payment Amount could not pay off the loan after the Number of Regular Payments had elapsed, no matter how low the interest rate. To fix this, I added a test for the condition and have the calculator write an error message if necessary.

It’s a little surprising to still find bugs of this magnitude after all these years. 🙂 But hey, they’re still getting fixed, one by one.

6 Replies to “More on rate-finding”

  2. Great calculator. I use it all the time. I think its the best one out there that I’ve seen and I’ve searched a lot. The only thing missing is being able to set on what period it calculates. For example, 5.5% interest calculated semi-annually. Or 5.5% interest calculated monthly.

    It would be fantastic if you added that.

    Vg

  3. OK, I’ll send you an e-mail.

    I’ve never done a balloon loan, so I don’t have any personal experience that says the balloon should be at N+1. But if it’s at N instead, then the amortization should really figured for N-1 payments, since the balloon is typically a fixed amount.

    If the balloon amount is paid at the same time as the final amortization payment, that’s the same thing as amortizing to N-1 with a balloon of B+x (in my nomenclature).

  4. I’m not mathematician either but I couldn’t explain it the way you did. I think you under value yourself 😉 Yes, you are right the first part of your equation is exactly the same as the standard amortization formula, it’s just the balloon that is different. Your balloon method solves correctly when the balloon payment is paid in the month following the last payment. When the balloon is paid in the same month the balloon formula changes slightly and is the method most calculators use. I was trying to understand why it doesn’t work for advance payments. Can you email off the blog? Promise I won’t make you do any hard math. Just want to share some ideas and methods the bank uses for structured payments….

  5. First off, as I say in the FAQ and other places, I am not a finance person. I don’t know what you mean by the “straight line method”.

    The formula I use was one that I derived from basic principals, and so it may not be the “standard” formula used by financial institutions. (In the mid-80s, I could not locate a reference that would give me the formula, so I did the derivation myself.) However, the equation has served well over many years, and seems to have close agreement with the loan transactions in the USA that I’ve personally encountered.

    But in fact, after looking at a few on-line resources, the equation that I’ve derived *is* the standard amortization formula (if you leave out the balloon payment terms).

    From what I’ve read over the years, here are some ways that this calculator may deviate from actual practice:

    • For monthly payments, my calculator works on the equivalent to a 30/360 basis. Some institutions may use an actual/365 or actual/actual basis.
    • The amortization formula used by the calculator assumes that the number of compounding periods and the number of payment periods annually are the same. (In contrast, Canadian mortgages, for example, are only compounded twice annually, I think.)
    • There are several “reasonable” approaches to rounding payments to the nearest penny. The calculator’s current method applies a strict rounding so that the payment is the exactly the same every payment period. The accumulated error is addressed by the last payment in the schedule. Other approaches may distribute rounding error more evenly throughout the schedule.

    The bottom line is that my calculator works as it does because I never knew how financial institutions performed the same task. I had to make guesses that seemed reasonable from what information was available to me at the time.

    If I haven’t adequately responded to your comment, please follow up.

  6. Hi Bret,

    I found your calculator while searching for a solution to a problem that I had with a similar type calculator I wrote for a bank. Your calculator was one of a few that gave the same result the bank was after so I explored your formula but don’t quite understand why you use this formula. For example the payment you get is different from the straight line method used in Excel or most financial calculators, or even for that matter the basic amortisation formula. However you get the same result that my bank gets with their inhouse software but only for one case, that is, monthly payments in arrears with a balloon paid in the month n+1. All other finance options, the bank uses the straight line method so I am trying to understand why they deviate from it for only one type of loan. I thought maybe you could shed some light on why you chose that particular method and not a standard amortization formula?

Leave a Reply