Plain-spoken finance

compounding

by

Financial Literacy #6: A Focus on Mortgages

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.

by

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

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.

by

Financial Literacy #4: Adding money while compounding

So far, we’ve started with a fixed amount of money and let it compound. In real life, you’re likely adding more money each year. But now we have 2 things going on: we’re adding money while the money that is already there is compounding.

This is critical to get to the next section, where we’ll look at mortgages and credit cards and student loans. With those, the adding and the compounding are fighting each other: The amount borrowed compounds (which is bad) while you pay it down (which is good.)

Here, there’s no fight: Adding to compounding money just makes it grow faster! It’s all good. We’re going to see some pretty big numbers. Let’s get started!

First let’s add a column with the number of years in it. Instead of having to remember that year 50 is in row 51, we can look for our 50th year by the number 50. Enter a header and then the first 2 values on your own. Then drag down to let Excel or Google Sheets do the rest:

Make sure you highlight both the 1 and the 2 to let Excel figure out the pattern. Drag down until the last value is 50, in cell A51.

Next, let’s add in our usual data, growing $100 at 6% annual interest:

I used F4 to add the dollar signs for $F$1, but typing them is fine too.

Now to add in the new money. Let’s make a new constant value next to the 6%; this is going to be the new money we’re adding every year. Modify the formula in B2 to add this value in, again remembering to add the dollar signs. Then double click to fill this down to the rest of the cells.

We used to get to $1842 after 50 years. Now you should have … $30,875! Not bad for adding $100 year, which is a total of $5,000 after 50 years. (Actually, we added money at the end too, so we added $5,100.)

Check to make sure everything is working by changing cell G1 to 0. Now you’re adding $0 every year, and you should once again end at $1,842 after 50 years. Now change it back to 100.

Let’s get a graph in here too. Add another data series in column C, with our usual $100 at 6% growth. You can use the same 6% value in F1:

You should see the old $1,842 value at the bottom of column C.

One last step before we use this spreadsheet to answer some questions: add a graph. You could use the CTRL + A trick to highlight the data, but this will also grab column A. Instead, click on the column headers for columns B and C, which are the gray B and C values above those columns. Click and drag to highlight both:

There’s our graph showing the huge difference between adding money once and adding it every year for 50 years!

Now for some questions you can answer by using your spreadsheet. We didn’t label which line is which, so remember the colors. For my example above, the first dataset (adding money every year) is blue, and the second dataset (only adding money at the beginning) is red.

  1. How much money do you need to add at the beginning (second data set) to roughly match what you get by adding money every year? That is, increase the value in cell C1 until the graphs end up in the same place.
    Remember that we added around $5,000 total over 50 years when we added every year. Note that if you add the money all at once, it can compound for a much longer time. So you don’t need as much to start with!
  2. Raise the interest rate from 6% to 9%. Now how much money do you need to start with to match the $100 a year ($5,000 total) ending value? Is it less or more than in question #1? Does this make sense?
  3. Susie Orman claims that you waste a million dollars if you buy coffee every day. Here are her assumptions: Assume you save that $100 per month money instead. So add $1,200 a year to an account. (You’ll have to change both the value in B1 to $1,200 as well as the value in G1 to $1,200.)
    She claims that after 40 years you’d have a million dollars. What interest rate is she assuming? Increase the 6% until you get close to $1,000,000 after 40 years.
    To make the large numbers easier to read, highlight columns B and C and change the format to dollars:

To change to a currency format in Google sheets, highlight both columns and select Format–>Number–>Currency.

Check this article to see if you got around the same interest rate. Our money won’t compound quite as fast as hers, since adding $100 a month is a little better than adding $1,200 at the end of the year. Adding each month gives the money more time to grow!

Things are getting more complex … e-mail me if you have any problems!

One you have it figured out, head on to our first examples of debt in lesson #5!

by

Financial Literacy #3: Graphing Compound Interest

The numbers we’re looking at are getting big quickly. Well, not quickly: it’s taking 50 years to turn $100 into $1842. I think it’s easier to understand what’s happening if you look at a graph. And it’s more fun to look at a graph comparing two different curves.

