mortgage

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.

So far, all of our numbers have been positive. I’ve assumed you have money in the bank (positive!), and you’re earning interest (positive!). But now we’re going to get into negative values, which will represent money you owe to someone else.

I’m going to use a mortgage as an example. You borrow money from a bank to buy a house, and you need to pay that money back. Other types of debt are similar, but they aren’t quite as simple.

For example, a student loan works the same way, though with some types of student loans you aren’t charged any interest while you’re still in school. And credit card debt is also similar, except you can add to the debt in small pieces over time. We’ll cover both of these in more detail later on.

Back to our mortgage. Let’s say to take out a mortgage for $100,000 to buy a house. And let’s stick with our 6% interest rate. But this is now interest you owe the bank!

You are fighting against this interest. The interest is bringing the balance up, so making the negative number more negative. You are making payments to offset this interest, plus a little more so the amount you owe goes down.

Let’s start our spreadsheet as we have before. Start with -100,000 (negative!) at a 6% interest rate. Let’s only go out 30 years, since most mortgages are 30 years or less.

Highlight all of column B and make it a currency, just to make the numbers easier to understand:

The brackets are a fancy financial way of representing a negative number.

We just created a mortgage … where you never make any payments! That doesn’t really work. The bank would take your house before well before 30 years. Let’s add in some payments. Payments are going to be positive to offset the loan amount. Let’s plug in a $5000 per year payment to begin with:

Now the number is still getting more negative, but not quite as quickly. This doesn’t work: with a 30-year mortgage you spend 30 years paying off the mortgage balance, and so it needs to get to $0 after 30 years.

Increase the value in cell F1 until the balance is roughly 0 after 30 years. How much do you need to pay every year?

We’ll get to this more in the next lesson. But think about the first year. You’re paying $6,000 in interest. So the first $6,000 of your payment goes to offsetting that interest. Whatever is left pays down the original loan. So the payment needs to be more than $6,000.

I came up with an annual payment (the value in F1) of about $7,265. (There are formulas to calculate the actual value without plugging in numbers, but we won’t cover that here.)

So the first year $6,000 of your payment goes toward interest, and the remaining $1,265 goes to pay down the original loan. About 83% of your first-year payment is fighting off that compounding interest! Actually, you keep it from compounding, since you pay it before it’s added to the next year’s balance.

That’s why the image for this lesson seems so apt: you are fighting the interest and slowly pushing that loan balance back to zero.

I’ve simplified mortgages a whole lot here. In reality, there are lots of complications:

You pay a mortgage monthly, and interest is calculated monthly.
You generally need to make a down payment of around 20% of the loan value.
You need to pay for insurance on the house, and insurance on the mortgage. These are often added to your monthly payments.

But the numbers we came up with match pretty closely to a real mortgage. I went here and calculated the monthly payment for a $100,000 30-year loan at 6%:

$599.55 a month is $7,195 a year. I came up with $7,265 on our spreadsheet. We’re off by $70 or so a year, or less than 1%. Not bad for using annual instead of monthly numbers. Try other numbers in your spreadsheet and see how they compare!

And save the spreadsheet– we’ll use it as the starting point for the next lesson as well. Here’s Lesson #6 with more details about mortgages.

Financial Literacy #6: A Focus on Mortgages

Financial Literacy #5: Mortgages, Credit Cards, and Student Loans