How to Use Excel NPER Function to Calculate Time Remaining

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

What is the NPER Function in Excel

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

How is the Excel NPER function useful?

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).

Download the file from the tutorial

How do you use the Excel NPER Function?

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.

Excel PMT Function Syntax

=NPER(rate, pmt, pv, [fv], [type])

• rate - the interest rate of the loan, you are typically given an annual rate that must be converted to a monthly rate by dividing by 12 (or whatever the annual payment frequency is).
• pmt - the amount of a payment.
• pv - the present value of the loan (how much you borrowed), typically expressed as a negative number.
• fv - [optional] the value of the loan after the final payment, typically 0. Generally left out of the function.
• type - [optional] when interest starts to accrue, typically 0 for immediately. Generally left out of the function.