The Excel PV function is a
financial function that returns the present value of an investment. You can use
the PV function to get the value in today's dollars of a series of future
payments, assuming periodic, constant payments and a constant interest rate.
Syntax
The syntax for the PV function
in Microsoft Excel is:
=PV( interest_rate, number_payments, payment,
[FV], [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. If this parameter is omitted, you must enter a FV value.
FV
Optional. It is the future
value of the payments. If this parameter is omitted, it assumes FV to be 0.
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 Pv Function Examples
Example 1
In the following spreadsheet,
the Excel Pv function is used to calculate the present value of an annuity that
pays $1,000 per month for a period of 5 years. The interest is 5% per year and
each payment is made at the end of the month.
Formulas:
|
|
A
|
|
1
|
Present
value of an annuity
with an interest rate of 5% per year and payments of $1,000 per month over 5 years (payment made at end of each month): |
|
2
|
=PV(
5%/12, 60, 1000 )
|
Results:
|
|
A
|
|
1
|
Present
value of an annuity
with an interest rate of 5% per year and payments of $1,000 per month over 5 years (payment made at end of each month): |
|
2
|
-$52,990.71
|
Note that, in this example:
► As
the payments are made monthly, it has been necessary to convert the annual
interest rate of 5% into a monthly rate (=5%/12), and to express the 5-year
period as a number of months (=60);
► As
the forecast value is zero, and the payment is to be made at the end of the
month, the [fv] and [type] arguments can be omitted from the above function;
► As
the initial investment is paid out, the calculated present value is a negative
cash amount.
Example 2
In the example below, the
Excel Pv function is used to calculate the present value of an annuity that
pays $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.
Formulas:
|
|
A
|
|
1
|
Present
value of an annuity
with an interest rate of 10% per year and payments of $2,000 per quarter over 4 years (payment made at start of each quarter): |
|
2
|
=PV(
10%/4, 16, 2000, 0, 1 )
|
Results:
|
|
A
|
|
1
|
Present
value of an annuity
with an interest rate of 10% per year and payments of $2,000 per quarter over 4 years (payment made at start of each quarter): |
|
2
|
-$26,762.76
|
Note that, in this example:
► As
the payments are made quarterly, it has been necessary to convert the annual
interest rate of 10% into a monthly rate (=10%/4), and to express the 4-year
period as a number of quarters (=16);
► Again,
as the initial investment is paid out, the calculated present value is
negative.
Notes
► The
PV function returns the value in today's dollars of a series of future
payments, assuming periodic, constant payments and a constant interest rate.
► A
stream of cash flows that includes the same amount of cash outflow (or inflow)
each period is called an annuity. For example, a car loan or a mortgage is an
annuity. When each period's interest rate is the same, an annuity can be valued
using the PV function.
► In
annuity functions, cash you pay out, such as a deposit to savings, is
represented by a negative number; cash you receive, such as a dividend check,
is represented by a positive number.
For example, a $2,500 deposit to
the bank would be represented by the argument -2500 for pmt if you are the
depositor, and by the argument 2500 for pmt if you are the bank.
References
No comments:
Post a Comment