Find out how long it will take to pay off a personal loan. Imagine that you have a $2,500 personal loan, and have agreed to pay $150 a month at 3% annual interest. Using the function NPER(rate,PMT,PV) =NPER(3%/12,-150,2500) it would take 17 months and some days to pay off the loan. The rate argument is 3%/12 monthly payments per ;· Figure 1: Use Excel’s NPER function to calculate the payment term for a loan. For longer term loans, NPER may return a large number of months, such as 94, which can be difficult to convert to months in your head. Let’s extend our calculations to make the output more 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) We will use the formula = B5 12 = 12 for the number of years to complete the loan repayment. In other words, to borrow $120,000, with an annual rate of and to pay $1,100 For this example, we want to find the payment for a $5000 loan with a interest rate, and a term of 60 months. To do this, we configure the PMT function as follows: rate - The interest rate per period. We divide the value in C6 by 12 since represents annual interest, and we need the periodic formula: Calculate payment periods for loan | ExceljetHow to calculate total interest paid on a loan in Excel?How to Calculate the Number of Months to Pay Off a Loan Excel formula: Calculate payment periods for loan | ExceljetThe same calculation could have been performed quickly using Excel’s CUMPRINC function: CUMPRINC(rate,life_of_loan,amount_of_loan,start_period,end_period,type) This requires explanatory notes: Again, the rate is the periodic rate (here , not 6%). life_of_loan is the total length of the loan, not just to date. This is needed to calculate implicitly the regular repayment the example, apply the negative sign to – to get positive , or approximately 240 months left on the loan: N = 240 This means if you make all your payments on time, you will pay off the loan in 240 months, or 20 years, from the current month.

Tags: Excel formula to calculate how long to pay off loan, Excel formula payoff loan,