The NPER Function in Excel is a financial function that returns the number of periods for a specified loan. The total periods of a loan are based on fixed data such as loan amount, interest rate, monthly payments, and compounding periods per year.
Syntax:
=NPER(rate,pmt,pv,[fv],[type])
Arguments:
- rate – The interest rate per period.
- pmt – The payment made each period.
- pv – The present value of all payments now (Today’s actual value)
- fv – [optional] The future value after the last payment. Defaults to 0.
- type – [optional] When payments are due.
- 0 = end of period. (Default)
- 1 = beginning of period.
NPER Function Usage Example
Let’s start by looking at the following example. Make sure to download the demo file if you have any questions.
We are going to use the following formula. First, we take Interest Rate and divide it by 12 to represent 12 periods in a year. Also, since we are dealing with a loan, the value is represented as a negative number.
=NPER(B4/12,B5,-B3)
Based on our data, the total periods is 92.