Thursday, October 10, 2013

De-Mystifying the Mortgage Amortization Schedule

Every month you know you've got to make that pesky mortgage payment. Say it's $1,000. You hate seeing that money disappear from your bank account, but at least you know that you've just reduced your debt by $1,000, right? Wrong. Or, I guess I should say, mostly wrong.

When you make your mortgage payment, it's typically a fixed amount every month. But that whole amount is not reducing your debt. In fact,
in the beginning, most of it is used to pay interest on your loan (is this depressing, or what?).

This is a concept, that, to me at least, is easier to understand when you see the mechanics of how your interest expense is actually calculated. And we do this on something called an amortization schedule. To amortize basically means to gradually pay off a debt (as opposed to doing it in one lump sum). So banks use amortization schedules to see how much of your original debt you owe at a given time. I know, I know, it sounds like something you don't need to know, but I think it's a good thing to understand for anyone who ever plans on making a fixed loan payment in their lifetime. If you can do multiplication, addition, and subtraction, you can understand an amortization schedule. Then the whole concept will just stick better (at least in my opinion).

Plus, if you are an uber-nerd, amortization schedules are oddly satisfying to create (if you happen to be writing a blog post on amortization schedules that no one requested, just because you wanted to).

So let's walk through the steps. Click here to download a sample amortization schedule if you want to follow along in Excel.

First, set up the schedule with some helpful column headings.

Let's keep things organized by putting in columns for the payment number and due date. We'll need the interest rate (I've used 5 percent divided by 12, since they are monthly payments). Let's also put in columns for the items we'll calculate.

  • Beginning Principal Balance: This is the amount you owe before you make the payment for this row.
  • Fixed Monthly Payment: Kind of self-explanatory-- this is the amount of the payment you make every month. The amount is the same for every payment. The bank calculates it based on the initial amount of the loan you take out, the interest rate, and the number of payments you'll make. That's why it's usually a funky number.
  • Interest Expense: This is how much interest you owe the bank that month. Interest expense is calculated by taking the amount owed and multiplying it by the interest rate. So each month the bank might say something like, "Well, you took out a loan for $100,000 a while back. But by the start of this month you only owed $80,000. You owed that amount for the whole month, so we're going to charge you interest on that $80,000. When we calculate your interest expense, we'll multiply that $80,000 by your interest rate, which is the annual interest rate divided by twelve (since in this example we're doing monthly payments). This way we'll only charge you interest for just a month's time. Since interest expense is calculated by multiplying the balance owed times the interest rate, as you continue to pay off the loan, your interest expense will decrease."
  • Principal Payment: Say your fixed payment is $1,000, but your interest expense for the month is "only" $300. What happens to the other $700? It goes towards paying off your debt. As you continue to pay down your principal, the interest expense keeps getting smaller, and this leftover portion will keep getting bigger.
  • Ending Principal Balance: Now comes the rewarding part. Take the total amount you owed before you made the payment, and subtract out the part you get to apply to the principal. This is your new amount of remaining debt. It will become the "Beginning Principal Balance" for the next payment. A correctly built amortization schedule will have the last payment bringing the ending principal balance down to zero. Woohoo!

To recap, using numbers from our sample amortization schedule...

Note that I'm using 0.42% as my interest rate. I picked an annual rate of 5%, but since we only want to figure out interest expense for one month, I divided 5% by 12. If you used 5% for monthly payments, you'd be using an annual rate of 60%!! Yikes!!

Please tell us-- This is one of our more technical posts. Let me know if I need to clarify parts in the comments, and I'll do my best!


  1. Excel is a magical thing. Love having the downloadable one to follow along. Thanks!

  2. This comment has been removed by a blog administrator.


Related Posts Plugin for WordPress, Blogger...