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. Hi from Naples FL.

    Bret many congratulations on writing the Amortization Spreadsheet version 1.0 June 22, 2010 is the one I am using.

    Here is the situation. I have a loan that the borrowers are not repaying on a regular basis and there are many missed payments.

    If I put the payment amount missed as a negative in the EXTRA PAYMENT column or as a positive in the EXTRA FEES column will the calculator calculate the correct loan balances and interest considering the all too numerous missed payment history? There are lots of missed montly paymnents over a the 10 year amortization period. Which column is the best to put the missed payment in? I seem to perfer putting it in as a neagative in the Extra payment column because it zeros out the total payment clearly showing that the regulary schedule payment was not made.

    If your calculator will not do this is there one you have seen that or can recommend that does?

    Best Regards,
    David

    1. Hi, David. As you’ve noted, the spreadsheet was not really designed for keeping track of missed payments. Putting either a negative payment amount in the “extra payment” column, or a positive payment amount in “additional fees” column will work out mathematically the same. In both cases, the principal balance will increase by the amount of the unpaid interest due for that period, which is what typically happens. I would probably opt for the negative extra payment column, so that a late/missed payment fee could also be added to the additional fees column if appropriate.

      The problem comes at the end of the amortization schedule. The spreadsheet was designed to close out the amortization at the last scheduled payment date, no matter what the balance is. In order to extend the amortization period, you’ll have to unhide columns M and N on the amortization tab (I don’t remember how to do this off-hand with Excel: I’m not regular Excel user; you may also need to unprotect the sheet and workbook). At row 11, you’ll see a label in column M called “max rows”. Replace the formula in cell N11 with a value larger than the default (for 10 years of monthly payments, this value would be 120 by default, so choose a larger number: 130, 140?). This will let the spreadsheet roll the amortization out that many more periods. The spreadsheet will end the amortization when the principal balance reaches 0, so setting this number larger should not be a problem.

      I’m not sure I would trust the Fiscal Year interest calculation, however. If a payment is missed and the unpaid interest becomes part of the principal balance, is that still considered a (potentially deductible) interest payment? I don’t know the answer to that question, which may require the advice of an accountant or tax lawyer.

      Bret

    1. Hi, Eddie. Plug in the principal balance, interest rate, and number of annual payments. Assuming you already know what your monthly payment is, just add the extra payment amount to the regular monthly payment amount and plug that into the regular payment slot. Clear the “Number of Regular Payments” box and click calculate to see how many payments remain. See the FAQ for another example of how to solve this problem.

  3. Okay, now that I’ve thought about it, I think you’re right about the interest compounding. I was able to get the calculation to run using the RATE() function in excel or in google docs. Thanks for all your work, it has been really helpful.

  4. Hi! I use this calculator all the time to compare and contrast various ways to pay down debt.

    I’m easily able to see which method is more effective to pay down a debt, but now I’d like to calculate the effective interest rate of these solutions, so I can compare paying down the debt against investments.

    For example, if paying an extra $100 on my debt saves me $10,000 after five years, I’d like to compare that to investing $100 for five years and whether it would be better to invest the money.

    I think this calculation comes down to a negative amortization case along with a negative monthly payment. I tried putting the total money saved as the balloon payment and putting the monthly contribution (negative) in as the payment, hoping that I could then solve for the effective interest rate, but it looks like the calculator doesn’t accept negative payments. (it sets my payment to 0 and changes other parameters)

    Is there a way to do the calculation I’m trying to do using the calculator as it stands?

    1. Hi, Scott. Interesting idea, but you’re right: the calculator doesn’t allow for negative payments. My intuition suggests the math isn’t quite right for an investment scenario either, because interest is not compounded. But I need to think about this for a bit with paper and pencil to verify that notion.

  5. Hi Bret, brilliant calculators. I am attempting to write an Excel worksheet which assists me to calculate a “Debt Service”….essentially a backwards amortization. Thus, I know the interest rate, and term in years, but would like to know the total amount paid in P&I over the course of the loan. Your calculator provides this information, but I would like to incorporate it into my worksheets. Any advice or direction would be most highly appreciated.

    1. Hi, Iowan. The other piece of information you would need will be the periodic payment amount. Since you know the term of the loan, you will be able to directly calculate the total P&I, which is just the number payments times the payment amount. You can also start working backwards from the last payment. If you have an annual interest rate and you’re making monthly payments, then the periodic interest rate will be i%/12. If we call the payment amount ‘x’, then the final interest payment will be i%/12 times x (call this amount I_f). If you subtract I_f from ‘x’, you’ll get the final principal payment. For the next-to-last payment, we would have to pay the same interest as on the final payment, since the final principal would still be outstanding. So if we subtract the final interest payment from the payment amount ‘x’, the remaining amount must be the P&I for the next-to-last payment. The interest part would be i%/12 * (x – I_f); and the principal part would be (x – I_f). Keep the same pattern going until you’ve run to the beginning of the term.

      I hope that gets you started. If you look through the PDF documents on the derivations, you’ll find other formulae which will help you calculate P&I for each payment directly, if you prefer.

  6. I have made a loan where the borrower because of his cash flow wanted a 5yr loan with semi-annual payments on 6-30 and 12-31 for 5 years but the first one to start this Dec 31 or just 97 days in 2011. We agreed to a payment of $2043.89 on 12-31-2011 and then 10 payments of $3514.08 plus a last cleanup one. How can I get that first payment into the amortization table ?

  7. Hi Bret,
    Thanks for the site. I am receiving a loan (100K) at 3%. I would like to make a $250 a month payment each month until the loan is paid off and one time each year, I would make a payment of $10,000 on the principal. I played around with the spread sheet you provided, but I’m no accounting genius. Any help would be appreciated!

    1. I am no accounting genius either, Shar: I’m sure they get paid more than I do. 🙂

      Unfortunately, the spreadsheet is not set up to do what you need it to do. You have a negative amortization situation for at least part of the loan, and bending the current spreadsheet to your will may be more trouble than starting afresh with something much simpler.

  9. I need help, please. I need to run a NEW loan amorization with a different balloon payment.I want to change the ballon payment only. The folks that bought our farm that we owner financed, illegally cut the timber off the land and the money was awarded to us and and I need to deduct this amount from the balloon payment at the end of the ten year owner financing. I want the ten year amorization to stay the same and just change the balloon payment that is due in ten years. I know nothing about how to do this and your help will be greatly appreciated.

    1. Hi, Kay. This sounds like an interesting problem, but I don’t know what the correct way to do the calculation would be. I would think you’d need to consult an attorney or accountant to figure out what is fair to everyone. This is not my area of expertise.

      Good luck!

  10. Brett,

    Do you have a calculator that calculates Credit Card debt. the calculator that do have seems to calculate the interest rate charges as though it were a home mortgage which is different than a credit card. So the outcome calculated is too high.

    1. Hi, Geoffrey. I do not have a credit-card-specific calculator. From the credit card agreements I’ve seen over the years, there are so many variations in how interest and fees are calculated that creating a one-size-fits-all solution presents quite a challenge. The best that any general-purpose loan calculator can do is to provide an estimate. Otherwise, I suspect one would have to write a program or spreadsheet tailored to specifications of each unique credit card agreement.

Leave a Reply