In that case, the rate per period is simply the nominal annual interest rate divided by the number of periods per year. When the compound period and payment period are different (as in Canadian mortgages), a more general formula is needed (see my amortization calculation article). Start by entering the total loan amount, the annual interest rate, the number of years required to repay the loan, and how frequently the payments must be made.

Interest Rate, Compound Period, and Payment Period

Each column will use a different formula to calculate the appropriate amounts as divided over the number of repayment periods. An amortization schedule template is a structured financial tool that outlines the complete payment plan for a loan, ensuring clarity and predictability in debt repayment. It serves as a roadmap that details each periodic payment over the loan term, breaking it down into principal and interest components. The amortization schedule is handy for fixed-rate loans, repayment schedule in excel where the payment remains constant, but the distribution between principal and interest shifts over time. Initially, a more significant portion of each payment covers the interest, while gradually, more is applied toward reducing the principal. Amortization, derived from the Latin word meaning “to die off,” signifies the gradual elimination of a financial obligation.

• One way to account for extra payments is to record the additional payment.
• This may involve paying a late fee or making a larger payment in the next period.
• For example, if you have 12 payments per year for 30 years, then the sequence function below generates numbers 1 thru 360.

By keeping your schedule up-to-date, you will have a better idea of your loan’s progress and be able to make informed decisions regarding your finances. You can now use this schedule to make financial decisions and keep track of your loan payments. Remember to update the table if you make a payment early or late, or if your interest rate changes. One way to account for extra payments is to record the additional payment. This spreadsheet includes a second worksheet (the Loan Payment Schedule) that allows you to record the actual payment instead.

Interest Rate

By default this calculator is selected for monthly payments and a 30-year loan term. A person could use the same spreadsheet to calculate weekly, biweekly or monthly payments on a shorter duration personal or auto loan. When an amortization schedule includes rounding, the last payment usually has to be changed to make up the difference and bring the balance to zero.

Bonus #1: Home Mortgage Calculator (Commercial Version)

In this template, you will find your revised amortization table with a selected moratorium period range. In simpler terms, amortization occurs when a borrower repays a debt through regular, equal installments over time. With each payment, a portion is allocated towards the principal amount, while the remaining portion goes towards covering the interest charges. This systematic approach ensures a structured and predictable repayment process, allowing the borrower to gradually eliminate the debt. Basic amortization calculators usually assume that the payment frequency matches the compounding period.

If you prefer to have all the results as positive numbers, put a minus sign before the PMT, IPMT and PPMT functions. Because the loan amount is a positive number and principal is a negative number, the latter is actually subtracted from the former. She holds a Bachelor of Science in Finance degree from Bridgewater State University and helps develop content strategies.

The free downloadable Excel template includes different grace periods, no grace periods, random moratorium selection, and moratorium period range. An amortization schedule is a tabular representation that outlines the repayment breakdown for each payment period of an amortized loan. This spreadsheet assumes that the extra payment goes into effect on the payment due date. There is no guarantee that this is how your lender handles the extra payment!

Limitations of Amortization Schedules

Due to the use of relative cell references, the formula adjusts correctly for each row. To check whether your calculations are correct at this point, add up the numbers in the Principal and Interest columns. The sum should be equal to the value in the Payment column in the same row. As you can see, with a 30 year payment of $100,000 loan at 5.35% interest rate, more than half of the payments (50.26%) go towards interest. We can use simple arithmetic formulas like SUM or division (/) to calculate such values.

It allows the borrower to track his/her loan repayment process easily. Understanding loan repayment can be complex, but an amortization schedule simplifies it by separating each payment into principal and interest. This transparency allows borrowers to see precisely how their payments contribute to reducing their debt over time. It also highlights how much interest they will pay over the loan’s duration, enabling them to make informed financial decisions about loan terms, refinancing, or early repayments.

Excel Monthly Amortization Schedule Template Tips

Using Excel is a great way to keep track of what you owe and to come up with a schedule for repayment that minimizes any fees you might end up owing. For your convenience, we publish local Indianapolis mortgage rates below to help you see currently available rates. Loan amount – the amount borrowed, or the value of the home after your down payment.

In the above example, we built a loan amortization schedule for the predefined number of payment periods. This quick one-time solution works well for a specific loan or mortgage. The first three arguments are the annual rate of the loan, the monthly payment needed to repay the loan, and the principal borrowed. The last two arguments are optional, the residual value defaults to zero. The term argument payable in advance (for one) or at the end (for zero) is also optional. The first three arguments are the length of the loan (number of periods), the monthly payment to repay the loan, and the principal borrowed.

• Initially, a more significant portion of each payment covers the interest, while gradually, more is applied toward reducing the principal.
• This process aids borrowers in financial planning and supports lenders in tracking payment progression.
• This will give you a more accurate representation of your payments and how they impact the life of your loan.
• An amortization schedule is a tabular representation that outlines the repayment breakdown for each payment period of an amortized loan.
• The sum should be equal to the value in the Payment column in the same row.

However, the ending date of loan repayment will change depending on the moratorium periods. For example, a fully amortizing loan for 24 months will have 24 equal monthly payments. Each payment applies some amount towards principal and some towards interest. To detail each payment on a loan, you can build a loan amortization schedule. A monthly amortization schedule is an amortization schedule that shows the principal paid, interest paid, and the remaining loan balance after each monthly payment over the loan tenure.