NPER Function in Excel: How Many Payments Until Paid Off

📎 This article includes 1 downloadable practice file ↓

FinancialLevel: ExpertAvailable in: Excel 2007+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. NPER in practice
6. Where you will use it
7. Worked example
8. Mistakes people make
9. Related functions
10. Continue learning

NPER returns the number of periods needed to repay a loan or reach a savings goal.

Syntax

=NPER(rate, pmt, pv, [fv], [type])

Argument	What it means
pmt	Payment per period (negative).

Examples

Example 1

=NPER(9%/12, -50000, 4000000)/12

Years to repay ₹40 lakh at ₹50,000 a month.

Example 2

=NPER(12%/12, -15000, 0, 5000000, 1)/12

Years of ₹15,000 SIPs to reach ₹50 lakh at 12%.

NPER in practice

Where you will use it

• Months to repay a loan
• Months to reach a savings goal
• Prepayment effects

Worked example

Months to repay ₹25 lakh at 8.5%.

EMI	Formula	Result
30000	=ROUND(NPER(0.085/12,-A2,2500000),1)	126.4
50000	=ROUND(NPER(0.085/12,-A3,2500000),1)	61.9
100000	=ROUND(NPER(0.085/12,-A4,2500000),1)	27.6

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Sign convention	Payment negative, loan positive
EMI too small	#NUM! if interest exceeds the EMI

Related functions

PMT · RATE · FV

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Financial guides →

RECEIVED Function in Excel: Amount Received at MaturityFinancialLevel: ExpertAvailable in: Excel 2007+RECEIVED calculates what you get back at maturity for a…PDURATION Function in Excel: Years Needed to Reach a TargetPDURATION returns how many periods an investment needs to grow to a target at…INTRATE Function in Excel: Interest Rate of a Fully Invested SecurityINTRATE returns the annualised return when you invest an amount and receive a larger…PV Function in Excel: What a Future Amount Is Worth TodayPV returns today's value of future payments at a discount rate — how much…

📎 Practice files for this article

• 📗
  Practice workbook: Financial: EMI, SIP, NPV, IRR, depreciation23 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.