2

I’m looking for a Wolfram Alpha equation to put in my rate, loan term, principal remaining, and monthly payment (and anything else) to solve how many payments I have left.

All I seem to find are formulas for fixed extra payments, but before kids I was paying a lot extra, then after the first less, and now with 3 kids just $200 extra but that’s going to go away until they get out of daycare.

Essentially, how can I calculate how many payments I have left if no extra payments are made (or how many months did I take off for my variable extra payments.)?

  • If by "loan term", you mean the initial loan term, I'm not sure that's relevant. Rate, principal, and monthly payment should be enough. – Acccumulation Jan 2 at 21:20
  • I just didn’t know what is necessary. With amortization, I didn’t know if paying extra early affects how much goes towards principal and how much goes towards interest. Now I feel like I’m over complicating it. – user3000724 Jan 2 at 21:31
  • Just enter the data into a Loan Amortization spreadsheet such as the one that comes with Microsoft Excel and you will see when your balance decreases to 0. – Glen Yates Jan 2 at 21:53
  • You can use the method here: money.stackexchange.com/a/94230/11768 – Chris Degnen Jan 2 at 22:01
  • On a side note if your interest rate is near 3 like mine. You want to make sure you are maxing out your 401(k) before paying extra money to a mortgage. We are getting close to make 3% in the bank. Right now I am at 2.1% A 3% is close to a free loan when you factor in inflation and average return on investments. – Mark Monforti Jan 3 at 18:23
2

Let's define i = monthly interest. How to get monthly interest from yearly interest depends on how the yearly interest is given. If it's given as APR, then you can just divide the yearly interest by 12 (there is some complication as to whether there's compounding of interest within a month, but unless your rate is absurdly high, that should be small enough that it won't affect the result much). If it's given as APY, it's useful to define r = 1+i. The monthly r is the yearly r raised to the one-twelfth power. Let's also represent the size of the monthly check with m, the principal you currently have as P, and the principal you have n month from now as P_n. Then we have

P_(n+1) = P_n+iP_n-m = (1+i)P_n-m = rP_n-m

That is, the principal each month is the rate applied to the previous principal, minus the monthly payment.

Now, suppose we guess that P_n can be written as Ar^n+B for some A and B. (If you're wondering where that guess came from, it's just a matter of being familiar with this sort of math problem.)

P_(n+1) = Ar^(n+1)+B = rP_n-m = r(A^n+B)-m = Ar^(n+1)+rB-m

So

Ar^(n+1)+B = Ar^(n+1)+rB-m and hence

B = rB-m
B-rB = -m
(1-r)B = -m
B = -m/(1-r)
B = m/(r-1)

I defined r as being 1+i, so i = r-1, so B = m/i

Next, we can take P_0 = P = Ar^0+B = A+B = A + m/i

So A = P - m/i

This gives the formula

P_n = (P-m/i)r^n + m/i

You're looking for how look it takes to pay off the loan, so you want P_n = 0

(P-m/i)r^n + m/i = 0
m/i = (m/i-P)r^n
(m/i)/(m/i-P) = r^n
m/(m-Pi) = r^n
log_r[m/(m-Pi)] = n

So:

Take the current principal and multiply by the interest; hat gives you how much money is being added in interest each month. Subtract that number from the monthly payment; that gives you how much you're currently contributing to the principal. Divide the monthly payment by that number; that tell you what the ratio is between the total amount you're paying and how much you're paying is going towards the principal. Then take the log of that number, base r. Since calculators generally don't have arbitrary bases, you can also take the log of that number, any base, then divide by the log of r in that same base.

For example, suppose you have a yearly interest rate of 6% APR. That gives i = .5% and r = 1.005. Now suppose you have a balance of $100,000 and you're paying $1000 a month. So P = 100,000 and m = 200. You have .5%*$100,000= $500 of interest accruing this month. Your monthly payment is twice as large as that, so you need the log base r of 2. The natural log of 2 is .301, and the natural log of 1.005 is .0021. The ratio between those is .301/.0021 = 138.97, so you have 139 payments left (11 years and 7 months).

0

Just to make this really simple, in a time value of money loan situation, you have 5 variables -

  • n - number of periods
  • i - interest rate
  • PV - present value (balance)
  • PMT - payment
  • FV - future value ($0 if you're looking to pay it off)

When you have 4 of the 5 you can solve for any of the others. No Wolfram Alpha equation needed, just a simple financial calculator (online, app, or actual calculator). It doesn't matter what payments you made before, just what the current balance is and the other variables. Then to find the number of payments left, solve for the number of periods, typically by pressing the N key or tapping the N button, etc after the other variables are filled in.

Now if you really want to model changing future payments you can, but then you need to repeat the above calculation separately for each period where there is a different payment. LibreOffice has the needed functions to set this up in a spreadsheet and I'm guessing Excel does too.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.