How to use the NPER function
What is 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.
Table of Contents
1. Introduction
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.
2. Syntax
NPER(rate, pmt, pv, [fv], [type])
| 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. Example 1
If you repay a $1,000 loan with $100 per period at 5% interest, how many periods will it take?
This example shown in the image above demonstrates the NPER function in cell C7. It calculates the number of periods needed to repay $1,000 based on a constant payment of $100 and a fixed interest rate of 5%.
The provided arguments are:
- Interest rate: 5.00% (annual interest rate)
- Payment: -$100.00 (negative value indicates a payment outflow)
- Present value: $1,000.00 (initial loan or investment amount)
Formula in cell C21:
With these inputs, the NPER function calculates the number of periods as 14.20669908, which is approximately 14 periods and a fraction to fully pay off the $1,000 loan with $100 periodic payments at 5% interest.
The image shows the use of the NPER function in Excel to calculate the number of periods required to fully pay off a loan or investment amount based on a constant periodic payment and interest rate.
The chart plots the cumulative amortization (loan paydown) and interest amounts over the calculated number of periods (in this case, 15 periods). The chart shows that with an initial $1,000 loan, the interest portion starts high and decreases over time as more of the payment goes towards the principal amortization. The cumulative amortization line increases steadily as the loan balance is paid down period over period.
The table below provides the balance, amortization, interest, payment, and cumulative amortization amounts for each period until the loan is fully paid off after the calculated 15 periods.
4. Example 2
You want to save $250,000 for retirement by making monthly deposits into an account earning 4.5% annual interest. You save $1,200 per month, how many months (periods) will it take to reach your goal?
The provided arguments are:
- Interest rate: 4.50% (annual interest rate)
- Payment: -$1,200.00 (negative value indicates a payment outflow)
- Future value: $250,000
Formula in cell C21:
With these inputs, the NPER function calculates the number of periods as 154.23, which is approximately 154 periods or months and a fraction to fully save the $250,000 loan with $1200 monthly payments at 4.5% interest rate.
The chart above shows the savings balance over time from month 0 to month 155, the balance starts with $0 and increases as savings accumulate until the $250,000 is reached.
5. How is it 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.
6. What to do if the function is 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.
7. 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 27 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