0

This question already has an answer here:

I would like to know if there is a formula that could tell me the future balance of a loan. For example, if I have a loan of 10,000 at 6% for 5 years and I do the amortization it brings that the payment should be 193.33. After 3 monthly payments the balance would be 9,568. My question is, if there a formula where I can plug in the 10,000 and it would tell me how much the principal would change after 3 payments, which result should be 9568.

marked as duplicate by JoeTaxpayer Jun 12 at 20:22

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • but aside of excel there is no mathematical formula for this purpose? – kprincipe Jun 12 at 19:16
  • Excel certainly implements a mathematical formula (more probably a series formula inside the =CUMPRINC() function. – RonJohn Jun 12 at 19:31
  • Hmm, the question referenced as a duplicate isn't the same question at all. That question asks how amortization schedules work and why the amount of the payment that goes to interest declines. This question asks for a formula for the balance. Someone gave an answer that included a formula that answers this person's question, but it's not at all clear how either the OP here or some future visitor looking for a formula would know that that answer included one. – Jay Jun 12 at 20:28
1

Yes, there's a formula, but it's kind of complicated. The formula is:

b=P*(r+1)^n-m*((r+1)^n-1)/r

where:

n is the number of months (assuming monthly payments)

r is the monthly interest rate, expressed as a decimal, e.g. 2% is .02

P is the initial loan amount

m is the monthly payment

b is the balance after n months

Oh, and I'm using "computer notation" here: * is multiplication, / is division, and ^ means raise to the power.

The bank may also add interest from the date the loan was made until the first payment, which complicates the formula a little more.

I probably should write this formula down because every time I need it I ever to figure it out again. In practice I usually find it simpler to set up a spreadsheet. :-)

Oh, I should make clear that the interest rate r here is the MONTHLY interest rate, not considering compounding. So if you have an annual interest rate, divide by 12.

I ran your example through this formula and came up with $9567.86, which if we round off to the nearest dollar matches the number you expected.

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