Loan Amortization Schedule Excel Templates

Loan Amortization

Loan Amortization Templates

What are loan amortization schedule Excel templates?

Amortization means the spreading out of payments over more than one period of time. In business, amortization is a schedule for a series of mortgage or loan payments. It shows you how every transaction affects the principal amount that provides the payments and the interest on that principal.

The remainder of the payment that’s due after each payment is displayed, so it’s easier for you to keep track of what you owe. If you need a loan amortization schedule Excel template free download, has plenty of them ready for use. Specialized calculators perform the calculations of figures that appear in amortization schedules because they are designed to handle the amount of rounding required.

Loan amortization schedules can be used for mortgages and loans. Smaller lenders can use the template to create a schedule for repayment that borrowers can take advantage of.

The simpler amortization calculators typically assume that the compounding period and the payment frequency occur at the same rate. This means that the period rate is the same as the yearly rate of interest divided by the number of periods in a year. In Canada, the compounding and payment periods aren’t the same, so a less specific formula is necessary.

In the United Kingdom, some loans use yearly compounding interest; payments are made every month and are determined by splitting the yearly payment by twelve. To calculate the interest (which only happens at the beginning of the year for interest), our loan amortization schedule Excel template sets the compounding period and frequency of payment to annual.

Negative Amortization

In two different situations, you might find yourself with negative amortization indicated by your excel loan amortization template:

  • If you select a compounding period that isn’t as long as the payment frequency
  • If the payment you’ve made doesn’t cover any interest added.
  • If you select a compounding period that isn’t as long as the payment frequency

Rounding In Amortization Calculations

Schedules for repaying loans almost always display every payment and the interest rounded to the closest cent. The reason for this rounding is to make sure that the schedule genuinely reflects payments for borrowers. Amortization calculators that don’t include rounding are usually designed with educational or illustrative intentions in mind. If you don’t need to see the data presented as actual payments, you can turn off the rounding option.

Assuming you keep the option on, the final payment in your amortization schedule often needs to be altered so that the balance can be zeroed out. This can be achieved by either changing the payment or the amount of interest. Whichever method you choose, the spreadsheet may display slightly altered compared to the numbers provided by other amortization calendars.

Additional Payments

A loan amortization schedule Excel template free download makes it easy to deal with any additional payments. All you need to do is move the additional payment to the principal amount for that period. When it comes to fixed-rate loans, the balance and the entire amount of interest are both reduced. This is good news for people trying to repay loans earlier than planned. Keep in mind that the regular payments aren’t also going down.

Zero Balance

It can be difficult to draft a schedule that takes into account rounding and additional payments because you need to make sure the last payment decreases the balance to zero. If you’re finishing your final payment, or if your regular payments are higher than the amount that is 1 + the rate multiplied by the balance, then that is how much you pay.


Payments are usually made when a period is ending. Choosing to pay when the period begins makes it so that there’s no interest paid in the first installment. The normal payment will be a little different.

Schedule For Loan Payment

If you have accrued additional payments, you can account for them by recording them in your amortization template. It might be more convenient for you to create a separate list for the adjusted list of payments. Put it this way: your payment is $200 per month. If you make a payment of $250, the extra $50 can be recorded as an extra payment on its own, or it can be incorporated into the rest of the payment.

Here’s some insight into exactly what it takes to use our loan amortization schedule Excel template:

Using The Amortization Formula Excel Template

Select a folder to download the template to. Copy it and give a generic name to the copy so you can reuse it later.

Information about the loan (amount, interest, how many payments) can be entered in the field title Enter Values. The section labeled Loan Summary will start to display the information you typed in after the template applies certain formulas. The only other two places you will need to input data are Lender Name and Payment Date.

To help you make sense of what you’re looking at more easily, you can pick different colors and word fonts. Our loan amortization schedule Excel template free download is ready to help you start figuring out your loans today.