The NPER function returns the number of periods for an investment based on periodic, constant payments and a constant interest rate.
|rate||The interest rate per period|
|pmt||The payment made each period; it cannot change over the life of the annuity. Typically, pmt contains principal and interest but no other fees or taxes|
|pv||The present value, or the lump-sum amount that a series of future payments is worth right now|
|[fv]||Optional. The future value, or a cash balance you want to attain after the last payment is made. If [fv] is omitted, it is assumed to be 0, i.e. the future value of a loan is 0|
|[type]||Optional. Defines whether the payment is made at the start or the end of the period. The [type] argument can have the value 0 or 1, meaning:
If omitted, the [type] argument takes on the default value of 0, denoting payments made at the end of the period.
|2||12%||Annual interest rate|
|3||-$150||Payment made each period|
|8||=NPER(A2/12,A3,A4,A5,0)||116.8957||Periods for the investment with the above terms|
|9||=NPER(A2/12,A3,A4,A5,1)||114.8628||Periods for the investment with the above terms, except payments are made at the beginning of the period|
|10||=NPER(A2/12,A3,A4)||110.4096||Periods for the investment with the above terms, except with a future value of 0|
Note: In the example, the annual interest rate of 12% had to be converted into a monthly rate (=12%/12)
Cash Flow Convention: In line with general cash flow conventions, outgoing payments are represented by negative numbers and incoming payments are represented by positive numbers.
Common Function Error(s)
|Problem||What went wrong|
|#VALUE!||Occurs if any of the supplied arguments are not recognized as numeric values|
|#NUM!||Occurs if the specified [fv] will never be met for the rate and pmt. You may need to increase the payment amount or reduce the interest rate to get a valid result|
|Returns a negative result when a positive one is expected||Usually occurs when the pv and the specified pmt both have the same arithmetic sign. If a loan is being paid off, pv should be positive and the pmt should be negative, or vice versa|