|
The Excel FV Function
Search this site:
Basic Description
The Excel FV function calculates the Future Value
of an investment with periodic constant payments and a constant interest rate.
The syntax of the function is :
FV( rate, nper, [pmt], [pv], [type] )
Where the arguments are as follows:
| rate |
- |
The interest rate, per period
|
| nper |
- |
The number of periods for the lifetime of the annuity
|
| [pmt] |
- |
An optional argument that specifies the payment per period
(if the [pmt] argument is omitted, the [pv] argument must be supplied)
|
| [pv] |
- |
An optional argument that specifies the present value of the annuity
- i.e. the amount that a series of future payments is worth now
(if the [pv] argument is omitted, it takes on the default value 0.
Also, if [pv] is omitted, the [pmt] argument must be supplied)
|
| [type] |
- |
An optional argument that defines whether the payment is made at the
start or the end of the period.
The type argument can have the value 0 or 1, meaning:
0 - the payment is made at the end of the period
1 - the payment is made at the beginning of the period
If the type argument is omitted, it takes on the default value of 0 (denoting payments made at the
end of the period).
|
Excel Fv Function Examples
The spreadsheets below show examples of the Excel Fv function. In each case, the format of the function
is shown in the spreadsheet on the left and the result is shown in the spreadsheet on the right.
Example 1
The following spreadsheet shows the Excel Fv function used to calculate the future value of an investment of
$1,000 per month for a period of 5 years. The present value is 0, the interest rate is 5% per year
and the payments are made at the end of each month.
|
Formula:
| |
A |
| 1 |
Future value of an investment
of $1,000 per month over 5
years, with a present value of
$0, and an interest rate of 5%
per year (payment made at
end of each mth): |
| 2 |
=FV( 5%/12, 60, -1000 ) |
|
Result:
| |
A |
| 1 |
Future value of an investment
of $1,000 per month over 5
years, with a present value of
$0, and an interest rate of 5%
per year (payment made at
end of each mth): |
| 2 |
$68,006.08 |
|
Note that, in this example :
-
The payments are made monthly, so we have had to convert the annual interest rate of 5% into the
monthly rate (=5%/12), and the 5-year period needs to be input as a number of months (=60)
-
As the present value is zero, and the payment is to be made at the end of the month, the [pv] and
[type] arguments can be omitted from the above function.
-
As the monthly payments are paid out, they are input to the function as negative values.
Example 2
The example below shows the Excel Fv function used to calculate the future value of an investment of
$2,000 per quarter for a period of 4 years. The interest is 10% per year and each payment is made at the
start of the quarter.
|
Formula:
| |
A |
| 1 |
Future value of an investment
of $2,000 per quarter over 4
years, with a present value of
$0, and an interest rate of 10%
per year (payment made at
start of each qtr): |
| 2 |
=FV( 10%/4, 16, -2000, 0, 1 ) |
|
Result:
| |
A |
| 1 |
Future value of an investment
of $2,000 per quarter over 4
years, with a present value of
$0, and an interest rate of 10%
per year (payment made at
start of each qtr): |
| 2 |
$39,729.46 |
|
Note that, in this example :
-
The payments are made quarterly, so we have had to convert the annual interest rate of 10% into the
monthly rate (=10%/4), and the 4-year period needs to be input as a number of quarters (=16)
-
Again the quarterly payments are paid out, and so are input to the function as negative values.
More details and examples of the Excel Fv function are provided on the
Microsoft Office website.
Fv Function Errors
If you get an error from the Excel Fv Function, this is most likely to be the #VALUE error:
Common Errors
|
#VALUE!
|
-
|
Occurs if any of the supplied arguments are non-numeric
|
|
