Related Functions:

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

Syntax

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

Arguments

Argument Description
rate The interest rate, per period
nper The number of periods for the lifetime of the annuity or investment
[pmt] Optional. Specifies the payment per period. If omitted:

  the [fv] argument must be supplied
[fv] Optional. Specifies the future value of the annuity, at the end of nper payments. If omitted:

  it takes on the default value 0
[type] Optional. 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 beginning of the period
If omitted, the [type] argument takes on the default value of 0, denoting payments made at the end of the period.

Examples

Example 1

The following spreadsheet shows the PV function 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.

  A B C D
1 Data      
2 $1,000 Periodic payment – monthly    
3 8% Annual interest rate    
4 20 Term – years    
5        
6 Formula Result Notes
7 =PV(A3/12,A4*12,A2) -$119,554.29 Present value of an annuity with the terms in A2:A4, [type] omitted defaults to 0
8 =PV(A3/12,A4*12,A2,,1) -$120,351,32 Present value of an annuity with the terms in A2:A4, payment made at the beginning of the period ([type] = 1)

Note: In the example:

  • the annual interest rate of 8% had to be converted into a monthly rate (=8%/12), and
  • the number of years had to be converted into months (=20*12)
  • [fv] was not required in either formula

Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied arguments are non-numeric
Result is much higher or much lower than expected Usually occurs due to failure to convert the rate or the nper to months or quarters, i.e.:

  months = 12 * years   monthly rate = annual rate/12
  quarters = 4 * years   quarterly rate = annual rate/4