Calculating Future Value Using Excel

I have received a number of questions over the past year or so on the best way to determine what an investment portfolio may be worth in the future. My response always starts with the same thought: it completely depends on your assumptions.

The assumptions you make with regards to your annual contributions and your investment returns will greatly alter what the future value calculation will return. If you keep that in mind, then you can safely forecast to your hearts content what you might have in the next X years.
The formula you use in Excel is called FV, for future value. To run the calculations, do the following:

1. Open Excel
2. Click on ‘Insert’ in the menu bar and select ‘Function…’
3. Find the FV function by searing for or entering FV and click ok
4. You will see a number of inputs on a little window that opens up. You need to fill in a few of these fields. They are explained below:
i. Rate:This is the rate of return you expect your investments to provide to you. If you expect your investment returns to be 8% enter ’8%’ in this box. Keep in mind, you should estimate what you will earn in a year here.
ii. Nper:Nper is the number of times you will make a payment to your investments. Since we inputted a rate above in years, we should enter our periods in years as well. If you are going to retire in 35 years, enter 35 here.
iii. Pmt:Pmt is the amount of money you are going to contribute in each period you entered above. Again, considering that we inputted a rate above in years, we should enter our payments as a yearly amount. This amount needs to be entered as a negative number – think of it like it is money that you are putting in. So if you do $10 per month, enter -120 here.
iv. PV: PV is the present value of your investments. I leave this blank. Play with it if you want.
v. Type: Type is when you will contribute money to your account – at the beginning or end of the period. I leave this blank. You can play with it to see the impacts but for this example let’s leave it.

Once this is all entered, click ok and you will see the results = $20,678.02 . At an 8% return, the future value of your $120 yearly contributions will be $20,678.02 in 35 years. Not bad for a small amount of money each year. If you earn 15% per year, that amount skyrockets to $105,740.42. I love this function. If anything, it is fun to play with and run different scenarios.