The Excel PV Function

Basic Description

The Excel PV function calculates the Present Value of an investment, based on a series of future payments.

The syntax of the function is:

PV( rate, nper, [pmt], [fv], [type] )

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, the [fv] argument must be supplied.

[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;1 - the payment is made at the start 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).

Cash Flow Convention:

Note that, in line with the general cash flow convention, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers. This is seen in the examples below.

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 annuitywith an interest rate of 5%per year and payments of$1,000 per month over 5years (payment made at endof each month):

2

=PV( 5%/12, 60, 1000 )

Results:

A

1

Present value of an annuitywith an interest rate of 5%per year and payments of$1,000 per month over 5years (payment made at endof 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 annuitywith an interest rate of 10%per year and payments of$2,000 per quarter over 4years (payment made atstart of each quarter):

2

=PV( 10%/4, 16, 2000, 0, 1 )

Results:

A

1

Present value of an annuitywith an interest rate of 10%per year and payments of$2,000 per quarter over 4years (payment made atstart 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.