Compound Interest- The Excel Future Value (FV) Function

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, doing this is as easy as opening up excel and using a simple function- the future value formula. Here’s how to do it:

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.) Click on the formulas tab, then the financial tab. Go down the list to FV and click on it. A box will pop up with five values you’ll need to fill in. 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. 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. 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. 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. 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(8%,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.

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.

 

Enter Your Mail Address

image_pdfimage_print


Comments

Compound Interest- The Excel Future Value (FV) Function — 7 Comments

  1. Pingback: Expected Returns | The White Coat Investor

  2. Pingback: Live Like A Resident | The White Coat Investor

  3. When dealing with Future Value calculations and retirement planning (or any financial planning for that matter), one must be very careful when assuming the annual rate of return as that rate is used – as this is assumed each and every year.

    For better or for worse, the market is serial in nature and, as a result, an investorr may have several years of positive or negative returns. This will not be reflected in the calculation, causing one to think that they may have a lot more money at retirement.

    For example, if I assumed a 35 year old invested a lump sum of $100,000 at 10% compounded annually for 30 years, the future value would be $1,744,940.

    However, if I took that same $100,000 and replaced the 10% rate of return with a -20% in any one year, the future value would drop to $1,269,047.

    A loss of over $475,000 due to one bad year. I don’t think most people understand just how much volatility can impact the end result when they make these assumptions.

  4. Yes, the sequence of returns is very important to the final amount. Ideally, you would have very high returns for the 5 years before retirement and 10 years after. The worst case scenario is starting a decade long bear market on the eve of retirement.

    It’s important to understand the limitations of a calculation like this, but far too many people don’t make the calculation at all!

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>