PV
Returns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.
Syntax: PV( rate, nper, pmt [ , fv [ , type ] ] )
- rate
- required Double specifying interest rate per period. For example, for a car loan at an annual percentage rate (APR) of 10 percent with monthly payments, the rate per period is 0.1/12, or 0.0083.
- nper
- required Double specifying total number of payment periods in the annuity. For example, monthly payments on a four-year car loan total 4 * 12 (or 48) payment periods.
- pmt
- required Double specifying payment to be made each period. Payments usually contain principal and interest that doesn’t change over the life of the annuity.
- fv
- optional Variant specifying future value or cash balance remaining after the final payment. For example, the future value of a loan is $0 because that’s its value after the final payment. However, to save $50,000 over 18 years for a child’s education, $50,000 is the future value. If omitted, 0 is assumed.
- type
- optional Variant specifying when payments are due. 0 means payments are due at the end of the period; 1 means payments are due at the beginning. If omitted, 0 is assumed.
An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage) or an investment (such as a monthly savings plan).
The rate and nper arguments must be calculated using payment periods expressed in the same units. For example, if rate is calculated using months, nper must also be calculated using months.
For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received (such as dividend checks) is represented by positive numbers.
Note
PV raises error 11, Division by zero, for a result beyond the range of a Double, as 64-bit VBA does. VB6 returns infinity.
Common Errors
| Error | When |
|---|---|
-2147467259 (&H80004005), Unspecified error | rate is -1 and nper is 0 rate is -1 and nper is greater than 0 rate is -1 and nper is less than 0 |
11 (&HB), Division by zero | the result is beyond the range of a Double |
13 (&HD), Type mismatch | fv or type is a string that is not a number fv or type is Null |
94 (&H5E), Invalid use of Null | rate, nper or pmt is Null |
Note BETA 1034
When rate is -1 and nper is 0, VB6 and 64-bit VBA raise no error.
Note BETA 1034
When rate is -1 and nper is greater than 0, VB6 raises no error; 64-bit VBA raises error 11 (&HB), Division by zero, instead.
Note BETA 1034
When rate is -1 and nper is less than 0, VB6 raises no error; 64-bit VBA raises error 6 (&H6), Overflow, instead.
Note BETA 1034
When fv or type is Null, VB6 and 64-bit VBA raise error 94 (&H5E), Invalid use of Null, instead.
Example
In this example, the PV function returns the present value of an $1,000,000 annuity that will provide $50,000 a year for the next 20 years. Provided are the expected annual percentage rate (APR), the total number of payments (TotPmts), the amount of each payment (YrIncome), the total future value of the investment (FVal), and a number that indicates whether each payment is made at the beginning or end of the payment period (PayType). Note that YrIncome is a negative number because it represents cash paid out from the annuity each year.
Dim Fmt, APR, TotPmts, YrIncome, FVal, PayType, PVal
Const ENDPERIOD = 0, BEGINPERIOD = 1 ' When payments are made.
Fmt = "###,##0.00" ' Define money format.
APR = .0825 ' Annual percentage rate.
TotPmts = 20 ' Total number of payments.
YrIncome = 50000 ' Yearly income.
FVal = 1000000 ' Future value.
PayType = BEGINPERIOD ' Payment at beginning of month.
PVal = PV(APR, TotPmts, -YrIncome, FVal, PayType)
MsgBox "The present value is " & Format(PVal, Fmt) & "."