Excel can figure out how long to pay off a loan

, , , , , , , , , ,

Microsoft Excel has a handy function called NPER that can help you work out how long it’ll take to pay off a loan. It’ll show how adding even a little bit per payment can help clear a loan faster and some practical advice on using NPER in the real world.

Say you have a personal loan of $10,000 that you want to pay off completely in $200 monthly instalments, with an interest rate of 6% and payments due at the end of the month. To work out the amount of time you use the function:

=NPER(interest rate, payment, loan amount, target value, payment type)

Which looks like this in our example.

=NPER((0.06/12),200,-10,000,0,0)

It will take approximately 57.68 months to pay off the personal loan, which equates to about 4 years and 10 months.

Comparing loan payment options

Once you have that basic calculation, it’s easy to add ‘what if’ scenarios to see the effect of paying more or less than the standard amount.

Row 3 – adding $25 a month to the payments cuts 7 months off the loan.

Row 4 – cutting the payment to $150 adds about 2.5 years to the loan duration.

Row 5 – uses the ‘future value’ option to work out the time to pay down to a certain value (instead of zero). In this example, pay down the loan to $2,000 owing.

How NPER() works

How does this work? It’s the magic of the NPER() function in Excel.

=NPER(rate, pmt, pv, [fv], [type])
  • Rate: The interest percentage charged annually.
  • PMT (Payment Amount): The fixed instalment you pay regularly (monthly or yearly).
  • PV (Present Value): The initial borrowed sum.
  • FV (Future Value) (optional): If you have a specific loan balance goal, mention it here.
  • Type (optional): Specify whether payments are due at the beginning (1) or end of each period (0).

Practical Notes about NPER()

Important things to note regarding the NPER function:

  • It’s important to use negative numbers for an outflows (amounts that you pay) and positive numbers for inflows (any amounts you receive)).  We’ve added a minus sign to the Monthly Payment parameter.
  • Enter the interest rate as a decimal number or percentage (0.03 or 3%).
  • Its important to supply the rate that corresponds to the correct periods. For example, if an annual interest rate is provided of 10% but a loan is paid off monthly, you will need to use 10%/12 for the rate argument.
  • Doesn’t include any additional fees added by lender.

Convert Months only into years and months

One more thing, a total number of months is correct but a little hard to understand so lets change that to years plus months.

There’s a formula to convert months into years and months:

=INT(<months>/12) & " years, " & MOD(<months>,12),0)& " months"

MOD() gives the remainder in base 12, in other words, the number of months left after whole years.

We use a slight variation of that to round up the months to the next whole month, because a loan has to be fully paid, down to the last dollar.

=INT(<months>/12) & ” years, ” &ROUNDUP(MOD(<months>,12),0)& ” months”

Excel can figure out payments to clear credit card debt
Predict the future value of your investments with Excel