PVANNUITY( ) function

Returns the present value of a series of future payments calculated using a constant interest rate. Present value is the current, lump-sum value.

Syntax

PVANNUITY(rate, periods, payment <,type>)

Parameters

Name Type Description
rate numeric

The interest rate per period.
periods numeric

The total number of payment periods.
payment numeric

The payment per period.

The payment amount must remain constant over the term of the annuity.

type

optional

 numeric

The timing of payments:

  • 0 – payment at the end of a period
  • 1 – payment at the beginning of a period

If omitted, the default value of 0 is used.

Note

You must use consistent time periods when specifying rate, periods, and payment to ensure that you are specifying interest rate per period.

For example:

  • for a monthly payment on a two-year loan or investment with interest of 5% per annum, specify 0.05/12 for rate and 2 * 12 for periods
  • for an annual payment on the same loan or investment, specify 0.05 for rate and 2 for periods

Output

Numeric. The result is calculated to two decimal places.

Examples

Basic examples

Monthly payments

Returns 21455.82, the present value of $1,000 paid at the beginning of each month for 2 years at 1% per month, compounded monthly:

PVANNUITY(0.01, 2*12, 1000, 1)

Annual payments

Returns 20280.61, the present value of $12,000 paid at the end of each year for 2 years at 12% per annum, compounded annually:

PVANNUITY(0.12, 2, 12000, 0)

Advanced examples

Annuity calculations

Annuity calculations involve four variables:

  • present value, or future value $21,243.39 and $ 26,973.46 in the examples below
  • payment amount per period $1,000.00 in the examples below
  • interest rate per period 1% per month in the examples below
  • number of periods 24 months in the examples below

If you know the value of three of the variables, you can use an Analytics function to calculate the fourth.

I want to find: Analytics function to use:
Present value

PVANNUITY( )

Returns 21243.39:

PVANNUITY(0.01, 24, 1000)
Future value

FVANNUITY( )

Returns 26973.46:

FVANNUITY(0.01, 24, 1000)
Payment amount per period

PMT( )

Returns 1000:

PMT(0.01, 24, 21243.39)
Interest rate per period

RATE( )

Returns 0.00999999 (1%):

RATE(24, 1000, 21243.39)
Number of periods

NPER( )

Returns 24.00:

NPER(0.01, 1000, 21243.39)

Annuity formulas

The formula for calculating the present value of an ordinary annuity (payment at the end of a period):

The formula for calculating the future value of an ordinary annuity (payment at the end of a period):

Remarks

Related functions

The FVANNUITY( ) function is the inverse of the PVANNUITY( ) function.

[ Back to top ]

ACL Scripting Guide 14.1