If you have an adjustable rate mortgage (ARM) or have thought about getting one, you may wonder how your loan balance is amortized. Designing an amortization schedule is an easy way to see how a mortgage payment is applied to paying down the balance over time.
You can create an amortization schedule using spreadsheet software such as Microsoft Excel or Google Sheets. For the purpose of illustration, focus on the principal and interest components of the monthly mortgage payment and omit add-ons like property taxes and homeowners’ insurance.
It’s simple to determine if you’ve created the schedule correctly for a fully amortizing loan: the loan balance should reach zero at the end of the term.
First, learn how a Fixed-Rate Mortgage amortizes
Amortization may be easier to visualize with a fixed-rate mortgage (FRM) because the interest rate and payment are fixed throughout the loan term.
For example, if you have a $200,000, 30-year fixed rate loan with an interest rate of 4%, your monthly mortgage payment of $954.83 remains constant throughout the 360 months of your loan. Use the payment (PMT) function to perform this calculation; =PMT (4%/12, 30*12, -200000, 0, 0).
To design the schedule, create columns to reflect how the balance is reduced each month. For example, your first few months in your schedule should look like this:
| Month | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 1 | $954.83 | $666.67 | $288.16 | $199,711.84 |
| 2 | $954.83 | $665.71 | $289.12 | $199,422.71 |
| 3 | $954.83 | $664.74 | $290.09 | $199,132.62 |
And the last few months look like this:
| Month | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 358 | $954.83 | $9.49 | $945.35 | $1,900.16 |
| 359 | $954.83 | $6.33 | $948.50 | $951.66 |
| 360 | $954.83 | $3.17 | $951.66 | $0.00 |
Then, see how Adjustable-Rate Mortgage payments are calculated and the loan balance amortizes
An ARM follows similar calculations but the monthly payment is adjusted when the rate adjusts. The new payment is calculated based on the 1) new interest rate, 2) current loan balance (which should be paid down from the original balance), and 3) remaining term on the loan.
For example, let’s say you have a 10/1 ARM on a mortgage loan of $200,000 and the mortgage fully amortizes in 30 years. The opening interest rate is 3.25% and the rate adjusts to 4.25% after 10 years and then to 5.25% after 11 years. (These rates are provided for illustration purposes only. Typically, you won’t know the precise interest rate adjustments beforehand but you should be aware of how the interest rate is calculated).
For the first 10 years, the monthly payment is $870.41. Your first few months will look like this:
| Month | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 1 | $870.41 | $541.67 | $328.75 | $199,671.25 |
| 2 | $870.41 | $540.78 | $329.64 | $199,341.62 |
| 3 | $870.41 | $539.88 | $330.53 | $199,011.09 |
During this time, the mortgage balance steadily declines until the remaining balance is $153,458.95 at the end of 10 years or 120 months. Starting in year 11, the monthly payment is adjusted to $950.27 based on the new annual interest rate of 4.25%, remaining loan term of 20 years or 240 months, and the remaining balance. The changes in the payment, application of interest charges, and balance pay-down are evident when you look at the amortization schedule before and after the rate adjustment:
| Month | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 119 | $870.41 | $418.07 | $452.34 | $153,912.51 |
| 120 | $870.41 | $416.85 | $453.57 | $153,458.95 |
| 121 | $950.27 | $543.50 | $406.77 | $153,052.18 |
| 122 | $950.27 | $542.06 | $408.21 | $152,643.96 |
At the beginning of year 12, the interest rate is adjusted to 5.25% and there are no additional rate or payment changes. The monthly payment becomes $1,030.47 based on the new interest rate, remaining loan term of 19 years or 228 months, and remaining balance of $148,481.49. At the end of the term, the outstanding balance drops to zero:
| Month | Payment | Interest | Principal | Balance |
|---|---|---|---|---|
| 358 | $1,030.47 | $13.41 | $1,017.07 | $2,047.50 |
| 359 | $1,030.47 | $8.96 | $1,021.52 | $1,025.98 |
| 360 | $1,030.47 | $4.49 | $1,025.98 | $0.00 |
You’ll see that ARM amortization adheres to the same concept as the FRM schedule. As the balance is paid down, more and more of the monthly payment is applied to paying down the loan principal. Interest rate adjustments may increase the monthly payment and interest charge but the payoff process remains the same.
