The VBA NPer Function

Related Functions:
VBA RATE
VBA PMT
VBA PV
VBA FV

Description

The VBA NPer function calculates the number of periods for a loan or investment.

The syntax of the function is:

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

Where the function arguments are:

Rate-The interest rate, per period.
Pmt-The (fixed) payment amount per period.
Pv-The present value of the loan / investment.
[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 examples below.


VBA NPer Function Example

Example 1 - Calculate the Number of Periods Required to Pay Off a Loan in Full

In the following example, the VBA NPer function is used to calculate the number of months required to pay off in full, a loan of $50,000 at a rate of $1,000 per month. Interest is charged at a rate of 4% per year, and payments are made at the end of each month.

' Calculate the number of months required to pay off a loan of $50,000, with
' payments of $1,000, made at the end of each month. Interest is 4% per year.
Dim nMths As Double
nMths = NPer( 0.04/12, -1000, 50000 )
' nMths is calculated to be 54.7875772559357.

After running the above VBA code, the variable nMths is equal to 54.7875772559357.

I.e. it will take 54.8 months to pay off the loan in full.

Note that:


Example 2 - Calculate the Number of Periods Required to Reduce a Loan

In the following example, the VBA NPer function is used to calculate the number of quarterly payments of $1,200 that would be required to reduce a loan of $9,000 to $5,000. Interest is charged at a rate of 6% per year and the loan payments are due at the beginning of each quarter.

' Calculate the number of months required to reduce a loan of $9,000 to $5,000, with
' payments of $1,200, due at the beginning of each quarter. Interest is 6% per year.
Dim nQtrs As Double
nQtrs = NPer( 0.06/4, -1200, 9000, 5000, 1 )
' nQtrs is calculated to be 11.9037372921928.

After running the above VBA code, the variable nQtrs is equal to 11.9037372921928.

I.e. it will take 11.9 quarters to reduce the loan of $9,000 to $5,000.

Note that:


VBA NPer Function Error

If the specified future value will never be met for the specified interest rate and payments the VBA NPer function produces the error:

Run-time error '5': Invalid procedure call or argument

VBA Run Time Error 5 Message Box