How to use the NPER function
The NPER function calculates the number of payment periods for a loan or an investment based on periodic constant payments and a fixed interest rate.
What is a payment period?
A payment period is the length of time between payments made on a loan or investment. For example, a loan with monthly payments the payment period would be one month. A loan with quarterly payments the payment period would be three months.
What is a loan?
A loan is an amount of money you borrow from an individual or entity. The lender is usually a corporation, financial institution, or government, however it can also be an individual. A lender is an entity or individual that makes funds available for a borrower that can be a person or a business. The borrower agrees to repay the loan amount plus interest over an agreed-upon period of time.
What is an investment?
An investment is an asset or business acquired with the goal of generating income or appreciation, the purpose is to grow the money over time.
What is periodic constant payments?
Periodic constant payments are payments that are made at regular intervals such as monthly, quarterly, or yearly and have the same amount each time.
What is fixed interest rate?
A fixed interest rate is an interest rate that remains the same throughout the term of a loan or an investment.
Table of Contents
1. NPER Function Syntax
NPER(rate, pmt, pv, [fv], [type])
2. NPER Function Arguments
| rate | Required. The interest rate per period. |
| pmt | Required. The payment per period; it must be constant. |
| pv | Required. Argument pv is an abbreviation for present value, in other words, the total value right now based on future payments. |
| [fv] | Optional. The future value, or the value you want to achieve when the last payment is made. Default value is 0 (zero). |
| [type] | Optional. When payments are due. 0 (zero) - Default value. At the end of the period. 1 - At the beginning of the period. |
3. NPER Function Example
This example shown in the image above demonstrates the NPER function in cell C7. It calculates the number of periods needed to repay $1000 based on a constant payment of $100 and a fixed interest rate of 5%.
Formula in cell F3:
The table below the calculation in the image above shows interest, amortization, and the remaining loan amount after each periodic payment. Get the workbook to see how the calculations are made, link below.
Note, the specified interest is on a yearly payment period, this makes the function return how many years it takes to repay the loan. However, to make it return a monthly payment period divide 5% by 12 (there are 12 months in a year) and you get approx. 0.42% which is the interest rate per month.
The NPER function returns 10.23 months to repay the loan.
4. How is the NPER function calculated?
Use the following math formula to calculate the number of periods :
nper = log((pmt - rate * pv) / (pmt + rate * fv)) / log(1 + rate)
where:
- nper is the number of periods
- pmt is the payment made each period
- rate is the interest rate per period
- pv is the present value of the investment
- fv is the future value of the investment
For example, if you want to know how many months it will take to pay off a loan of $10,000 at 5% annual interest rate with monthly payments of $200, here is how to populate the formula above:
nper = log((200 - 0.05/12 * 10000) / (200 + 0.05/12 * 0)) / log(1 + 0.05/12)
becomes
log((200 - 0.00416666666666667 * 10000) / (200 + 0.00416666666666667 * 0)) / log(1 + 0.00416666666666667)
becomes
log((200 - 41.6666666666667) / (200 + 0)) / log(1.00416666666666667))
becomes
log(0.791666666666665) / log(1.00416666666666667))
becomes
-0.101457640758777/0.00180580086326235
and returns -56.1842907614321
The result is 56.18 months.
5. NPER function not working
The NPER function returns a #VALUE! error if an invalid argument is used, the example above shows a letter as an interest rate.
The #NAME! error is returned if the NPER function is misspelled, the image above shows this error.
The NPER function returns a #NUM! error if it can't calculate the number of payment periods, in other words no matter how many payment periods the loan grows larger month by month.
6. Fixed payments calculator and amortization schedule
The file below contains a fixed payment calculator, you need Excel 365 to use it. It calculates the amortization table dynamically meaning the data spills to cells below instantly when you change the interest rate, payment and loan value.
Formula in cell B24:
Formula in cell C24:
Formula in cell D24:
Formula in cell E24:
Formula in cell F24:
Get Excel file
Useful links
NPER function - Microsoft
Fixed payment calculator
Microsoft Excel Loan Calculator With Amortization Schedule
Functions in 'Financial' category
The NPER function function is one of many functions in the 'Financial' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form