Bret's Amortization Calculator FAQ

Hi. From the e-mail I have received over the years, the calculator gets a lot of use by all kinds of people, even some folks in the financial industry. In this document, I will try to respond to the most frequently asked questions.

Questions

  1. Can you help me figure out this financial problem...?
  2. What is amortization?
  3. Can you describe the data entry fields?
  4. How do I use the calculator?
  5. Why is one line in the schedule highlighted?
  6. How do I prevent the calculator from changing the Payment Amount when I am trying to compute the Number of Regular Payments?
  7. How do I calculate an amortization if I make extra principal payments?
  8. How do I get the current principal balance?
  9. Can the calculator help me with refinancing?
  10. Can I calculate a negative amortization?
  11. Does the calculator work on a 30/360 basis, or actual/365, or actual/actual?
  12. Can you make the calculator show me payment dates?
  13. Can you have the calculator figure out the interest paid per year for tax purposes?
  14. Can I download this calculator so I can use it on my computer or server?
  15. Do you know of another calculator that has feature X?
  16. Do you have a spreadsheet template, or any other finance software, that I can download?

Can you help me figure out this financial problem...?

Probably not. I am a computer programmer, not a finance person. I can answer questions about my amortization calculator, how it works, and the (possibly naive) assumptions I've made regarding amortization. And though I can balance my own checkbook, I have no experience in the banking or finance industries.

What is amortization?

Amortization is a means of paying out a predetermined sum (the principal) plus interest over a fixed period of time, so that the principal is completely eliminated by the end of the term. This would be trivial if interest weren't involved, since one could simply divide the principal amount into a certain number of payments and be done with it. The trick is to find the right payment amount, which includes some principal and some interest. The math isn't celestial mechanics, but it probably doesn't come standard on the basic pocket calculator.

For the curious, there's a mathematical presentation (PDF) of the problem and its solution. I've done some additional work which shows how to calculate the principal remaining after a given number of payments, and how to amortize with an initial payment moratorium in this document. If you're trying to find some original loan parameters for an amortization schedule in the process of repayment, there's some additional math here which may help. Finally, there's also a document showing equations for calculating the total accumulated interest paid out after a certain number of payments.

This calculator assumes that each payment should be the same amount, and that a payment consists of some amount for principal reduction and the interest calculated on the principal balance (including the principal part of the current payment). I have been told that some Canadian mortgages are not calculated using this method. (Thanks to Andrew Bell, who sent along a link describing Canadian mortgage compounding.)

Amortization is used most often in mortgages (at least in the United States) and short-term loans, but the technique can also be applied to figure out how long it would take to pay off a given credit card debt (for example). In fact, this latter application was why I wrote the calculator in the first place.

Can you describe the data entry fields?

Principal
For loans, this is the amount that's borrowed, the amount which will be paid off by the end of the amortization period.
Annual Interest Rate
Typically, this rate would be the APR (Annual Percentage Rate) without additional fees. This rate is divided by the Payments per Year to obtain a periodic interest rate which is actually used by the calculator. What may be called the "annual rate" can be quite confusing. See the Wikipedia entry for the Annual Percentage Rate and related articles for more detailed information.
Payments per Year
This should be self-explanatory. Monthly payments would by indicated by 12 Payments per Year, twice monthly payments by 24, etc. (This also determines the number of compounding periods in the year.) For payments every 2 weeks, enter 26, but beware that this is an approximation, since every 10 years you'll actually make 27 payments in the course of one calendar year. A similar caveat applies to any schedule based on any multiple of weekly payments because a calendar year contains just slightly more than 52 weeks, not to mention the additional complications of leap years.
Number of Regular Payments
The number of payments, combined with Payments per Year defines the term of the loan. If you're looking at a 30-year mortgage with monthly payments, you'd enter 360 into this field (12 × 30). I call these regular payments to distinguish them from the optional Balloon Payment.
Payment Amount
This is the amount one would pay every regular payment period.
Balloon Payment
This is an optional field. Some loans are set up so that there's a lump sum paid at the end of the term, most of it principal, but typically some interest component as well. If you are running a balloon scenario, just enter the amount of the balloon payment in this field. If the field is blank, the calculator will assume that there is no balloon amount involved (unless it is the only blank field). The calculator will treat the balloon payment as if it occurs one payment period after the last regular payment, so this value includes one additional cycle's interest payment.

How do I use the calculator?

When you click Calculate, the calculator figures out which field is blank (or zero), and then determines what that value should be, given the other numbers you've filled in. However, the calculator will not figure out the Payments per Year if this is left blank: you will be given an error message. The Balloon Payment field is treated specially: it normally remains blank. If it is the only blank field, however, the calculator assumes that you want to calculate what the balloon payment should be, given the other values.