We’ll start with the same data we looked at in the last lesson: $100 invested at a 6% annual rate. The only small difference is that I’m going to put the 6% further off to the right, to leave room for our second data set:

Don’t forget the dollar signs on the $E$1, or whatever cell you used; column C or D also would have been fine.

Now we’re going to do the exact same thing, just a column over. And with a different percentage; let’s start with 8%:

Notice I used a trick there to fill in the other 49 cells down. When the pointer changes to a plus, you could drag down like we did before. But all I did was double-click, and Excel filled in the cells.

Here’s the key: This only works when you already have data in the column to the left. If you tried to double click in column A, nothing would happen; Excel doesn’t know how far down to fill in data, so it doesn’t do anything. Once we have column A down to cell A51, Excel knows you want to also fill column B to B51.

Make sure you have the correct values in row 51: 1842 (maybe plus some decimal places) in A51, and 4690 or so in B51. Now it’s time to graph.

First select the data you want to graph, which is all of the data in column A and column B. I used a keyboard shortcut here. Click on cell A1, or just any cell in column A or B with data in it. Then hit Ctrl+A. That is, hold down the Control (Ctrl) key and press A while still holding down Ctrl. (If you’re on a Mac, use Command + A.)

Once the data is selected, scroll back to the top. You don’t have to scroll back up, but if you don’t Excel will stick your chart at the bottom of the page. I used my middle mouse wheel, but you can also drag the slider on the right side of the page. Then click on the Insert menu at the top and then first click 2D line graph option:

I’m using an older version of Excel: 2010. The menu options look a little different on Excel 2016:

Google Sheets is going to look a little different when you’re making the graph, but it’s the same process (including the Ctrl +A):

Now that you have a graph, we can use it to answer some questions:

  1. The 8% return compounds to $4690 after 50 years. What year does it cross $1500? (Hover your mouse over the data point on the higher line closest to $1500 and it will show you the row value of that point.)
  2. What year does the 6% line cross $1500?
  3. Change the 8% value to 10%. How much money do you end up with now after 50 years?
    Take a look at the graph for 6% vs. 10%. The difference between 6% and 10% doesn’t sound like much, but look how big the return difference is!
  4. Some mutual funds charge a 1% annual management fee. That would lower a 7% return to a 6% return. How much less money would you have after 50 years at the 6% rate vs. 7%?

On to Financial Literacy Lesson #4!

by

Financial Literacy #2: More Compound Interest

In the first lesson, I (and hopefully you!) created a simple spreadsheet to calculate a 6% return on $100. Now we’re going to make that more flexible, and graph our results.

The first question we’ll answer: How much more money will you have after 50 years with a 6% annual return vs. 8%? (Take a guess!)

Open up a new spreadsheet. Put your $100 in a cell, and then put 6% off to the side a bit. We’re going to set this up so we can change the 6% to something else and see the results immediately.

Last time, our formula looked like this: =A1*1.06

Now, instead of using the fixed value of 1.06, we’re going to use the value in cell C1 (or wherever you put the 6%):

Now the formula looks like this: =A1*(1+C1)

But that won’t work if you drag it down. Try it! Excel will change the C1 to C2 (and then C3) as you drag down. But there’s nothing in C2, so Excel will treat it as a 0. That’s gives us 6% the first year and 0% every future year. That’s not what we want!

The answer is to put dollar signs in front of the C2, and make it $C$2. That tells Excel to fix those values, and to not update them as you drag down. (You can also put your cursor on the text “C1” in your formula and press F4. But just entering the dollar signs yourself works fine.)

Look for the formula bar above your spreadsheet cells, and change the formula in cell A2:

Now we can drag down the cell with 106 in it. Drag down to row 51, to give us 50 years of compounding at 6%.

Cell A51 should have a value of 1842.015. (Or just 1842 is fine too.)

Remember that value: 1842. Now go up to the top and change the 6% to 8%. Go back down to A51. How much money do you have after 50 years at 8%? Is it more or less than twice the amount you had at 6%?

Let’s make one more edit to our spreadsheet and then use it to answer a few questions. We’ll need to see more than one decimal place in the interest rate. Instead of just 6%, we need to see 6.41%.

Here are 2 ways to do that in Excel. For both, you should click on the cell with the 6% in it.

