Amortization Schedule for a Home Loan
- May 8
- 2 min read
Updated: May 9
How to Calculate Home Loan Interest Rate, Mortgage Payment, and Loan Balance for your Home Loan?
A mortgage amortization schedule is a structured way of breaking down a loan into equal monthly payments over time, showing how much of each payment goes toward interest and how much reduces the principal. In the given example, a loan of $650,000 is taken over 30 years (360 months) at an annual interest rate of 6%, which converts to a monthly interest rate of 0.5% (0.06 ÷ 12). The fixed monthly repayment is calculated using the standard annuity formula, which ensures the loan is fully repaid by the end of the term with equal payments of $3,897.08 each month.
The monthly payment formula is based on the present value of an annuity:
PMT = P × [r(1+r)^n] / [(1+r)^n − 1],
where P is the loan amount, r is the monthly interest rate, and n is the number of periods. This formula balances the interest growth and principal reduction so that the loan reaches zero exactly at the final month.
Each monthly payment is split into two parts: interest and principal. The interest portion is calculated as Outstanding Balance × Monthly Rate, while the principal portion is the remainder: Payment − Interest.
Early in the loan, interest dominates because the outstanding balance is high. Over time, the interest decreases and more of the payment reduces the principal, which accelerates repayment.
In the example, after 359 months, the remaining balance is only about $3,877.69. The interest for that month is approximately $38.68, leaving $3,858.40 as principal repayment. This demonstrates how, near the end of the loan, most of the payment goes directly toward clearing the remaining debt.
Overall, the total repayment over 30 years is $1,402,948.23, meaning the total interest paid is approximately $752,948.23. This highlights the long-term cost of borrowing and shows how amortization spreads both interest and principal in a predictable mathematical pattern that can be modelled precisely in Excel.
Download the excel template and adjust it for yourself here.
Comments