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.
- 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! - 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?
- 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!