NPER in Excel (Table of Content)
Introduction to NPER in Excel
NPER in excel is a financial function which calculates a number of periods which are required to completely pay off the loan amount with an assumption of a fixed monthly rate of interest along with fixed payments/EMI. It simply allows you to build a model where you wish to find out a number of periods required to completely reach out the final loan amount.
Syntax
Argument in NPER Formula
- rate – is a required argument that specifies interest rate per period.
- pmt – is a required argument that specifies the amount paid at each period which contains principle amount and interest rate (excluding taxes and other fees).
- pv – is a required argument that is nothing but the actual loan amount.
- fv – optional argument which specifies the future value of the loan amount.
- type – optional argument that specifies whether the payment is to be made at the start of the period or at the end of the period: 0 – payment made at the end of the period, 1 – payment made at the start of the period.
How to Use Excel NPER in Excel?
NPER in Excel is very simple and easy. Let’s understand how to use the NPER in Excel with a few examples.
Example #1 – NPER Simple Example in Excel
Mr. Akshay has taken a loan of 1,50,000 for higher studies. He agreed to pay the amount with a rate of interest as 10.28% per annum and is able to make a payment of 10,000 per month. However, he is not so sure when he can clear the loan amount. We will help him out to find this out with the help of the NPER function in Microsoft Excel.
In cell B1, put the value of the Loan Amount.
In cell B2, Input the Rate of Interest per Year Value (Which is 10.28% in case of Mr. Akshay).
In cell B3, put the value of Monthly EMI which Mr. Akshay can pay on behalf of his Loan Amount.
4.5 (898 ratings)
View Course
We need to find out the period under which Mr. Akshay can pay this amount with monthly EMI as 10,000 and rate of interest as 10.28% per annum. This value, we need to calculate in cell B4.
In cell B4, start typing the formula of NPER.
Mention B2/12 as the first argument for NPER formula. As a given Rate of Interest is per year, we need to break it down to the interest rate per month because we are paying the amount per month.
Now, next argument is the amount to be paid at each period, since Mr. Akshay is ready to pay the EMI amount of 10,000 per month (Cell B3), you will mention it as the next argument in the formula. However, please note that this amount is a cash outflow (amount which is going to be deducted from Mr. Akshay’s account). Therefore, you need to mention it as a negative amount.
Input the loan amount Mr. Akshay has taken from bank i.e. 1,50,000 (which is stored in cell B1).
Press Enter key.
You can see that with the rate of interest as 10.28% per annum and monthly EMI of 10,000, Mr. Akshay can clear/repay his loan amount of 1,50,000 within 16.12 months.
Please note that in this formula we have not included fv (future value of loan) and type (whether the EMI is being paid at the start of the month or at the end of the month) as these are the optional arguments.
Example #2 – Calculate Period for Future Value Increment
Mr. Sandeep a 25-year-old engineer, wants to make some investment for his retirement. He wishes to have a lumpsum amount of 20,00,000 at the time he retires and ready to invest 1,00,000 today (present value). The yearly rate of interest is 5% and Mr. Sandeep is ready to pay a monthly EMI of 5,000. We want to calculate the number of months Mr. Sandeep requires to earn 20,00,000.
In cell B1 of the Excel sheet, input the Annual Rate of Interest.
In cell B2, mention the payment amount Mr. Sandeep is ready to pay every month.
In cell B3, add the present value of the investment which Mr. Sandeep is about to make.
In cell B4, the future value will take place. The value which Mr. Sandeep wants as a lumpsum amount at the time of his retirement.
In cell B5, start typing the NPER Formula.
Put B1/12 as a first argument under NPER Formula. As the given rate of interest is per annum, you need to divide it by 12 in order to get a monthly rate of interest (because Mr. Sandeep is paying installment monthly).
Mention -B2 as next argument in this formula. As the value 5,000 is going to be debited from Mr. Sandeep’s account (outgoing amount), we need to mention the negative sign for the same.
Next argument would be the Present Value of the Investment which is in cell B3.
Lastly, mention B4 as an argument that represents the Future Value of Investment.
Press the Enter key and you will get the value of the number of months, Mr. Sandeep has to make an investment in order to get 20,00,000 as a final amount when he retires.
Here, if Mr. Sandeep makes a payment of 5,000 per month for the investment amount of 1,00,000, it will require 256.82 months (21.40 years) to get matured up to the lumpsum amount of 20,00,000.
Things to Remember About NPER in Excel
- PMT under NPER function generally includes interest amount. But not the taxes and additional processing fees.
- Rate of interest should be homogeneous throughout the period. For example, in the above examples, we have divided the yearly interest rate by 12 to make it homogeneous through the year for each month.
- The payments which are outgoing, all need to be considered as debts and marked as negative payments.
- #VALUE! error occurs when one of the required arguments from the formula is non-numeric.
- #NUM! error occurs when the future value mentioned will not meet over the period of time with the mentioned rate of interest and EMI (investment amount). In that case, we need to increase the monthly investment amount. Also, this error occurs when outgoing payments are not marked as negative.
Recommended Articles
This is a guide to NPER in Excel. Here we discuss how to use NPER in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
