The Excel FV Function is one of the more powerful financial functions within Excel. It let’s us calculate the future value of an investment based on entering a series of inputs.
In this simple guide, we are going to run through how to use the Excel FV function. We will cover:
- The Excel FV formula
- Breakdowns of each input
- An example
Let’s get started with the formula itself.
The Excel FV formula
The formula for the Excel FV function has quite a few components, some of which are optional. The formula is as follows:
=FV(rate,nper,pmt,[pv],[type])
Before we move on to examples, let’s first break down each element of this input as there is a bit going on, and as mentioned not every single one of these inputs actually needs to be used.
rate: This is the overall rate per period. If we have a yearly interest rate for example it would be that number divided by 12 if we had monthly payments or investments.
nper: The total number of payments overall for the entire period we are calculating. For example if we had a 5 year period the number would be 60 using our monthly investment example above.
pmt: The value of each individual payment over the period. If this is left blank then the pv field will be required.
pv: This one is optional if we filled out a figure in pmt, otherwise it is required. The pv is the present value of the investment or loan. If left blank it defaults to zero.
type: Can either be left blank, or use a 0 or 1. This indicates when a payment is due or occurs – 0 is for the end of a period, with 1 being the beginning of a period.
Now that we have each element explained, let’s look at an example.
An example output of FV
For the purposes of this example, we are simply going to enter the first 3 values, leaving pv empty for now, and type will just remain at zero as in our examples it won’t really matter.
Our example dataset is as follows:
In this example we have the rate at 0.42%, which is based on an initial 5% value divided by 12. An alternative to this would be to keep at 5%, and in the formula itself use the 5%/12 instead of a single value. This may work better depending on how you lay out your spreadsheet as it is a little more readable.
When running the FV formula across all of our cells the formula would simply be:
=FV(C3,C4,C5,C6,C7)
As mentioned, though, if we went with 5% as the rate, the formula could look like the below instead:
=FV(C3/12,C4,C5,C6,C7)
The output is then as follows:
As we can see, this takes into account the interest rate and the $300 payments, leading to our final value of $20,401.82.
What if we had an initial value though set for pv? Let’s use our same dataset, but starting with $5,000 as the pv value, keeping out $300 monthly payments. This then results in the following:
As we can see, when starting with $5,000, we are then left with the value of $26,818.62.
The Excel FV function saves a lot of manual work when calculating the future value of something such as a loan or investment by taking away a lot of the math that is generally required for this kind of scenario planning.
This sums up our simple guide on how to use the Excel FV Function. For more handy guides on working with Excel, be sure to check out our Excel Tips page.