How to Use NPER Function in Excel (Calculate Loan Time)

Hello friends! Today I teach you NPER() function in Excel with easy example.

What is NPER? It stand for "Number of Periods". If you have a bank loan and you pay fixed money every month, NPER will tell you exactly how many months until you finish paying! Very good for planning!

🛠️ How to write NPER Function

To make NPER work, you need 3 things: bank interest rate, how much you pay per month, and total loan amount.

Formula Syntax:

=NPER(rate, pmt, pv)

• rate: Interest rate from bank (Must divide by 12 for monthly rate!).
• pmt: Payment per month. (Must be negative - number because money go out).
• pv: Present Value. How much money you borrow total.

📊 Short & Clear Example

Imagine you buy a new computer for $2,000 (This is PV). Bank charge 5% interest per year (This is Rate). You can afford to pay $150 every month (This is PMT).

Let's calculate how many months to finish paying!

Formula for Time (Months):

=NPER(B2/12, B3, B1)

• Result: 13.73
• What it means? It means you will finish paying in almost 14 months!
• Why divide by 12? Because you pay every month, so yearly rate 5% must divide by 12 to match your monthly payment.

Check out my Advance Loan Calculator Tool to calculate loan payment easily!

💡 Easy Pro Tips

1. Watch the Minus Sign: Your monthly payment (pmt) MUST be negative number. If you forget minus sign, Excel will show #NUM! error because it think you never pay!
2. Round Up the Result: Sometimes result is 13.73 months. You can use =ROUNDUP(NPER(...), 0) to make it clean 14 months so you know the exact final month.
3. Savings Goal: You can also use NPER for saving! If you want save $10,000 and you put $500 in bank every month, NPER tell you how long to reach goal!

📚 Useful Resources

• How to Use PMT Function in Excel (Calculate Loan Easy)
• How to Calculate Simple Loan Monthly Payment in Excel (Video in Khmer)
• Top 15 Basic Excel Formulas for Beginners (Khmer Video + Free Practice File)