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!
- (We already did this one.) How much does your ending cash value change if you increase your return percentage from 6% to 8%?
- 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.)
- 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%?