An Introduction To Compound Interest With Spreadsheets, Part 1: Getting Started And Defining Compound Interest

Several readers have written me excitedly asking how exactly I do some of the calculations on this site and how compound interest works. Usually, I point them at various online calculators, but the truth of the matter is that a basic understanding of how a spreadsheet works and how compound interest works makes it possible to calculate almost every personal finance situation that you want.

I originally wrote this all as one post, but I’ve since split it up into several, which will be posted in order throughout the weekend. If you found this piece enlightening, check back at the end of the weekend and there will be plenty of interesting things to try out!

Also, be aware that this first entry is intentionally very simple so that people who are unfamiliar with spreadsheets can follow along. If you know the basics of using a spreadsheet and the very basic idea of compound interest, this will probably seem very basic for you. That’s fine; tune in later and we’ll be kicking things up a notch.

Anyway, in the interest of saving money, I’m going to use OpenOffice Calc for these examples. For most things such as this, it is functionally identical to Microsoft Excel, so if you have a copy of Microsoft Office, you can basically follow along with these examples. I attempted to use Google Spreadsheet for examples, but it was quite frustrating to use for repetitive calculations. OpenOffice Calc is free, open source software; you can get it for free at OpenOffice.org.

Got a spreadsheet now? Let’s get started.

What is interest? Interest is the “rent” paid to borrow money. When you put money in the bank, you are essentially lending your money to the bank so that they can use it for various investments. In exchange for that, they pay you a small amount of interest. Similarly, when you use a credit card, you are borrowing money from a bank, and for their investment in you, you must repay that investment with interest.

What is compound interest? Compound interest means simply that rather than directly giving the interest back to you, it is instead added directly to the amount of money you loaned or deposited. After that interest has been added to your balance, your balance is now higher, and thus the new balance earns more interest than before.

Let’s get started and see how this works. Fire up OpenOffice Calc (or Excel). You’ll see the following screen:

Compound Interest 1

This is the default screen for OpenOffice. If you’ve ever used Excel or any other spreadsheet, this should feel familiar; if you haven’t, play around for a bit. You’ll find out that each little rectangle can be filled with information. This is really cool because we can make those rectangles do automatic calculations for us really easily, and those calculations can really illustrate what’s going on with our money.

Compound Interest 2

In the first four cells of column A, I’ve entered a few labels. These will be used to describe the data we’ll put in column B to the right. You’ll see that cell A4 is highlighted (it’s called A4 because it’s in column A and row 4 – look to the left of the row to see the number and to the top of the column to see the letter) and in the lower left there’s a little black square. If you click on that little black square and drag downwards, you’ll see that the spreadsheet will automatically fill in more years for you. Nifty! This auto-fill feature saves a ton of time for doing repetitive calculations as we’ll see in a bit.

We want to do some compound interest calculation, so we need to enter a balance (put this in cell B1, to the right of where we labeled “Balance”) and an interest rate (in B2, just to the right of the “Interest Rate” label). You should be sure to put a percent sign at the end of the interest rate value so the program knows you’re talking about a percentage and you might want to also put a dollar sign in front of the balance amount so the program knows you’re talking about dollars. When you’re done, things will look something like this:

Compound Interest 3

Notice that in cell B4, I’ve also written =B1+B1*B2 … what does that mean? I’m telling the program that in this cell I want to see the value in B1 (the balance) plus the value in B1 multiplied by the value in B2 (the interest). This is the basic interest calculation: we want to see the original balance (B1) plus the interest earned a year (B1*B2). If you type in =B1+B1*B2 into that cell and hit enter, you’ll see the result of the calculation: $10,500.00 in this case.

Here’s the neat part, and this will hopefully begin to show you the power of a spreadsheet. Go back up to B1 and change the balance to something different. As soon as you do that, the calculated balance in B4 automatically changes! You can change the interest rate in B2 as well. Throw in all kinds of numbers to see what happens.

Compound Interest 4

Now we’re going to see what happens in year two. This time, in cell B5, we’re going to enter the formula =B4+B4*$B$2 … that’s a bit different than before. Just bear with me for a minute. When you enter that formula, you’ll see that it takes the value in B4 and uses that as the initial balance to calculate the second year’s worth of compound interest, giving you a new balance of $11,025.00 in this example.

Now, click on B5 and then click and drag down on that black square in the lower right, all the way down to the label for Year 20. When you let go of the mouse button, you’ll see that the calculation for all of the years is done. This is why we did the $B$2 part; the program knew to automatically change the balance each time, but we wanted to tell it to always point back to B2 for the interest value no matter what. You can jump back up to the top and play with the interest rate and the balance to see compound interest at work. You can keep dragging down for as far as you wish, but after a while you start looking at numbers that aren’t really feasible for your lifetime.

Compound Interest 5

If you want to see how much interest is actually earned each year with compound interest, just start another column off to the right as pictured above. In the Year 1 row in cell C4, enter the formula =B4-B1 to see how much interest was earned in year one…

Compound Interest 6

… and then enter =B5-B4 in cell C5 to calculate how much is earned in year two. Once you’ve done year two, click on cell C5 and then drag that black square in the lower right of the cell down again to automatically calculate the earnings for all of the other years. See how that money grows? That’s the power of compound interest.

Compound Interest 7

Next, we’ll tackle the idea of how monthly compound interest works, learn what the difference is between APRs and APYs, and see how that affects you.

If you enjoyed reading this, sign up for free updates!

Loading Disqus Comments ...
Loading Facebook Comments ...
  1. john says:

    Great simple example. I’ve build so many compound interest tables over the years it’s almost an automatic process for me now.

    You probably already know this, but for other OpenOffice.org or NeoOffice (the OOo suite for Mac OS X) users, instead of typing in the cell reference (for example, B2), you can click on the target cell instead to add it to the formula list. Then, after clicking, hitting [Shift]-F4 (in Linux, at least) will make the last clicked cell reference static (the $B$2 format) so you can now autofill as above. Hitting [Shift]-F4 a few more times will make either the row static (B$2) or the column static ($B2) or bring it back to relative (B2).

  2. Daniel says:

    Thank you! This is exactly what I needed!

  3. Ron crimmins says:

    thasnks it was very helpfull there are lots of how to’s out there but no explation of the meaning or why it might be thanks again will now look at the rest of your how to’s

  4. S. Lewis says:

    Do you have a compounded interest calculator that allows a person to enter a set amount every month to a compounded interest account and determine the amount that will be saved after a specific period of time?

  5. Mrs. Micah says:

    Thanks, Trent. I’m learning to do fancier stuff in Excel for work purposes and this tutorial was a good jumping off point.

    I use Calc at home and save everything as .xls, so it translates to Excel as well. :-)

  6. crash course says:

    Thank you, I’ve been messing about with excel for over an hour… and hadn’t made much progress. Thank you for pointing me in the right direction.

  7. Bogdan says:

    I liked your article very much and I can’t wait for the monthly compound interest.

    I’d like you to write a series of articles regarding: bank interests for economy accounts, how to calculate the real prices that go when taking a credit for a house (the PMT formula and other tricks). Basically I want to understand my own finances better.

    Thanks again.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>