Let’s look at our mortgage example some more. We already know that 83% of your payment the first year ends up just paying off the interest. The good news is that this number steadily decreases with each payment. This makes sense: You pay the same fixed amount each year, but the loan balance decreases each year. A lower balance means less interest. Less interest, same payment … so more of the money can go to paying off the mortgage balance.
Before we get started, I didn’t leave quite enough room for all of the columns I want. Let’s cut and paste our constants over 1 column:
Once you highlight the cells, cut them, move over a column, and paste. It’s easier to use keyboard shortcuts here: CTRL + X to cut, and CTRL + V to paste.
I also added a Payment column above. This is just copying the one payment constant down for each of our 30 years.
Now let’s add 2 more columns: The interest paid each year, and then the ratio between the two. The ration is the percentage of the payment that goes toward interest. We already know that the first year should be 83%.
Notice that I put a negative sign in front of D2 in the Ratio formula; it’s hard to see. If you leave it out it’s not a big deal, but a ratio of -83% might be confusing.
Notice how the ratio goes down consistently. By the end, just 5.6% of your payment is going toward interest– that’s the 0.056357 number in cell E31. We’ll change the format of this number to be a percentage in a minute. But first, let’s add up how much you paid in total over 30 years, and how much of that went to interest:
SUM is a spreadsheet function that adds any numbers it finds in a range of cells. Excel or Google Sheets will automatically add the close parenthesis for you when you hit enter after you choose the range.
Look at your sums. Over 30 years, you’ve paid $217,950 in total payments, and $117,941 in interest. The difference between those is just over $100,000– that’s the amount of the mortgage principal you paid back! (It’s not exactly $100,000 because we ended up with an over-payment of $8.61 at the end.)
You paid more in interest than the loan value itself! $117,900 in interest for your $100,000 loan. Seems crazy, doesn’t it? This is common for mortgages, where most people can’t afford to pay in cash. Where it’s less of a good idea is with a credit card payment. Whatever you buy on your credit card can easily cost you 2 or 3 times more with interest payments over time. More on this in the next lesson.
The good news is that with lower interest rates, mortgage rates aren’t 6% today. Take a look at Bank of America’s mortgage rates today. Or any bank; I chose Bank of America because they show you the rates without asking for any personal information.
Look at the APR, which builds in all the fees, such as points. We’re not going to get into points here, but basically a point costs you money up front in exchange for a lower interest rate. Pay more now to pay less later.
Here’s what I see. I asked for a $100,000 loan just to match our spreadsheet:
So 3.63%. You can use that, or 3.5% (as I do below) or whatever rate you see today. Before we enter that rate, let’s add some decimal places to our interest rate constant. Otherwise, Excel will round 3.5% to 4%.
With a lower interest rate our payments can be lower. Decrease the annual payment amount in cell G1 until you get to $0 or so after 30 years. What are your total payments now? Do you pay more or less than $100,000 in interest now?
Let’s do 2 more examples. At 6%, we paid $117,000 in interest. At 3.5% or so, we paid under $100,000 in interest. Just for fun, at what interest rate would you pay exactly $100,000 in interest?
You could try different interest rates, but for each one you would then need to change the annual payment amount. That would take a lot of time. But you know something about this case: If you’re paying $100,000 for the house, and $100,000 in interest, your total payments are $200,000.
So let’s change the payment constant so you pay a total of $200,000 over 30 years. The annual payment is simply 200,000 divided by 30:
Then increase the interest rate until you get to that $0 balance after 30 years.
One final example. Remember that in the early years, very little of your payment goes toward paying down the principal. It all goes to paying off that year’s interest. You’re allowed to make extra payments early on, which would go toward principal. (A few mortgages don’t allow for pre-payments. If you find a mortgage that charges a pre-payment penalty, don’t take it!
Let’s say you make an extra payment of $100 in the first year. Just that one payment, and then you just pay the regular amount every year after that. How much does that save you in interest?
I went back to my 6% interest example for this, but you can use any scenario you want.
That’s it– don’t double click to copy the new formula down! Well, you can, but that would be the case where you make an extra $100 payment every year.
How much has the balance at the end increased? Does that seem like a lot?
Here, compound interest is working in our favor, at least on that $100. This is the equivalent to investing $100 at a 6% interest rate. We’re back to the very first lesson! That $100 is going to compound to a much bigger value over 30 years. Those early principal payments can make a big difference.