Albert Einstein’s “most powerful force in the universe” is an important financial concept for the investor to understand. Being able to calculate out the future value of an investment after years of compounding will help you to make goals and measure your progress toward them. Fortunately, calculating compound interest is as easy as opening up excel and using a simple function- the future value formula.

How To Calculate Compound Interest Using The Excel Future Value (FV) Function

  1. Open Excel (I’m using 2007, but other versions are similar. You can download the free Open Office spreadsheet if you don’t have excel.) [Update: Steps are the same with Excel 2016 – Ed]
  2. Click on the formulas tab, then the financial tab.
  3. Go down the list to FV and click on it.
  4. A box will pop up with five values you’ll need to fill in.
  5. The first is the RATE (aka interest rate or rate of return). Usually, you can just put in an annual rate of return, such as 5% here. If you want to do things on a monthly basis, put in 5%/12.
  6. The next box is NPER, or the number of periods such as years or months. If you used an annual rate above, put in the number of years, such as 30. If a monthly rate, put in the number of months in the period.
  7. The next box is PMT or the amount you’ll put into the investment on a regular basis each year. Any amount going into the investment should be a negative number, and any amount coming out of the investment should be positive, so if you’re going to invest $10,000 a year, put in -10000.
  8. The fourth box is PV or present value- the amount you already have in the investment. Let’s say you already have $50,000, so you put in -50000.
  9. The last box is TYPE or the type of payment. If you’re putting the money in at the beginning of the year, put in 1. If at the end, put 0 (the default option).

So, in our example, the function would end up looking like this: =FV(5%,30,-10000,-50000,0) and the answer would be about $880,485. So if you have $50,000 now, and if you added $10,000 to it at the end of each year for 30 years, and if you earned 5% a year on it, you would end up with $880,485.

calculate compound interest excel

Playing With the Variables

Now, once you’ve figured out how to do this, you can play with the variables a little. Say you earn 7% a year instead of 5%. How much more do you get? You’d have $1.33 Million, or about $445,000 more. What if you put in $15,000 a year instead of $10,000 a year? That would earn you $332,000 more. What if you invested that $10,000 at the beginning of each year instead of the end? That would get you about $33,000 more. What if you invested for 35 years instead of just 30? $299,000 more.

Isn’t that fun? You’ll quickly realize that just a few more years of compounding or just a little higher return can make a huge difference. You also learn that in the beginning it matters much more how much you are adding to the investment (your savings rate) and in later years your rate of return matters much more. You can also see how important it is to get started as soon as possible, to maximize the years of compounding.

What do you think? Have you found this function to be helpful? Why or why not? Comment below!