Loan Amortization Templates

Loan Amortization Templates
What are Loan Amortization Templates?

Amortization means the spreading out of payments over more than one period of time. In business, an amortization is a schedule for a series of mortgage or loan payments. It shows you how every transaction affects the principal amount that’s providing 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. Exceltemplates.net has an excellent loan amortization Excel template when you’re ready to use it. 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 borrows can take advantage of.

The more simple amortization calculators typically assume that the compounding period and the frequency of payment occur at the same rate. This means that the period rate is the same as the yearly rate of interest divided by the amount 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, there are loans that use yearly compounding interest; payments are made every month, and are determined by splitting the yearly payment by twelve. In order to calculate the interest (which only happens at the beginning of the year for interest), our loan amortization 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 displays every payment and shows the interest rounded to the closet 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

Loan amortization templates make 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 who are 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 which takes into account rounding and additional payments because you need to make sure the last payment decreases the balance down 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.

Payment

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 Excel loan amortization template:

Using The Amortization Formula Excel Template

Select a folder to download the template too. 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. The template is free to download and is ready to help you start figuring out your loans today.

DOWNLOAD