The VBA NPer function calculates the number of periods for a loan or investment.
The syntax of the function is:
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.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:
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:
If the specified future value will never be met for the specified interest rate and payments the VBA NPer function produces the error: