Calculating the Time to Repay Loans

The NPER function calculates the term, or number of regular payments, on a loan or for an investment annuity given its interest rate, the payments, present value (or loan balance), future value (or balloon payment), and, optionally, the type-of-annuity switch. If you set the type-of-annuity switch to 1, Excel assumes payments occur at the beginning of the period, following the annuity due convention. If you set the annuity switch to 0 or you omit the argument, Excel assumes payments occur at the end of the period following the ordinary annuity convention.


The function uses the following syntax:

NPER (rate, pmt, pv, fv, type)

For example, to calculate the number of $1,000 monthly payments required to pay off a 9% mortgage that still has a $100,000 mortgage balance, you use the following formula:

=NPER (.09/12,-1000,100000,0,0)


The function returns the value 185.53, representing roughly 185 payments and then another roughly half payment. Notice that to convert the 9% annual interest to a period interest, the formula divides the annual interest rate by 12. Notice, too, that the payment amount, as a cash outflow, shows as a negative value while the loan balance, as an implicit cash inflow, shows as a positive value.

NOTE The NPER function rarely returns an integer, or whole-number result. As in the preceding example, it commonly returns a fractional value, indicating that after the last regular payment, an additional fractional payment will also need to be made.

You can also use the NPER function to calculate investment terms. In this case, you calculate the number of payments that need to be made in order to reach some future value. Suppose, for example, that you want to calculate how many years a customer needs to contribute $2,000 to an Individual Retirement Account in order to amass a $1,000,000 portfolio. If you assume the customer will earn 9% annually and will make payments at the beginning of the year, you use the following formula to make this estimate:

=NPER (.09,-2000,0,1000000,1)

The function returns the value 43.45, indicating the $2,000 payments will need to be made for slightly more than 43 years. Notice that the type switch is 1, which means that the function returns the amount that must be paid at the beginning of the year. If you instead want to calculate the amount that would need to be paid at the beginning of each year, you would use the following formula to make this estimate:


=NPER (.09,-2000,0,1000000,0)

This formula returns the value 44.43. This value is slightly more than the annuity due value because by making payments at year-end, the customer loses interest. If you wanted to make the same calculation but recognize the added fact that the customer already has $5,000 in his IRA account, you would use the formula:

=NPER (.09,-2000,-5000,1000000,0)

This formula returns the value 42.07.