Calculate payment periods for loan

Calculate payment periods for loan - Excel Formula

To calculate the number of payment periods for a loan in Excel, you can use a formula based on the NPER function.

This tutorial is a part of our formula library.

How to calculate payment periods for loan?

  1. Select cell C8.
  2. Type =NPER(C3/12,C4,-C2)
  3. Here, C3 is the interest rate, C4 is the monthly payment and C2 is the loan amount.
  4. Press Enter.
  5. The formula returns the number of payment periods.

Example

In this example, we have a dataset summarizing loan details, including the loan amount in cell C2, the annual interest rate in cell C3, the monthly payment in cell C4, and the number of compounding periods per year in cell C5. The goal is to calculate the total number of periods (in months) required to fully repay the loan.

Formula:

=NPER(C3/12,C4,-C2)

Calculate payment periods for loan - Excel Formula

The formula in cell C8 uses the NPER function to determine the number of periods based on the input values, where the interest rate is adjusted for monthly compounding. The result gives the total repayment duration expressed in months.

Explanation

Evaluate the formula!

The NPER function calculates the number of payment periods for an investment or loan based on constant payments and a constant interest rate. We uses the following arguments to configure the formula:

  • rate: The interest rate for each period.
  • pmt: The payment made in each period (must remain consistent throughout).
  • pv: The present value, or total amount that a series of future payments is worth now.
  • [fv]: (Optional) The future value or the desired cash balance after the last payment.
  • [type]: (Optional) Specifies when payments are due (0 = end of the period; 1 = beginning).

C3/12: The interest rate provided in cell C3 is annual. To convert it to a monthly rate, it is divided by 12 (number of months in a year). Example: If C3 = 6.01% (0.0601), then C3/12 = 0.0601 / 12 = 0.005008 or 0.5008%

C4 represents the monthly payment amount (in this case, $235). Payments are typically negative values because they represent outgoing cash flows.

-C2: The present value (loan amount) is the amount borrowed. It’s negated because it’s treated as an inflow (you receive the loan amount upfront, so it’s a positive cash flow initially). Here, C2 = $8,500, so -C2 = -$8,500.

The formula calculates and returns 40. This means it will take 40 months to pay off the loan fully with these parameters. In other words, the loan of $8,500 will be paid off in 40 months with an annual interest rate of 6.01% and monthly payments of $235.

You can download the practice file here.