Questions

If you have a question about the calculator or about amortization, attach a comment to this page. But please, be sure you’ve checked the FAQ Page first. (You’ll need to create an account to post a comment.)

199 Replies to “Questions”

  1. Bret:

    Great work here. I have a question about how to build an amortization calculator for the following:

    Promissory Note dated December 1, 2012, repaid in 10 equal quarterly payments, beginning on August 31, 2013.

    The typical amortization calculator doesn’t include the interest going back to December 1, 2012.

    Thanks in advance.

    1. Hi, Osborns. The calculator will not handle this loan directly. However, if you calculate interest manually during the moratorium period, you can use the calculator to determine the rest of the payment schedule.

      The math for figuring out the interest during the moratorium period can be found here: finextra.pdf. Use equation 7a if the loan agreement calls for compound interest, or use 7b for simple interest. The new value (P’ in the equation) is what you would use to plug into the calculator as the amount of principal borrowed.

      Bret

  3. Greetings Bret
    My name is Chris Halliday. I have been asked by a friend to do some calculations that for all practical purposes replicate those that you have developed for your amortization website. My friend is developing a small data logging device which requires similar calculations/processing to be performed in its internal software. My friend originally wanted someone to determine a formula for calculating the interest rate but I have explained to him that (as you have said in one of your papers on your website) a numerical method/iterative process is required so a closed form/straight forward equation doesn’t exist.
    What he really requires is someone to write or to advise his current programmer of how to perform these calculations in the relevant programming language (I am assuming C/C++) As I have negligible programming knowledge I can’t help him but I was wondering if this is something that you may be interested in assisting with??? It’s a commercial undertaking/project so you would be paid for your work/input. Is this something that you may be interested in?

    Many thanks

    Regards

    Chris Halliday

    1. Hi, Chris. I wouldn’t have a clue how to bill for advice like this, and given the added complication of an international consultancy, I’m pretty sure I wouldn’t want to bother.

      However, the technique isn’t that complicated. It’s a root-finding method that goes something like this:

      1. Take one of the final equations—probably (6), dropping the B term if not required.
      2. Select an acceptable error tolerance (perhaps 1/10th of a cent).
      3. Make an initial guess for the periodic interest rate.
      4. Plug in all the numbers on the right-hand side and calculate.
      5. Subtract the result from the left-hand side.
      6. If the difference is within the tolerance, then stop.
      7. Otherwise, revise upwards the guess for the interest rate if RHS < LHS, or else revise the guess downwards.
      8. Go to Step 4.

      The solution curve has no local minima. In fact, it looks almost linear for most of it, so the only trick is finding a way to revise the guess which minimizes the number of iterations. This isn’t strictly necessary if computer time is cheap: just choose a small step size. Or employ a common method like Newton-Raphson. As I recall, I think I used some sort of secant method, which meant that I didn’t have to calculate the derivative of equation (6) with respect to i.

      If the programmer has further questions, have her or him write me directly.

      Bret

  4. Hi, styxdog. Interesting question that I don’t know how to answer directly. The first step will be to know what your current principal balance is, so fill in the calculator form with the original loan parameters, be sure to check the “Show Amortization Schedule” box, and look up the principal balance for the payment due in July.

    Now, assume you’re starting a new loan that has the same number of payments remaining as your previous loan, using the current loan balance. Set the new interest rate you’d like to try (e.g. 5%), clear the regular payment amount, and click “calculate”. Make note of the “Total Interest Paid” result. Since you are not really going to change the interest rate, you just want to get close to this number over the remainder of the loan, and you can only do this by reducing the length of time you’re paying.

    Now, restore the current interest rate, bump up the payment amount to some value larger than your current payment, clear the “number of regular payments” field, and click calculate. If the “total interest paid” calculates larger than the target, bump up the payment some more, clear the “number of regular payments” field, and try again. It shouldn’t take more than a few tries to get close to the target value.

    Good luck!

  5. Hi, lrauzon. Printing is a function of the web browser that you are using. On the calculator page, fill in the parameters, make sure to click “Show Amortization Schedule” before clicking the “calculate” button, and then use the browser’s print function (usually under the main menu somewhere) to print the page.

  6. Let’s assume I don’t want to go thru the hassle of a refinance since the last time was an experience to forget. First payment was 02/01/2003 for 30 years at 6.25% and the loan amount was $91,000. Next payment is 07/01/2012. If I decide to make an extra principle payment for the same amount for the rest of the loan, what amount would I have to add to say get an effective rate of say 5.00%, 4.75% etc. In other words I would be ‘self refinacing’ to get a lower rate commensurate with today’s low mortgage refinance rates.

  7. Thank you, thank you, Bret, for sharing your wisdom and knowledge. We used your site when we financed one child’s home from our savings, now we are trying to help another child. I got through the calculator/payment page, but cannot find how to created and print the schedule?? Sorry to be so dense.

  8. I’m traveling at the moment, David, so I can’t check on the state of certain files without access to my laptop. It will be a few weeks until I’m back at home. Meanwhile, try the download again: nothing should have changed. But if I find that something went kafluey, it will just have to wait until my return.

    Best wishes!

  9. Bret I just accessed the link below and for some reason the format of the worksheet seems to be different.

    http://www.bretwhissel.net/files/amort-wk.xls

    Last time I looked at this it had the nice data entry screen with the teal colored fields at the top starting with LOAN AND AMORTIZATION WORKSHEET. I belive it had 3 tabs across the top one for the initial DATA INPUT TAB (loan term and interest info, a second tab for the AMORTZATINO FUNCTION and a final tab for instructions and technical data. These three tabs seem to have vanished.

    Now the only thing to CLICK ON is the AMORTIZATION CALCULATOR link (which is web based amortize.html amortization data in put screen?

    Am I missing something what happened to the other Xcel tabs?

    This is very confusing did something happen to the original workbook and spreadsheet?

Leave a Reply