Mortgage Amortization: Calculate Using an Excel Template

According to the latest Mortgage Bankers Association survey, mortgage applications increased by 2.5% in 2023. With more homebuyers opting for mortgages, they should be aware of the loan repayment details.

Mortgage amortization in real estate refers to the loan repayment process with a set end date and equal monthly installments. 

Lenders provide home buyers with an amortization schedule to assist them through the repayment process. A mortgage amortization schedule outlines your loan repayments. It includes the loan amount, interest rate, amortization period, etc.

How to Calculate Mortgage Amortization?

Let’s understand how you can calculate mortgage amortization with the help of an example:

If you take a 30-year fixed-rate mortgage for $440,300 with an interest rate of 6.57%, your monthly payment would be $2,803.30. In the first month, $2,410.64 of your payment would go towards paying off the interest. $392.65 would go towards paying the principal.

This means that at the end of the first month, the borrower would owe $439,907.35. The second month’s interest will be calculated on this amount.

Over time, the amount of each payment that goes towards paying off the principal increases. While the amount that goes towards paying off the interest decreases.

» Home Value Estimate: Find out the value of your property here.

Amortization With Fixed-Rate Mortgages (FRMs)

With a fixed-rate mortgage, the interest rate remains constant throughout the loan term. Also, FRMs are fully amortizing, which means your loan closes once you make all payments.

Amortization With Adjustable-Rate Mortgages (ARMs)

Amortization is a key aspect of adjustable-rate mortgages. With an ARM, lenders periodically adjust the interest rate based on market conditions. This leads to changes in the monthly mortgage payments over time.

During the initial fixed-rate period, typically 5 to 10 years, the interest rate remains the same. The loan amortization takes place just like a fixed-rate mortgage. But, after this period, the interest rate may adjust annually or at predetermined intervals based on a specific index and margin.

The monthly mortgage amortization payment can change as the interest rates get adjusted. This directly impacts the amortization schedule. Interest rate adjustment also affects the part of each payment that goes towards interest and principal. The loan balance is changed as a result.

Information in a Mortgage Amortization Schedule

The amortization schedule includes the following information:

• Payment Number: This refers to the number of payments made.
• Payment Date: This is the date on which the payment is due.
• Principal Payment: The amount of the payment that reduces the principal balance of the loan is the principal payment.
• Payment Amount: This is the total amount due for the payment.
• Interest Payment: This is the amount that the lender charges as interest.
• Remaining Balance: The outstanding balance of the loan after the payment has been made is the remaining balance. 

How to Make an Amortization Schedule?

Here is a stepwise guide to creating an amortization schedule:

1. Gather Loan Information: Collect all the relevant information about your mortgage. This includes the loan amount, interest rate, loan term, and start date.
2. Calculate Monthly Payment: Use a loan calculator or mortgage calculator to determine your monthly payment.
3. Calculate Interest Payment: Establish the amount of interest that will be charged every month. Multiply the outstanding loan balance with the interest rate, then divide by 12 (for monthly payments).
4. Calculate Principal Payment: Subtract the interest payment from the monthly payment. The calculated amount will be used to reduce the loan’s main balance.
5. Calculate Remaining Balance: Subtract the principal payment from the outstanding balance of your loan to determine the new balance.
6. Repeat Steps 3-5: Repeat steps 3-5 for each month of the loan term. Also, calculate the new balance from the previous month to calculate the interest and principal payments for the current month.
7. Create a Mortgage Amortization Table: Create amortization tables that display all the calculated information for each month. Also, include the payment number, payment date, payment amount, interest payment, principal payment, and remaining balance.
8. Review and Adjust: Review the mortgage amortization schedule to ensure accuracy and adjust if necessary. Also, you can use the schedule to explore different scenarios, such as making extra payments or refinancing your loan.

Here is a free amortization schedule Excel template for you

• 30-year mortgage amortization schedule
• 15-year mortgage amortization schedule

📌It is important to note that the scheduled amortization for mortgages is based on the terms of the loan. This includes the interest rate and loan amount. Also, any changes in these factors can impact the schedule and the overall cost of the loan.

Factors That Affect Mortgage Amortization

Here are the factors that can impact the mortgage amortization process:

• Loan Term: Longer loan terms, such as 30-year mortgages, will have lower monthly payments but higher interest paid over the loan term. Shorter loan terms, such as 15-year mortgages, will have higher monthly payments but lesser total interest.
• Interest Rate: Higher interest rates will have higher monthly payments and total interest paid over the life of the loan. Conversely, lower interest rates will result in lower monthly payments and less total interest paid.
• Principal Amount: A higher loan amount will result in higher monthly payments and total interest paid. Whereas, with a lower loan amount, monthly payments and total interest paid will be reduced.

How to Payoff Mortgage Early?

Here’s how you can pay off your mortgage early:

• Refinance Your Mortgage: When you refinance a home, you apply for a new mortgage loan to replace your existing one. The process involves similar steps to obtaining a mortgage, such as lender research, and interest rate comparisons.
• Make Extra Mortgage Payments: You can pay extra with your monthly mortgage payments to quickly pay off your loan. However, your lender must approve extra payments without penalties.
• Dollar-A-Month Plan: You can simply pay an extra dollar each month to reduce your mortgage term. For instance, if you pay an extra dollar per month on a $150,000 mortgage at 6% for 30 years, you can shorten the loan term by 8 years.

Additionally, all these mortgage payoff strategies will help you pay a lesser amount of interest in total over the loan term.

Bottom Line

As a new homebuyer, it can be difficult to manage monthly payments. However, when you follow your amortization plan, you are able to stay on track with your finances.

Also, it’s advisable to use an amortization schedule and calculator to fully understand how and where your money is spent.

Find Your New Home With Houzeo

With thousands of property listings, Houzeo.com is one of the biggest property listing sites in the US. Find condos, townhouses, co-ops, and other types of homes for sale on Houzeo.

» Need More Clarity? Read these exclusive Houzeo reviews and learn why the platform is the best in America’s competitive housing market.

Frequently Asked Questions

• mortgage terms