Plain-spoken finance

graphing

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!