For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007
Video walk through example demonstrating how to use the NPER function to calculate periods in a loan or investment.
To learn how to use the PMT function see How to Use the PMT Function
For more advanced finacial modeling see the Loan Amortization tutorial
NPER is an Excel financial function that calculates the number of payment periods for a loan or investment based on constant periodic payments and interest rate (return).
The NPER Function takes three required arguments and up to two optional arguments. At a minimum, you must tell the NPER function the loan's annual interest rate(divided by number of payments per year), amount of a payment and the present value(how much you borrowed or have in an investment). Present value is typically expressed as a negative number so the result of the NPER function willwork correctly.
Optionally, you can give the function a future value (how much you will owe (or have left) at the end of the loan), but this is typically $0 and leaving it off tells Excel as much. You could use a future value other than $0 to evaluate a lease, or balloon payment. For example in a leased vehicle you can enter the residual as the future value and in this way calculate an implied interest rate for the lease. This is also useful to compare purchasing with leasing. The other optional argument, type, tell the function when interest start to accrue. Either 0 for immediately or 1 for at the end of the first period. In most loans the interest begins to accrue immediately, and the default value when leaving this argument out of the function is 0 to reflect this.
=NPER(rate, pmt, pv, [fv], [type])