The Excel PV function calculates the Present Value of an investment, based on a series of future payments.
The syntax of the function is:
Where the arguments are as follows:
rate | - | The interest rate, per period. |
nper | - | The number of periods for the lifetime of the annuity or investment. |
[pmt] | - |
An optional argument that specifies the payment per period. If the [pmt] argument is omitted, it takes on the default value 0. |
[fv] | - |
An optional argument that specifies the future value of the annuity, at the end of nper payments. If the [fv] argument is omitted, it takes on the default value 0. |
[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; |
Cash Flow Sign Convention:
Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers. This is seen in the examples below.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:
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:
For further details and examples of the Excel Pv function, see the Microsoft Office website.
If you get an error from the Excel Pv Function, this is likely to be the #VALUE error:
#VALUE! | - | Occurs if any of the supplied arguments are non-numeric. |