Amortization refers to the gradual reduction in a loan amount with periodic payments, usually of equal size, over a predetermined loan term. The amortization schedule is a special table used by borrowers that details the payments and due dates for a loan or mortgage, including how much of each payment goes toward interest and how much goes toward the principal. It also shows your outstanding balance after each payment, giving you an idea of how much you still have to pay.
To generate an amortization schedule for a loan, you can use our free amortization schedule template, which is available to you in an editable and easy-to-use Excel format. Amortization plans aren’t just used for fixed-rate mortgages — you can also use our amortization calculator when taking out personal loans, student loans, or auto loans.
Now, let’s explore our template in more detail:
Overview
Loans come in all shapes & sizes; from a large mortgage on your house to small car loans.
Once you have entered basic loan details, including the Annual Interest Rate, loan term, payment frequency, and compound period – our template instantly creates an amortization schedule specially tailored to you and your financial situation.
The result is a detailed payment plan spread out as per your set period in “term of loan in years” and “payment frequency”. The template breaks down every payment into (a) the share of the payment that goes toward paying the principal amount you borrowed and (b) the share that goes towards paying the interest. You can set this payment frequency to weekly, bi-weekly, semi-monthly, monthly, bi-monthly, quarterly, semi-annually, or annually – as agreed upon between you and your lender.
In this Summary section, we provide a quick snapshot of the loan repayment process based on the details you provided earlier, including the number of payments you must make. If you select Monthly as your Payment Frequency, and the loan term is 12 years, this number will be 144; if you choose ‘weekly’, this number changes to 624.
In addition, this section also calculates the interest rate per payment frequency by dividing the annual interest rate by the number of periods in a year. Based on this, we also show exactly how much Total Interest (in dollars) you will pay on your Principal. It also allows you to calculate the amount of money you save on interest payments by making an additional payment on top of your regular monthly installment (Est. Interest Savings).
Amortization Calculations
Interest
When calculating interest, it is important to distinguish between the payment frequency (which refers to the interval at which payments are made) and the compounding period which is the interval at which interest is calculated and added to an investment or debt. In an ordinary simple annuity in the US, the payment period and the compounding period are the same. For example, if a borrower makes monthly payments, the interest will also compound monthly.
If these two periods are not the same (say, the payment period is monthly while the compound period is quarterly) – our template reconciles differences in compounding and payment period frequencies using an interest conversion formula. That is, by multiplying the initial loan amount, or principal, by one plus the annual interest rate raised to the number of compound periods minus one. This will leave you with the total sum of the loan, including compound interest. Subtract the initial principal from this and you are left with the total compound interest.
By default, the compound period in our template is set to the same as the payment frequency, but this can be changed if, for example, the loan is a Canadian mortgage. Our template displays a warning if you select a compound period that is shorter than the payment frequency. This is because such a configuration may lead to negative amortization with inaccuracies in interest calculations and payment schedules.
What Is Negative Amortization?
Negative amortization is when interest charged on a loan is greater than the loan payment, causing the outstanding balance to increase. This happens when borrowers make reduced payments for a certain period, and can generally be offset by increasing monthly payments.
Additional Payments
Our template also allows you to see what happens when you pay an additional amount i.e. beyond your standard monthly payment. Simply add this extra amount in the column next to the Payment Due. For fixed-rate loans, this allows you to calculate the amount of money you will save on interest. This option can be helpful in situations where you want to pay off a loan faster.
For example, let’s say you took out a loan of 5,000 USD in January 2023 to buy a new car, hoping to pay it off over the next three years. Thanks to a major increment in your salary, you are now in a position to pay off the loan within 2 instead of 3 years. Is it possible for you to make extra payments and lower your principal loan value? Well, yes.
Normally, an amortization plan lets you do this in two ways. In Scenario A, any extra amount you pay won’t affect your regular payment amount and therefore, the interest rate will not be recalculated. However, whatever you pay on top of your regular payment amount will be knocked off from your last payment(s) – so you may expect to see a reduction in the number of installments overall.
In Scenario B, any extra payment is deducted from your outstanding Principal amount right away, that is, your remaining Principal at the end of each successive installment. As a result, in this case, both the interest and monthly installments will be recalculated.
To keep things simpler, our template is based on Scenario A where extra payments only affect the amount of the last payment. However, different banks or lending institutions may have different rules & regulations when it comes to accelerated amortization, so you might want to confirm which of these scenarios applies to you.
Interest Vs. Principal
As you can see in the mainsheet of our template, the monthly payment for your loan remains constant over the life of the loan. This is true of all amortized loans, which involve fixed payments until the loan matures.
What changes is the portion of the monthly payment that goes towards principal and interest over the term of the loan.
Note that as your loan life progresses, the share of payment going towards covering interest decreases, while that going towards paying off the principal amount increases.
Rounding
For easier calculation and consistency, the monthly payment and the interest payment in our spreadsheet are rounded off to two decimal points. When you switch on the rounding option in our template, we round off the value to cents for each monthly payment and add the difference to the last payment to bring the total value to zero.
You can also switch off the rounding function to enable comparison with scenarios where you do round the payment or interest. Remember, if you round in your calculations, you’ll end up with a slight difference in either the total underreporting interest paid and the overreporting principal paid or vice versa.
Bar Chart
In addition to a breakdown of each monthly payment in our main sheet, we also add a bar chart to help you graphically visualize the loan repayment process over time. Where the dark blue bars represent the Principal amount paid, while the light blue bars on top show the Interest paid upon each successive payment. A dotted line has been added to depict the remaining balance i.e., the outstanding balance after each payment.
Why Is this Template Helpful?
From aspiring home-owners and bankers to financial portfolio managers and professional accountants, an amortization schedule template is an extremely useful financial tool for you as it:
- Helps you understand loan repayment terms & obligations.
- Helps ensure timely payments by providing a structured timeline
- Equips you to better evaluate loan options by highlighting the cumulative cost of interest.
- Helps you budget more effectively as it helps you manage monthly expenses related to loan payments.
- Helps you understand and forecast loan value vs. asset value over time.
- Allows you to analyze personal or corporate finance loan structures or cash flow.
- Assists in managing financial records and ensuring accurate reporting of liabilities.
Our editable template can be instantly accessed in .xlsx, .xltx, and .ods formats.
Be aware that spreadsheets are somewhat prone to error. Even if the spreadsheet is completely free of errors at the time you download it, there is always a possibility that you might accidentally introduce errors as you edit it. With that said, download and enjoy!
