VBA PV Function (Syntax + Example)

HomeVBA TutorialTop VBA FunctionsVBA PV Function (Syntax + Example)

The VBA PV function is listed under the financial category of VBA functions. When you use it in a VBA code, it can calculate the present value of a loan or investment which has a constant rate of interest and equal payments throughout the entire period. It helps you know if a loan or an investment is profitable in the present time.

It works like PV function in the worksheet.

Syntax

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

Arguments

  • Rate: The rate of interest for the period.
  • Nper: The number of periods to pay the loan or investment.
  • Pmt: The fixed amount of payment per period.
  • [FV]: The future value of the loan/investment [This is an optional argument and if omitted VBA takes 0 by default].
  • [Due]: Defines whether the payment is due at the start or the end of the period [This is an optional argument and if omitted VBA takes the end of the period by default].
    • Use 0 for the end of the period and 1 for the start of the period

Example

To practically understand how to use VBA PV function, you need to go through the below example where we have written a vba code by using it:

Sub example_PV()
Range("A9").Value = PV(0.08 / 12, 5 * 12, -1500)
End Sub

In the above code, we have used the PV to get the present value of an investment by using an 8% annual interest rate and 5 years of the time period, with a monthly payment of 1500 and it has returned 73977.65 in the result.

Notes

  • Both “rate” and “nper” arguments need to calculated using payment periods that are expressed in the same units.
  • While using PV any cash paid out is represented by a negative number and any cash received is represented by a positive number.

Related Functions

DDB | FV | IPMT | IRR | MIRR | NPER | NPV | PMT | RATE | SLN | SYD