2

Does anyone know what formula they use for this When will you be debt-free? calculator? I want to put it in a Google Spreadsheet, but I can't find a payments-to-debt-free formula using my Google searches...

https://www.nerdwallet.com/blog/finance/consolidate-credit-card-debt-personal-loan/

Screenshot:

enter image description here

3

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

Number of payments formula

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:

total interest 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 ($10,000 loan, $500 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 $500 payment and determine that you have paid $10,984.81 over the course of the entire loan period. Subtracting the principal, you have paid $984.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.

enter image description here

1

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.

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .