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.

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

[…] ← Compound Interest- The Excel Future Value (FV) Function […]

[…] more important. Second, you can get your portfolio jumpstarted. As we saw in a recent post on compound interest, the early years of saving are the most important because they lend more time to compounding. […]

This is a nice web site that has all the calculators you can think of

Oops, here it is

http://www.easysurf.cc/vfpt2.htm

Very cool. I’ll add that onto the recommended websites page.

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.

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!

So neat to be able to do this. But you have a typo:

“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.”

8% return will yield $1.6M according to my calculator.

I probably meant 5% real with a 3% inflation adjustment and didn’t explain it well.

FV, one of the financial functions, calculates the future value of an investment based on a constant interest rate.

Thank you for the useful article.

When I run your formula in excel, I get the same answer. However, when I run it in an online “compounding interest” calculator, I get a different result (913,705.02 in both cases when I tried to run it, rather than the $880,485.59 you came to)

I ran this on 2 separate sites (see below links), so I’m curious as to how the forumula from the online calculator is different from Excel. Any idea on this?

So you want me to trouble shoot the calculators on MoneyChimp and Dave Ramsey? 🙂

I suspect it comes down to when the contributions are made- the beginning of the period or the end.

So let’s try these calculators. Dave’s uses monthly contributions instead of annual, so that’s a pain. So let’s just take the ongoing contributions out of it. If I put in a $100K present value, 5% annual return, no contributions, and a 10 year period, it spits out $162,889.47.

On Moneychimp, those same factors give me $162,889.46. So within a penny.

On Excel, I get $162,889.46. =FV(5%,10,0,-100000)

So they all seem to work just fine. Not sure what happened to you.

We can try it with annual contributions. Let’s just use the moneychimp one since that gives us that option.

Let’s say 5%, 10 years, 100K now, and $10K a year at the beginning of each year.

On MoneyChimp: $294,957.33.

On Excel: =FV(5%,10,-10000,-100000,1) = $294,957.33

Now, let’s put the new contributions in at the end of the period.

On Moneychimp: $288,668.39

On Excel: =FV(5%,10,-10000,-100000,0) = $288,668.39

I dunno. The calculators seem to be working fine for me. Maybe pilot error?

Thanks for the response on thoughts. Yes it does look like you nailed it, the difference with the online calculators vs the formula from the post is that the online calculators are using payment at the beginning of each period. (and as you also pointed out, this can also easily be done in excel by adjusting the formula)

Thanks so much!

Jake

Here are the links

http://www.daveramsey.com/blog/investment-calculator#/advanced_entry_form

http://www.moneychimp.com/calculator/compound_interest_calculator.htm