The NPER Function allows you to calculate the number of periods that make up the life of a loan or investment. So, If you have a loan with a constant repayment plan and a constant interest rate, the NPER function will tell you exactly how long it will take you to repay the full loan amount.
The video tutorial below will walk you through using the IPMT Function (a full transcript of the video is below):
Get your Free copy of our Financial Functions Quick Reference Guide: Click Here
IPMT Function Tip Video Transcript:
The NPER function returns the number of periods for the life of a loan amount repayment plan or investment plan based on periodic constant payments and a constant interest rate. Don’t be fooled by the heading of total months, the NPER function can return the number of periods, whether the periods are weekly-based, monthly-based, fort-nightly, as long as the periods are consistent.
The NPER function requires an interest rate syntax, the payment amount that will be made each and every period for the life of either the loan repayment plan or the investment payment plan, the present value in case of loan amounts, the future value in case of investment plans or savings plans, and the type (the type lists whether the payment will be at the start or at the end of the month which is important for tax calculations).
Let’s take a look at the NPER function in action:
If you would like to download and use this example workbook, click here.
Here we have a sheet with a number of values ready for us to start building our NPER function.
We’ll be working on a monthly repayment plan, that’s important because we will need to take our annual interest rate and divide it by twelve to get our monthly repayments. The amount to be repaid per month is listed as a negative value. The reason that it is listed as a negative value is because this is representing money that is moving away from you. Here, we have a loan amount of ten-thousand dollars, for this particular loan amount we would like to see, based on a hundred and seventy-five dollars a month, how many periods it would take us to pay of the entire loan given a set repayment plan and a set interest rate.
=NPER (rate, pmt, pv, [fv], [type])
We now have enough to build our function, I reference my interest rate (B10), the payment amount (C10), and the present value (D10).
You’ll note that future value and type are listed in square brackets this means that these are optional parameters and don’t have to be used for the function. Very quickly we can see that it will take sixty-seven months given the constant interest rate and constant repayment amount to pay off the life of the ten-thousand dollar loan.
If I was to try and add a little bit more in each and every month, say an addition seventy-five dollars, we can see that that would have a substantial effect on how many months it would take to pay off the loan in total.
Get your Excel Financial Functions Quick Reference Guide Now:
This Quick Reference Guide is a curation of the Top 15 most commonly used Excel Financial Functions.
