How to use the VBA NPER Function (Syntax + Example)

HomeVBA Functions LIST (Category Wise)How to use the VBA NPER Function (Syntax + Example)

The VBA NPER function is listed under the financial category of VBA functions. When you use it in a VBA code, it calculates the number of payments that need to be made for a loan or an investment where payment and rate of interest are the same.

Syntax

NPER(Rate, Pmt, Pv, [Fv], [Due])

Arguments

  • Rate: The rate of interest for the period.
  • Pmt: The fixed amount of payment per period.
  • PV: The present value of investment or loan.
  • [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, use 0 for the end of the period and 1 for the start of the period [This is an optional argument and if omitted VBA takes the end of the period by default].

Example

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

Sub example_NPER()
Range("A9").Value = NPer(0.08 / 12, 1000, 96000)
End Sub

In the above code, we have used the NPR to calculate the total number of periods for the investment and it has returned 67.17.

Notes

  • Both “rate” and “nper” arguments need to calculate using payment periods that are expressed in the same units.
  • If the future value supplied will never be met for the supplied interest rate and payments then the VBA will return an error.

Related Functions

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