Finance
Work out how long a loan takes to pay off
=NPER(B1/12,-B2,B3)How it works in Excel
- B1/12 is the monthly interest rate.
- -B2 is the monthly payment.
- B3 is the amount owed.
- The answer is a number of months.
- Divide by 12 to get years.
- If the payment is below the monthly interest, you get #NUM!, because the debt never clears.
The idea in general
A bigger payment pays the debt down faster, and this counts how many it takes.
What I assumed
- The yearly rate is in B1, the monthly payment is in B2 and the amount owed is in B3.
Check the result on your own data before relying on it.