Exceljet

Quick, clean, and to the point

Calculate payment periods for loan

Excel formula: Calculate payment periods for loan
Generic formula 
=NPER(rate,payment,-loan)
Explanation 

To calculate the number of payment periods for a loan, given the loan amount, the interest rate, and a periodic payment amount, you can use the NPER function. In the example shown, the formula in C10 is...

=NPER(C6/12,C7,-C5)

How this formula works

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the NPER function is to calculate the number of periodic payments for loan.

For this example, we want to calculate the number of payments for a $5000 loan, with a 4.5% interest rate, and fixed payments of $93.22. The NPER function is configured as follows:

rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest: 

=C6/12

pmt - The payment made each period. This is the known amount $93.22, which comes from cell C7.

pv - The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in fron of C5:

-C5

fv - the future value, or desired balance after last payment. We do not provide a value since the default is zero and makes sense for a loan.

type - When payments are due, where 0 = end of period and 1 = beginning of period. Default is 0 so we do not provide a value.

With these inputs, the NPER function returns 59.996, which is rounded to 60 in the example, representing 60 months = 5 years. The monthly payment is rounded to the nearest penny, which causes NPER to return a number slightly less than 60.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables