Quick answer
The NPER formula calculates the number of periods for an investment or loan.
=NPER(B2/12,-B3,B4)Syntax
Use this syntax as the pattern, then replace the sample arguments with your own cells, ranges, and criteria.
=NPER(rate, pmt, pv, [fv], [type])When to use it
Financial formulas turn business and investment assumptions into numbers you can compare. They are useful for loans, budgets, pricing, margin analysis, investment decisions, and business cases.
- Calculate monthly loan payments
- Estimate return on investment
- Calculate gross margin and markup
- Evaluate investment cash flows
Example worksheet layout
Test the formula in a small worksheet first. This makes it easier to confirm the result before copying the formula down hundreds of rows.
| Column or range | What it represents |
|---|---|
| A | Assumption label |
| B | Input value such as rate, periods, or amount |
| C | Financial result |
Copy-paste examples
These examples move from simple to more practical versions. Start with the beginner examples, confirm the result, then adapt the intermediate and advanced versions.
Basic NPER example
=NPER(B2/12,-B3,B4)Use the simple version to check the business logic first.
Use locked assumption cells
=NPER($B$2/12,-$B$3,$B$4)Lock assumptions when every row should use the same rate, cost, target, or starting value.
Avoid missing-input errors
=IFERROR(NPER(B2/12,-B3,B4),0)Useful when some rows are incomplete or when a denominator can be zero.
Create a display label
=IFERROR("Result: "&TEXT(NPER(B2/12,-B3,B4),"0.00"),"Check inputs")Use TEXT only for a display cell; keep the raw numeric result for calculations.
Use LET for auditability
=LET(result,NPER(B2/12,-B3,B4),result)LET makes financial model formulas easier to review.
Dashboard status label
=IFERROR(IF(NPER(B2/12,-B3,B4)>=0,"Positive","Negative"),"Check inputs")Use a label when the calculation feeds a dashboard status or exception report.
How to use it step by step
- Put your raw data in clear columns with headers such as Amount, Date, Status, Region, or Customer.
- Paste the starting formula into the first result cell, usually row 2.
- Replace sample references such as A2, B2, or $F$2:$F$100 with the cells in your own worksheet.
- Check the result on a few rows where you already know the expected answer.
- Copy the formula down only after the first row returns the right result.
Common mistakes and fixes
- Percentages are entered as whole numbers instead of percentages, such as 12 instead of 12%.
- Cash inflows and outflows use inconsistent signs.
- The result cell is not formatted as currency, number, or percentage as appropriate.
- Test the formula on a few known rows before using it across a full report.
Beginner tips
- Use a small sample first so you can see exactly how the NPER formula behaves.
- Convert your source range into an Excel Table when the data will grow over time.
- Add a short note near important formulas so future users know what each input represents.