How to Use the NPER Excel Formula: 2 Easy Examples
Note: This tutorial on how to use the NPER Excel function is suitable for all Excel versions including Office 365.
The NPER function in Excel is a financial formula that calculates the maturity term of a loan or an investment based on a fixed interest rate and periodic payments. It is a very handy tool that is frequently used in financial analysis to find out the time period of a loan or an investment.
In this guide, I’ll show you how to properly use the NPER Excel function and illustrate it with examples.
We’ll Cover:
- NPER Formula Syntax
- How to Use the NPER Excel Formula?
- Things to Keep in Mind About NPER
- NPER – Common Errors
Related:
How to Use e in Excel Using the EXP Function? 2 Easy Examples
How to Use the Chi Square Test Excel Function? 4 Easy Steps
The Excel SMALL Function – 3 Best Examples
NPER Formula Syntax
=NPER(rate,pmt,pv,[fv],[type])
Where,
Rate (required) is the fixed interest rate of the loan
Pmt (required) is the regular periodic payment amount
Pv (required) is the present value of the funds right now
Fv (optional) is the future value of the funds after the last payment (set to 0 by default)
Type (optional) determines when the payments are due (set to 0 by default). Type 0 means payments are made at the period’s end. Type 1 means payments are made at the period’s beginning.
How to Use the NPER Excel Formula?
Using the NPER function is pretty straightforward. All you have to do is input the required and optional arguments as per the syntax. It is easy to understand with the help of these two examples.
How to Use the NPER Formula to Calculate Loan Periods?
Let’s say I am taking a loan of 100,000 USD at a 5% annual interest rate. I am required to pay a monthly instalment of 500 USD. Now, I want to find out how long it will take for me to repay the loan. To calculate this using the NPER formula follow these steps:
List all input parameters
List all input parameters like rate, loan amount, monthly payment etc., in a column.
Enter the Input Parameters inside the NPER formula
In a different cell, enter these input parameters inside the NPER formula.
Make sure to use the correct parameters inside the formula
Please note that: All loan payments are outgoing payments. Hence, they should be marked negative. Since the period of repayment is monthly, convert the annual interest rate into a monthly interest rate by dividing it by 12. Since future value is 0 for loans and the payments have to be made at month ends, FV and TYPE can be ignored or set to 0.
Also Read:
How to Unmerge Cells in Excel? 3 Best Methods
How to Make Excel Track Changes in a Workbook? 4 Easy Tips
The Excel CHOOSE Function – 4 Best Uses
How to Use the NPER Formula to Calculate the Time to Maturity of an Investment?
In this example, I am looking to invest 50,000 USD in a fund that promises a 9% return annually. It also requires an additional recurring monthly contribution of 1,000 USD. Now, my target is to earn 1,000,000 USD out of this investment.
I am eager to find out how long it is going to take to achieve this target.
To calculate this using the NPER formula follow these steps:
- List all input parameters (rate, initial investment amount, monthly payment, future value) in a column.
- In a different cell, enter these input parameters inside the NPER formula.
Please note that, since monthly contributions are outgoing payments, they have to be marked negative.
Things to Keep in Mind About NPER
- Please follow the standard accounting conventions. Meaning all outgoing cash flow is negative and incoming cash flow is positive.
- Generally speaking, loans are set to type 0 and investments are set to type 1. This is because we generally invest as soon as possible to accumulate more interest.
NPER – Common Errors
- The #NUM! Error is displayed in a scenario where the future value will never be attained. It can also appear when the input parameters like interest rate or payments are insufficient to meet the goals.
Change them appropriately to get correct results.
- The #VALUE! Error is displayed if non-numeric values are used inside the NPER formula.
- If the NPER formula returns a negative value, it usually means that the cash flow accounting standards are not followed. Please remember that all outgoing cashflows should be marked negative and all incoming cashflows should be marked positive. In case of loans, the PMT value is negative and in case of investments, both the PMT and PV should be marked as negative. Correct such errors to get correct results.
Suggested Reads:
The FORMULATEXT Excel Function – 2 Best Examples
How to Autofit Excel Cells? 3 Best Methods
How to Extract an Excel Substring? – 6 Best Methods
FAQs
Is NPER a year or month?
NPER will be in years if you use annual interest rates and annual payments in your calculations. NPER will be in months if you use monthly interest rates and monthly payments in your calculations.
What is the meaning of NPER?
NPER is the short form of the phrase number of periods. It represents the number of periods required to settle a loan or reap an investment with a constant interest rate.
Closing Thoughts
In this guide, we just saw how the NPER Excel formula works and how to practically use it in financial calculations. I recommend you test this out for yourself to gain a better understanding of this formula.
If you need more high-quality Excel guides, please check out our free Excel resources centre.
Ready to dive deep into Excel? Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
