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:
Results:
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:
Results:
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.