The Excel FV function is a
financial function that returns the future value of an investment. You can use
the FV function to get the future value of an investment assuming periodic,
constant payments with a constant interest rate.
Syntax
The syntax for the FV function in Microsoft Excel is:
=FV( interest_rate, number_payments, payment,
[PV], [Type] )
Parameters or Arguments
interest_rate
The interest rate for the
investment.
number_payments
The number of payments for the
annuity.
payment
The amount of the payment made
each period.
(Note that if the payment argument
is omitted, the PV argument must be supplied).
PV
Optional. It is the present
value of the payments. If this parameter is omitted, it assumes PV to
be 0.
(If the PV argument is
omitted, it takes on the default value 0. Also, if PVis omitted, the payment
argument must be supplied).
Type
Optional. It indicates when
the payments are due. If the Type parameter is omitted, it assumes
a Type value of 0. Type can be one of the following values.
0 -
the payment is made at the end of the period;
1 -
the payment is made at the start of the period.
Excel FV Function Examples
The following spreadsheets show the Excel FV function,
used to calculate the future value of two different investments.
Example 1
In the following spreadsheet,
the Excel Fv function is 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 it is necessary to supply the annual interest
rate of 5% as a monthly interest rate (=5%/12), and to express the 5-year
period 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
In the example below, the
Excel Fv function is 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 the annual interest rate of 10% has been
converted into a monthly rate (=10%/4), and the 4-year period has been input as
a number of quarters (=16).
Ø Again
the quarterly payments are paid out, and so are input to the function as
negative values.
Notes:
Ø Units
for interest_rate and number_payments must be consistent. For example, if you
make monthly payments on a four-year loan at 12 percent annual interest, use
12%/12 (annual rate/12 = monthly interest rate) for rate and 4*12 (48 payments
total) for number_payments. If you make annual payments on the same loan, use
12% (annual interest) for rate and 4 (4 payments total) for number_payments.
Ø If
payment is for cash out (i.e deposits to saving, etc), payment value must be
negative; for cash received (income, dividends), payment value must be positive.
References
No comments:
Post a Comment