IMPORTANT NOTE: You are welcome to use this calculator as a guide in your decision-making or to explore alternatives, but please consult your lending institution or financial advisor before making your final decision, since I am not a finance person. (See #1 above.)

As you see, the calculator can be used to calculate a number of different things. For example, if you are car-shopping and you want to know how much you can reasonably borrow given that you can safely make a $325 monthly payment over 5 years, put 325 in the Payment Amount field, 60 in the Number of Regular Payments box, and fill in an APR that your lending institution will give you. When you click Calculate, the Principal field will be filled in with the amount you can borrow under these conditions. Each time you try a different set of numbers, be sure to delete or clear out the field that you want the calculator to figure for you.

Since I originally wrote the calculator to figure out how long it would take me to eliminate some credit card debt, I'll provide an example of that calculation. Assume you've got a $5000 balance on a credit card at 18%. Put 5000 in the Principal field, and 18.0 in the Annual Interest Rate field. Clear the Number of Regular Payments field, and set the Payment Amount box to the amount you think you can afford to pay each month. When you click Calculate, the number of payments will be filled in for you. The payment amount may be adjusted so that the entire $5000 (plus interest) will be paid off in the given term.

As a final example of how to use the calculator, assume that you've got a $45,000 principal balance remaining on a house at 7.5%, and you'd like to pay that amount off in 5 years so that you can retire without a having a house payment. To figure out how much you should be paying now to pay off the loan, put 45000 in the Principal field, 7.5 in the Annual Interest Rate field, and 60 in the Number of Regular Payments field (12 months × 5 years). Clear the Payment Amount field and click calculate. By paying the calculated Payment Amount, you should be able to retire mortgage-free.

If the Show Amortization Schedule option has been activated, when you click Calculate, you will be shown a table of all the payments, their principal and interest components, and running totals of the principal and interest components.

In addition to finding the value that belongs in the empty field, the calculator shows you an estimate of the total amount of interest you'll be paying. A more accurate estimate of interest will be found on the last line of the amortization schedule. The actual interest you will pay depends on how your financial institution rounds its numbers, but the estimate should be close.

Why is one line in the amortization schedule highlighted?

This line indicates the cross-over point for the payment schedule, the point in the amortization when the principal part of the payment exceeds the interest part of a payment for the first time. Not all schedules will have a cross-over point, though most typical mortgage amortizations probably will.

How do I prevent the calculator from changing the Payment Amount when I am trying to compute the Number of Regular Payments?

The calculator only wants to work with an integer number of payments. Sometimes, the Payment Amount you enter may result in a non-integer number of payments. In such a case, the calculator rounds the Number of Regular Payments to the nearest integer, and then re-calculates what the Payment Amount should be under these altered conditions. If this is not the behavior you want, then the calculator can be coerced into doing it your way.

Let's assume that you borrow $5000 from your great-grandmother at 8% (you want to be fair, afterall). You want to make payments of $250 monthly. How long will it take to pay off? You enter the parameters into the calculator, and it tells you that the loan can be paid off in 22 months, but that you will only pay $245.10 per month. For some reason, this is unacceptable (perhaps because it does not quite cover great-grandma's $250 monthly bingo habit), so you invoke the Balloon Payment field to handle the left-overs: subtract one from the Number of Regular Payments (resulting in 21 in this case), and reset the Payment Amount to 250.00. When you click "Calculate" this time, the Balloon Payment field is filled in with your final payment. In this scenario, you'll make 21 regular payments of $250.00, and one "balloon" payment of $134.36.

How do I calculate an amortization if I make extra principal payments?

The calculator can only handle extra payments under the following conditions:

  1. Extra payments are the same amount each time
  2. Extra payments are made at the same time as regular payments
  3. Extra payments are made every regular payment period

If any of these conditions don't apply to your situation, then you probably need some sort of a spreadsheet to help you generate an amortization table. I've written an Excel loan amortization spreadsheet to assist folks in analyzing these cases.

If the above conditions are met, then you may add the extra amount to the Payment Amount field and re-calculate. For example, if you have $100,000 loan at 8% over 30 years, the calculator determines that the Payment Amount is $733.76. If you want to make an additional payment of $100 per month, set Payment Amount to 833.76, clear the Number of Regular Payments field, and click Calculate. Under these conditions, the loan term has been reduced to 242 payments, or just over 20 years.

How do I get the current principal balance?

Plug in the loan parameters, and set the Show Amortization Schedule option before clicking Calculate. Determine how many payments you have made so far, and look up the Principal Balance in the final column of the amortization table.

Can the calculator help me with refinancing?

