Calculate How many payments are necessary to pay off a loan in Excel

The NPER function in Excel tells you how many payments are necessary to pay off a loan. This is useful when you know how much you can afford to pay per month, and need to know how long it will take to pay off the loan. The inputs for this function are the principal, the interest rate, and the periodic payment amount.

NPer(rate, pmt, pv [, fv ] [, type ] )
  • rate Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10 percent and make monthly payments, the rate per period is 0.1/12, or 0.0083.
  • pmt Double specifying payment to be made each period. Payments usually contain principal and interest that doesn’t change over the life of the annuity.
  • pv Double specifying present value, or value today, of a series of future payments or receipts. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.
  • fv Variant specifying future value or cash balance you want after
    you’ve made the final payment. For example, the future value of a loan is $0 because that’s its value after the final payment. However, if you want to save $50,000 over 18 years for your child’s education, then $50,000 is the future value. If omitted, 0 is assumed.
  • type Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.

First draw a table in Excel like shown below
Calculate How many payments are necessary to pay off a loan in Excel
Now enter this code in “B4″

=NPER(B3/12,-B5,B2)

you have to put the value of the payment of each period as a negative value.

2 Comments

Add a Comment

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

Notify me of followup comments via e-mail. You can also subscribe without commenting.