The VBA NPer Function
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; 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
Dim nMths As Double' payments of $1,000, made at the end of each month. Interest is 4% per year. 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:
- As the payments are monthly, the annual interest rate of 4% is divided by 12 to calculate the monthly interest rate.
- As the payments are outgoing, the [Pmt] argument is a negative value.
- The function has omitted the [Fv] argument and so this is set to the default value 0.
- The function has omitted the [Due] argument and so this is set to the default value 0 (denoting that payments are due at the end of each period).
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
Dim nQtrs As Double' payments of $1,200, due at the beginning of each quarter. Interest is 6% per year. 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:
- As the payments are quarterly, the annual interest rate of 6% is divided by 4 to calculate the quarterly interest rate.
- As the payments are outgoing, the [Pmt] argument is a negative value.
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
