The VBA PV Function

Related Functions:

Description

The VBA PV function calculates the present value of a loan or investment that has periodic fixed payments and a fixed interest rate.

The syntax of the function is:

PV( Rate, Nper, Pmt, [Fv], [Due] )

Where the function arguments are:

Rate	-	The interest rate, per period.
Nper	-	The number of periods over which the loan or investment is to be paid.
Pmt	-	The (fixed) payment amount per period.
[Fv]	-	An optional argument that specifies the future value of the loan / investment. If omitted, [Fv] uses the default value 0.
[Due]	-	An optional argument that defines whether the payment is due at the start or the end of the period. The [Due] argument can have the value 0 or 1, meaning: 0 - the payment is due at the end of the period; 1 - the payment is due at the beginning of the period. If the [Due] argument is omitted, it uses the default value 0 (denoting payments due 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 example below.

VBA PV Function Example

In the following VBA code, the VBA 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.

								' Calculate the present value of an annuity that pays $1,000 per month over 5 years.

									' Interest is 5% per year and each payment is made at the end of the month.

Dim pv1 As Double
pv1 = PV( 0.05/12, 60, -1000 )
' pv1 is calculated to be 52990.7063239272.

The above VBA code calculates the present value of the annuity to be $52,990.71.

Note that:

As the payments are monthly, the annual interest rate of 5% is divided by 12 to calculate the monthly interest rate. Also, the number of periods during the 5 years of the annuity is supplied as 60 months.
As the [Pmt] argument represents payments going out of the annuity, this is supplied as a negative value.
As the future value of the annuity is 0, the [Fv] argument can be omitted from the PV function.
As the payments are due at the end of each period, the [Due] argument can also be omitted from the PV function.

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page