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:
- 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.)
- What year does the 6% line cross $1500?
- 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! - 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%?