If I invest $200,000 now at 6%, how much will I have in 10 years? One of my students recently said that if she had a magic spreadsheet that could predict her financial future, this is one question she would ask it.
Some of you probably know that you can calculate this using the formula =200000*(1+.06)^10, but a lot of you have already run away at the mention of the word “formula” and the scary flashbacks to high school algebra it spawns. Come back! You can get Excel to do this for you without formulas, functions, exponents, or any of that. Just addition and multiplication. As a bonus, you’ll watch your future money grow year-by-year.
Here’s the compound interest spreadsheet. We start with the amount and rate as our inputs, named and colored blue to remind us that we can change them. Be sure to create names for them or this won’t work.* We don’t use the number of years as an input, since we’ll be looking year-by-year, and so can see the answer for any number of years we choose.
Now make a year column and an amount column and fill in the first row, year 0 with a starting amount equal to the input of $200,000. Fill in the first column of sequential year numbers. (AutoFill can help here.)
The magic happens in cell B6. If you can tell Excel how to do this using only the previous row and the inputs, you can use it to fill the whole thing. Start with =, then think about how you would figure this manually. The new amount would include the money you had the previous year, so click on the $200,000 just above. But there will be more, so type “+”. You’ll also have the interest earned, which is 6% of the $200,000 from the year before. You multiply to take percentages, so click on the 6%, type * for multiply and click on the $200,000 just above. That’s it. When you enter it, Excel gives you a formula, =B5+Rate*B5, but all you did was think “I’ll have the $200,000 from before plus the interest, 6% of the $200,000 from before.” You did it with no formulas, no cell identifiers.
Now you can tell Excel, “do the same thing” for the rest of the table. Click on the cell B6 that you just entered, grab the lower right corner and drag down to AutoFill. Take it down to 10 years or more. You see that after 10 years you’ll have $358,170 (the same answer you get from the formula, by the way).
I love this technique since you don’t have to memorize any formulas, but just think about what’s changing. I hate memorizing! Even better, you can use this same technique for more complicated problems, such as if you’re withdrawing money each year. Seeing it year-by-year helps you understand what’s going on.
*By the way, if you’re wondering why naming cells should make any difference in how the spreadsheet works, it’s because any named cells automatically become absolute references. That means that when you AutoFill, it always picks up the right number, and not one below it.
Have you used this technique for anything else? Let us know.