Bret's Amortization Calculator FAQ (2024)

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

• Go to the calculator
• Visit Bret's Blog
• Go to Bret's Home Page

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 (theprincipal) plus interest over a fixed period of time, so that theprincipal is completely eliminated by the end of the term. This wouldbe trivial if interest weren't involved, since one could simply dividethe principal amount into a certain number of payments and be donewith it. The trick is to find the right payment amount, whichincludes some principal and some interest. The math isn't celestialmechanics, but it probably doesn't come standard on the basicpocket 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 calculatethe principal remaining after a given number of payments, and how toamortize with an initial payment moratorium in this document.If you're trying to find some original loan parameters for an amortizationschedule in the process of repayment, there's some additional mathhere which mayhelp. Finally, there's also a document showingequations for calculating the total accumulated interest paid outafter 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 andthe 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 theUnited States) and short-term loans, but the technique can also beapplied to figure out how long it would take to pay off a givencredit card debt (for example). In fact, this latter applicationwas 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 outwhich field is blank (or zero), and then determines what that valueshould be, given the other numbers you've filled in. However, the calculator will not figure out thePayments per Year if this is left blank: you will begiven an error message.The Balloon Payment field is treated specially: itnormally 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 thiscalculator as a guide in your decision-making or to explorealternatives, but please consult your lending institution or financialadvisor 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 numberof different things. For example, if you are car-shopping and youwant to know how much you can reasonably borrow given that youcan safely make a $325 monthly payment over 5years, put 325 in the Payment Amount field, 60 in the Number of Regular Payments box, and fill in an APR that yourlending institution will give you. When you click Calculate,the Principal field will be filled in with the amount you canborrow under these conditions. Each time you try a different set ofnumbers, be sure to delete or clear out the field that you want thecalculator to figure for you.

Since I originally wrote the calculator to figure out how long itwould 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 Principalfield, and 18.0 in the Annual Interest Rate field.Clear the Number of Regular Payments field, and set thePayment Amount box to the amount you think you can afford topay each month. When you click Calculate, the number ofpayments will be filled in for you. The paymentamount may be adjusted so that the entire $5000 (plus interest) willbe paid off in the given term.

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

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

In addition to finding the value that belongs in the emptyfield, the calculator shows you an estimate of the total amountof interest you'll be paying. A more accurate estimate of interestwill be found on the last line of the amortization schedule. Theactual interest you will pay depends on how your financialinstitution 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 paymentexceeds the interest part of a payment for the first time. Not allschedules will have a cross-over point, though most typical mortgageamortizations probably will.

How do I prevent the calculator fromchanging the Payment Amount when I am trying to computethe Number of Regular Payments?

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

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

How do I calculate an amortization if I make extra principalpayments?

The calculator can only handle extra payments under the followingconditions:

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, thenyou probably need some sort of a spreadsheet to help you generatean amortization table. I've written an Excel loan amortization spreadsheetto assist folks in analyzing these cases.

If the above conditions are met, then you may add the extra amountto the Payment Amount field and re-calculate. For example, ifyou have $100,000 loan at 8% over 30 years, the calculator determinesthat the Payment Amount is $733.76. If you want to make anadditional payment of $100 per month, set Payment Amount to833.76, clear the Number of Regular Payments field,and click Calculate. Under these conditions, the loan term hasbeen 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 Scheduleoption before clicking Calculate. Determine how many payments you have made so far, and look up the Principal Balance in the finalcolumn of the amortization table.

Can the calculator help me with refinancing?

A little. First, find out what your principal balance is (seethe previous question).Now enter the principal balance in the Principalfield. 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 negativeamortization loan is a scenario where the periodic payment is lessthan the interest that is due for that period. In this case,the unpaid interest is added into the principal amount, and sothe debt grows over time rather than being reduced. Since theseloans are never paid off, they are usually temporary or short-termarrangements, after which the loan is "recast" into an actual payoffscenario.

As an example,assume that we would like to know how much we will owe after anegative amortization period of 5years, 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 Rateat 8.0, Payments per Year at 12, set Number of Regular Payments to 60 (12×5), and set thePayment Amount to 600.00. Click on "Show AmortizationSchedule", and then click "Calculate". The Balloon Paymentfield will be filled in with the amount of money owed at the end of thenegative 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, whichmakes sense because this column is intended to show the principalreduction. You will also note that the principal balanceis increasing over time.

NOTE: I've recently noticed that some of the loan summaryinformation that the calculator produces in negative amortizationscenarios is inaccurate and misleading. The final line of the paymentschedule is also inconsistent. I will fix these things when I havethe 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,26payments per year, is treated as having 365/26=14.038...daysper payment period by the calculator. A true bi-weekly schedule wouldinstead use 14days exactly, and every 10years, there wouldbe an extra (27th) payment during the year.

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

My understanding of the "actual" bases is that the APR is dividedby 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 relativeto 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 anytimesoon 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 notthat I'm averse to writing more code, but in addition, the input formwould require more fields to be filled in by the user. I made aconscious effort to keep the input required to a minimum while maintaining the computational flexibility I wanted.

More profound issues are raisedif dates are added: for one, I would need to know how to calculate anamortization based on a bi-weekly payment schedule. I really don'tknow how financial institutions handle this case in real life. [If youwork for a lending institution and have specific info on how theperiodic interest rate is calculated for a true bi-weekly paymentschedule, 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 interestpaid per year for tax purposes?

To handle this properly requires that the calculator know when afiscal year begins and ends, and therefore requires some knowledge ofdates (see previous question). However, you can sidestep the issueand do the calculation manually. Suppose that payment 31 is the finalpayment of the previous fiscal year, and payment 57 is the finalpayment of the current fiscal year. To get the interest paid duringthe current fiscal year, subtract Cum Int for payment 31 fromCum 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, Ido not license it or make it available in any other form, even formoney. (Well, OK, if you wanna put me through grad school at MIT, Imight 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 Idon't keep track of what other people may offer. Frankly, I'msurprised that this calculator still seems to be so popular after allthese years. I would have thought that someone else would haveoutclassed this puppy long ago.

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

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