Planning with a Mortgage Amortization Schedule

Disclosure: This article is written for entertainment purposes only and should not be construed as financial or any other type of professional advice.

I’ve paid off my mortgage for now. But I’ve considered buying a small second home near biking trails or moving to a new home in a neighborhood with grocery stores, restaurants, etc. within walking distance. There’s a possibility that I might take out a mortgage for one of these purchases.

When I consider a mortgage, I immediately think of creating a mortgage amortization schedule. This tool can be useful for:

• comparing my loan-balance calculations to the mortgage company’s records
• identifying when mortgage insurance should no longer be required
• planning the payoff of the mortgage prior to retirement or another significant life change
• recognizing how much of the payment goes to insurance, taxes, etc. and will continue even after the mortgage is paid
• analyzing the impact of extra payments (and later comparing this impact to the benefit of using those funds for a purpose other than mortgage payoff)
• comparing the original schedule to a potential refinance for a lower rate or shorter term

As I mentioned in my article on personal finance spreadsheets, using online calculators is cool but designing my own spreadsheets is way cooler. I love to be able to create a model to see how a financial concept works. Plus I like to apply this model to my personal situation.

My Downloadable Mortgage Amortization Schedule

I put together a downloadable mortgage amortization schedule based on a 30-year, fixed-rate mortgage. This spreadsheet requires just five inputs:

• home value
• loan amount
• interest rate
• monthly mortgage insurance (if any)
• escrow amount monthly

Optional inputs are extra payments made in any amount in any month. (This option is not an encouragement to pay extra on a mortgage loan. It illustrates the benefit of paying extra in order to pay less interest and accelerate the mortgage payoff. Paying more toward the mortgage generally means forgoing the opportunity to spend or invest elsewhere.)

This schedule shows how payments are applied to paying down the loan over time. It also models how and when private mortgage insurance is avoided and/or falls off the monthly payments (if the insurance is the type that disappears after the loan balance falls to 80% of the original home value).

Note that the mortgage payment is comprised of three parts: 1) principal and interest or P&I; 2) mortgage insurance; and 3) escrow amounts for items like homeowners’ insurance and property taxes. The principal and interest (P&I) portion of the payment is fixed throughout the 30-year term of the loan with a fixed-rate mortgage. Figuring the P&I is straightforward, especially when using the payment function (PMT).

Adding escrow amounts and mortgage insurance complicates the construction of the mortgage amortization schedule. They increase the monthly payment. But, technically, they don’t affect the amortization process. These can be omitted but can be useful to get the full picture regarding the monthly mortgage obligation. By understanding upfront what you’re required to pay monthly, you can avoid budgeting surprises that could lead to regret and even foreclosure. In addition, by maintaining your own schedule, you can compare your mortgage servicing company’s numbers to your own and correct mistakes more easily.

Here’s the link to the mortgage amortization schedule. Note: Currently, you can download the spreadsheet to Excel or go to File > Copy to make a copy of the spreadsheet with a Google account; I don’t allow editing access in order to maintain the integrity of the original file.

Do you find looking at amortization schedules useful when making a financial decision? How has this helped?