A little. First, find out what your principal balance is (see the previous question). Now enter the principal balance in the Principal field. If you're planning a zero cash out-of-pocket re-fi, you should add closing costs/points to the Principal amount. Then you can play with the numbers in the other fields.

Can I calculate a negative amortization?

Yes, the calculator can perform negative amortizations. A negative amortization loan is a scenario where the periodic payment is less than the interest that is due for that period. In this case, the unpaid interest is added into the principal amount, and so the debt grows over time rather than being reduced. Since these loans are never paid off, they are usually temporary or short-term arrangements, after which the loan is "recast" into an actual payoff scenario.

As an example, assume that we would like to know how much we will owe after a negative amortization period of 5 years, given that we have borrowed $100K at 8%, and we'll be making monthly payments of $600.00. We enter Principal at 100000.00, Annual Interest Rate at 8.0, Payments per Year at 12, set Number of Regular Payments to 60 (12×5), and set the Payment Amount to 600.00. Click on "Show Amortization Schedule", and then click "Calculate". The Balloon Payment field will be filled in with the amount of money owed at the end of the negative amortization period ($105,597.78, in this example). If you look at the amortization schedule, you will see that the "Principal" column contains negative values, which makes sense because this column is intended to show the principal reduction. You will also note that the principal balance is increasing over time.

NOTE: I've recently noticed that some of the loan summary information that the calculator produces in negative amortization scenarios is inaccurate and misleading. The final line of the payment schedule is also inconsistent. I will fix these things when I have the opportunity, but for now, please be aware of these "gotchas".

Does the calculator work on a 30/360 basis, or actual/365, or actual/actual?

Strictly speaking, none of the above. For all payment schedules, the calculator treats the "Payments per Year" as equally-distributed. E.g., a "bi-weekly" payment schedule, 26 payments per year, is treated as having 365/26=14.038... days per payment period by the calculator. A true bi-weekly schedule would instead use 14 days exactly, and every 10 years, there would be an extra (27th) payment during the year.

A 30/360 basis treats the year as having 360 days, each month having 30 days, resulting in 12 equally-spaced payments per year. For monthly payments, the calculator appears to be on a 30/360 basis since the math ends up the same: (APR×30)/360 is the same as APR/12.

My understanding of the "actual" bases is that the APR is divided by the number of days in the year to provide a daily interest rate, and the interest is calculated on the true number days between scheduled payments. If one were to plot the interest paid over time, the actual basis methods will produce slightly jittery curves relative to the curves this calculator will produce.

Can you make the calculator show me payment dates?

As convenient as this feature may be, it's not gonna happen anytime soon for several reasons. The calculator is rather simple right now, and it would need to have a whole lot more intelligence to handle dates properly. I like to program, so it's not that I'm averse to writing more code, but in addition, the input form would require more fields to be filled in by the user. I made a conscious effort to keep the input required to a minimum while maintaining the computational flexibility I wanted.

More profound issues are raised if dates are added: for one, I would need to know how to calculate an amortization based on a bi-weekly payment schedule. I really don't know how financial institutions handle this case in real life. [If you work for a lending institution and have specific info on how the periodic interest rate is calculated for a true bi-weekly payment schedule, please fill me in!] In short, the calculator program is just a quickie (it took longer to figure out the math than to write the actual program), and I prefer its current simplicity.

Can you have the calculator figure out the interest paid per year for tax purposes?

To handle this properly requires that the calculator know when a fiscal year begins and ends, and therefore requires some knowledge of dates (see previous question). However, you can sidestep the issue and do the calculation manually. Suppose that payment 31 is the final payment of the previous fiscal year, and payment 57 is the final payment of the current fiscal year. To get the interest paid during the current fiscal year, subtract Cum Int for payment 31 from Cum Int for payment 57.

Can I download this calculator so I can use it on my computer or server?

Sorry, but this calculator and its source code are not available. It is old and idiosyncratic. Since I cannot offer support for it, I do not license it or make it available in any other form, even for money. (Well, OK, if you wanna put me through grad school at MIT, I might consider it. ;-)

Do you know of another calculator that has feature X?

Sorry, I'm not trying to keep up with the Jones's calculators, and I don't keep track of what other people may offer. Frankly, I'm surprised that this calculator still seems to be so popular after all these years. I would have thought that someone else would have outclassed this puppy long ago.

Do you have a spreadsheet template, or any other finance software, that I can download?

Funny that you should ask. In June 2010 I wrote an amortization spreadsheet in Excel which may be useful with irregular extra-payment or late fee situations which the online calculator isn't well-equipped to handle. And since it's something you download onto your own computer, it may also be handy as a means of tracking your loan or mortgage payments. Use at your own risk!