NPER function
Also written NPER · Excel NPER · Period of the loan · Number of periods
The Excel function that returns how many periods a loan takes to clear, given the rate, the instalment and the amount outstanding — the workbook's tool for the period of the loan.
In plain language
Most loan questions ask for the instalment. This one asks the opposite: given the instalment, how long will the loan run?
That is what NPER answers. Feed it three things — the rate of interest, the fixed payment made each month, and the amount outstanding today — and it returns the number of periods.
The workbook calls the idea the period of the loan, and notes that it is also known by its Excel name, NPER.
It is the most useful of the four loan functions for advice, because tenor is the number a household can actually negotiate. A client who cannot afford a higher instalment can often afford a longer loan.
One caution that decides the answer. If the payment is monthly, the rate must be monthly too — divide the annual rate by 12. The answer then comes back in months, not years.
How it works
Section 2.2.5 sets it out. There are times when a person would like to know the time period within which they would be able to pay off an amount. Given a capital amount, the loan interest and a fixed amount repaid each month, the period in which the loan can be repaid can be known. This is also known by its Excel term NPER.
The inputs the workbook lists:
- r — the rate of interest on the loan
- PMT — the equal payment on the loan
- PV — the present value of all the future payments
The workbook's worked example. A person wants the NPER of a loan worth Rs 5 lakh now, where the EMI is Rs 12,000 per month and the rate of interest is 8%. Since this is a monthly payment, the interest rate has to be divided by 12:
=NPER(0.08/12, −12000, 500000) = 48.97
The result is 48.97 months. The workbook's conclusion: NPER is useful to calculate the time period of repayment, and this is then matched with the EMI payable, so that it can be affordable for the individual.
The sign convention. The payment goes in as a negative number and the loan amount as a positive one, because money leaving the borrower and money received by him move in opposite directions. Reverse both and the function still works; mix them up and Excel returns an error or a nonsense answer.
Where it sits among the four. Chapter 2 uses the same family of Excel functions throughout: PV for the present value of a stream, FV for the future value, PMT for the instalment — the workbook's own case is a Rs 30 lakh loan at 6.5% for 20 years, giving PMT = Rs 22,367 — and RATE for the return, used for CAGR. NPER is the one that solves for time.
The formula
=NPER(rate, pmt, pv, [fv], [type])
rate = interest rate for ONE period (annual rate ÷ 12 for a monthly loan)
pmt = the instalment, entered as a negative number
pv = the amount outstanding today
The workbook's example:
=NPER(0.08/12, -12000, 500000) = 48.97 months
A worked example
The workbook's own case: a Rs 5,00,000 loan at 8%, repaid at Rs 12,000 a month, clears in 48.97 months — a little over 4 years.
Now an illustrative use in advice. Priyanka owes Rs 5,00,000 at 8% and can manage Rs 12,000 a month. Her adviser runs the same function at different instalments:
| Monthly payment | NPER | Total paid | Interest paid |
|---|---|---|---|
| Rs 10,000 | 61.0 months | Rs 6,10,000 | Rs 1,10,000 |
| Rs 12,000 | 48.97 months | Rs 5,87,600 | Rs 87,600 |
| Rs 15,000 | 37.8 months | Rs 5,67,300 | Rs 67,300 |
| Rs 20,000 | 27.4 months | Rs 5,48,000 | Rs 48,000 |
The instalment moves by Rs 10,000 across the table; the interest bill moves by Rs 62,000. That is the trade-off the workbook wants matched against affordability: a smaller EMI is easier every month and dearer overall.
And the mistake that wrecks the answer. Enter the annual rate instead of the monthly one — =NPER(0.08, −12000, 500000) — and the function does not merely mislead, it fails: at 8% a year the interest on Rs 5,00,000 is Rs 40,000, which a Rs 12,000 payment cannot even cover, so the loan never clears and Excel returns an error instead of a number. The rate and the periods must always speak the same language: 0.08/12, with the answer read in months.
Why NISM asks about it
Chapter 2 (Time Value of Money), section 2.2.5 (Period of the loan (NPER)), between periodic payments at 2.2.4 and annuities at 2.2.6. Chapter 2 is the most heavily computed chapter in the paper. Expect the workbook's own figures — Rs 5 lakh, Rs 12,000, 8%, answer 48.97 months — or a question on which function to use when the tenor is the unknown.
Common exam traps
- Divide the annual rate by 12 for a monthly loan, and read the answer in months. This single step is what most wrong answers get wrong.
- The answer is in periods, not years. 48.97 is months, just over 4 years.
- The payment is negative, the loan amount positive. Getting the signs the same way round gives an error.
- NPER solves for time; PMT solves for the instalment. If the question gives you the tenor, you do not need NPER.
- A fractional answer is normal. 48.97 months means the last instalment is a part payment, not that the arithmetic failed.
- A longer tenor is not a cheaper loan. Section 4.3.3 is explicit that a longer tenor means more interest paid over the life of the loan, even though each instalment is smaller.
Where this is taught
Free preparation for NISM Series X-ARelated terms
- PV functionThe spreadsheet function that converts a stream of future payments into the single lump sum needed today — the tool the workbook uses to size a retirement corpus and a life insurance cover.
- Present valueWhat a sum of money due in the future is worth today, found by discounting it back at the rate the money could earn: PV = FV ÷ (1 + r)^n.
- AmortisationThe repayment of borrowed capital over a fixed term through equal instalments, each of which is split between interest and principal in proportions that shift over the life of the loan.
- EMIThe Equated Monthly Instalment — the fixed monthly payment on a loan that covers both interest and a portion of the principal, calculated so the loan is fully repaid by the end of its term.
- Future valueWhat a sum of money invested today will be worth at a future date once returns have been earned and reinvested — the compounding half of the time value of money.
- Ordinary annuityA series of equal payments falling at the end of each period — the default form of annuity, and the one every standard present value and EMI calculation assumes.
- PPMT and IPMTThe two spreadsheet functions that split a single loan instalment into its principal part (PPMT) and its interest part (IPMT), for any instalment number you name.