PV Function 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) & "."