Personal Finance & Money Stack Exchange is a question and answer site for people who want to be financially literate. It only takes a minute to sign up.
Sign up to join this community
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:
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 ($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.
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.
