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.|
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.
' Calculate the number of months required to pay off a loan of $50,000, withDim 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.
' Calculate the number of months required to reduce a loan of $9,000 to $5,000, withDim 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.
If the specified future value will never be met for the specified interest rate and payments the VBA NPer function produces the error: