Excel Office

Excel How Tos, Tutorials, Tips & Tricks, Shortcuts

Calculate payment periods for loan in Excel

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.

Formula

=NPER(rate,payment,-loan)

Explanation

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.

Also See:   Calculate periods for annuity in Excel

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.

Also See:   PMT, RATE, NPER, PV and FV Financial Functions in Excel

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

Also See:   NPER function: Description, Usage, Syntax, Examples and Explanation

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.

Leave a Reply

Your email address will not be published. Required fields are marked *