: How to calculate the number of months until a loan is paid off (given principal, APR and payment amount)? Does anyone know what formula they use for this When will you be debt-free? calculator? I want to put it in a Google

Vote Up Vote Down   0 Reactions

Login to give Reaction!

Here is the derivation of the formula, with

s = value of loan
d = periodic repayment
r = periodic interest rate
n = number of periods

The loan is equal to the sum of the repayments discounted to present value.

Vote Up Vote Down   0 Reactions

Login to give Reaction!

The formula for determining the number of payments (months) you'll need to make on your loan is:

where i=monthly interest rate (annual rate / 12), A=loan amount (principal), and P=monthly payment.
To determine the total interest that you will pay, you can use the following formula:

where P=monthly payment, N=number of payments (from above formula), and A=loan amount (principal).
A quick example: using the numbers in the screenshot above (,000 loan, 0 monthly payment, 10% APR), the number of payments ends up to be 21.97 (which means that payment number 22 is slightly less than the rest). In the second formula, you take that number times your 0 payment and determine that you have paid ,984.81 over the course of the entire loan period. Subtracting the principal, you have paid 4.81 in total interest.
On your spreadsheet, the function you are looking for is NPER:

NPER(rate, payment_amount, present_value, [future_value, end_or_beginning])

rate - The interest rate. (This should be the monthly rate, or the annual rate divided by 12.)

payment_amount - The amount of each payment made. (For a loan payment, this should be a negative number.)

present_value - The current value of the annuity. (The initial principal of the loan)

future_value - [ OPTIONAL ] - The future value remaining after the final payment has been made. (This should be 0, the default if omitted.)

end_or_beginning - [ OPTIONAL - 0 by default ] - Whether payments are due at the end (0) or beginning (1) of each period.