Option 1: Make sure you’re on the “Home” ribbon up top, and click the decimal place button in the Number section:

Option 2: Click your right mouse button on the cell. Choose Format and then select 2 decimal places:

The 2 options are similar in Google Sheets, though instead of right clicking, you need to choose the “Format” menu at the top. Here I do both methods:

Okay. Now we can use our spreadsheet to answer some questions!

  1. (We already did this one.) How much does your ending cash value change if you increase your return percentage from 6% to 8%?
  2. What interest rate do you need in order to end with about $10,000? (Change the value of your interest rate up and down until you’re within $100 or so of $10,000 in the last cell, A51.)
  3. Interest rates are very low right now. What interest rate is required for you to at least double your money in 50 years? (So, your $100 becomes $200.)

Once you answer #3, check interest rates at Goldman Sachs and Capital One (scroll down to see the interest rate with Capital One).

Feel free to play around with your spreadsheet. What should happen if you plug in an interest rate of 0%? What about 100%?

Here’s Lesson 3!

by

Financial Literacy #1: Compound Interest

All of finance is compound interest.

This is, of course, an exaggeration. But only slightly. So much of what you need to know involves how money compounds. If it’s your money, compounding is very good. If it’s money you owe to someone else, it’s very bad.

The concept of compound interest here is simple: If you earn interest on your money, that interest you earn can earn interest in the future.

I’ll give you one basic example, but then you have to play with the numbers on your own to truly understand what’s happening here. Suppose you have $100 in a bank account. You never do anything with this money– it just sits there. The bank pays you interest on this money.

Interest rates are very low right now, and some banks pay almost nothing. Even the highest interest savings accounts only pay 1.70% per year these days. But let’s pretend we’re back in the year 1995 when you could earn 6% on your money.

After a year, your $100 is now $106– the original $100 plus 6% of $100, or an extra $6.

By the way, the bank pays you this money as an incentive for you to lend them your money. They take that money and lend it out to businesses or people buying houses, and at a rate higher than 6%. As long as everyone doesn’t ask for their money back at the same time, the system usually works!

Now you have $106 to start year 2. You get 6% interest on $106 now, or $6.36 in interest. Last year you got an extra $6, and this year you got an extra $6.36. The following years you’ll get $6.74, and then $7.15, and then $7.57, and so on. That’s compound interest.

It doesn’t seem so exciting here. But if you’re young, and leave the money there for say 50 years, your original $100 is now worth $1,842. And in that 50th year, you earn $104 in interest. The interest payment that year is more than your original deposit!

Okay, your turn. Open up a new spreadsheet. If you have Microsoft Excel, use that. Or you can use Google Sheets, which is free; you only need to sign up for a Google account. I’ll show examples for both.

You can also use a free version of Excel online. It’s not as fancy as Office 365 or the desktop versions of Excel, but it will work just fine for this class.

Here’s how to do our 6% example in Excel:

(You may or may not get any decimal places … we’ll fix that later.)

Notice that after I type the equal sign in the second cell, I click on cell A1, and Excel fills in “A1”. I also could have just typed “A1”.

Also, I multiply each cell by 106%. I want to take all of what I had before (100%) and add an extra 6% in interest. That’s a total of 106%. I also could have typed 1.06: =A1*1.06

Finally, I drag the formula down. Notice that the pointer changes to a black plus. You only get this if you’re right at the corner of a cell:

It’s the same process in Google Sheets:

I did both of these for 25 years … well, 24 years of compounding. Notice in that last year we go from $382 to $405– $23 in earned interest on your original $100 deposit!

Please make sure you can do the above spreadsheet work before continuing on. We’re going to build on this and make more complex spreadsheets. We’ll look at how changing the interest rate even a little bit can have a big impact on the final value.

If you’re having trouble, e-mail me!

We’ll also look at some examples of common compounding examples. Like how investing $100 a month can turn into over $500,000 in 50 years. Or how skipping a $3 coffee every day can turn into $1 million after 40 years. And how those statements together can’t both be true! (Think about it: $3 a day is less than $100 a month, and 40 years is less than 50 years.)

And of course, we’ll look at actual investment returns, where making a steady 6% per year isn’t how it really works.

Here’s the next lesson!