FV Function - Get the Future Value in Excel

Add to Favorites
Author:

The Future Value function (FV) in Excel will return the future value of an investment based on a particular interest rate.  The future value of the investment can be calculated when there is a single lump sum payment, a series of payments, or a lump sum payment with a series of payments.

This is a corner stone of many financial calculations and it is easy to use in Excel.

Syntax

FV(rate,nper,pmt,[pv],[type])

Argument

Description

Rate

The interest rate used per period to calculate the future value.  Make sure this rate is per period; if the rate is 5% but there are two periods per year, the number for this argument would be 2.5%.

Nper

The number of periods used for the calculation.  If each year is broken into two periods and you calculate the FV for 5 years, this number would be 10.

Pmt

This is the payment that is made each period.  This amount cannot change when using the FV function. This argument is actually optional, but only if you include the PV argument in order to calculate the value of a lump-sum in the future - an example for this will be shown below.

[Pv]

Optional argument. This is the Present Value argument and it is the current value of the investment.  If you leave this empty or use a 0 for it, you must put something into the Pmt argument.

[Type]

Optional argument. This tells the FV function when the payment in the Pmt argument will be made, either 0, for the end of the period, or 1, for the beginning of the period.  If you leave this argument empty, Excel will assume it is 0, or that payments are made at the end of each period.

[] means the argument is optional.

Now that you can see the syntax, let's look at some examples.

FV Function Examples

Below is a screenshot of a simple Future Value function calculation in Excel.  Make sure to download the accompanying workbook in order to follow along.

Above you can see that we calculated a future value of $32,944.64 for an investment that pays 4.25% per period for 16 periods while at the same time $1,000 is invested each period and where we started out with $5,000.  Since the Type is 1, this means we decided to make payments at the beginning of each period instead of the end.

Example 1 - Note the Negative Values

Let's look at what we need to create the FV function correctly.

Now that the actual function is selected, in cell B8, we can see that there are a few things that stand out.

There are negative signs in front of the Pmt and Pv arguments and this is for a reason.  Due to the mathematical equation used to calculate the future value, if we do not put a negative sign in front of the payment and present value arguments, the result of the FV function will be negative, like this:

The result is still the same, just negative.  However, when you use this result in other calculations, such as when you nest the FV function, it is very important to get this as a positive number so that you do not mess-up other parts of your spreadsheet or confuse people who see a bright red cell and don't understand why a future value would be negative.  So, it is good practice to put the negative signs in front of the Pmt and Pv arguments to keep the result as a positive number.

Example 2 - Payments without a Lump Sum

This example shows how to use the FV function to calculate the future value of a series of payments where there is no lump sum.  This means there will be no value for the present value, or pv, argument.

Here, I typed in 0 for the Present Value argument in cell B5.

You could also enter the function this way in cell B8:

=FV(B2,B3,-B4,,B6)

Or you could type 0 for the argument in the function like this:

=FV(B2,B3,-B4,0,B6)

If you wanted to leave the Type argument blank, you could also enter the function this way:

=FV(B2,B3,-B4)

In this case, the future value will be a little lower since the default value for the Type argument is 0, which means the payment will be made at the end of the period, which means there will be less time for the money to earn interest  (remember that we put 1 in for the type argument in previous examples).

Example 3 - Future Value of a Lump Sum

This example will calculate the future value of a lump sum when there are no payments to be made.  This means that we will leave the Pmt argument empty or put a 0 in for it; but, you must also remember to put a value in for the Pv argument.

I changed the value in cell B4 to 0, but we can also change the function to look like this if we want:

=FV(B2,B3,,-B5,B6)

Or, put a 0 into the function instead of leaving it blank:

=FV(B2,B3,0,-B5,B6)

Here, the type argument is meaningless since it only refers to when payments are made (remember no payments are made in this example), so we can also remove that and are then left with a function like this:

=FV(B2,B3,0,-B5)

Note on the Periods

When you are calculating the future value of anything, you need to make sure that you keep the period calculations consistent.

If there are two periods in a year but you have an interest rate that is a yearly rate, you need to divide that rate by two so that you have the correct rate for the period.

The Nper argument is not the total number of years for an investment but the total number of periods.  So, if there are two periods per year and you have an investment time of 5 years, you need to put 10 in for the Nper argument because, in this case, there are 10 periods in 5 years (5 years*2 periods = 10 total periods).

Also, remember that the Pmt argument refers to the payment per period and not a payment per year.

Conclusion

The FV function is a great function to calculate the future value of an investment but it is rather simple.  Make sure to memorize how this function works and how to use it well so that you can later build more complex financial models with it.


Excel Function: FV()
Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Excel Function: FV()
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course