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.

  • but aside of excel there is no mathematical formula for this purpose?
    – kprincipe
    Jun 12 '19 at 19:16
  • Excel certainly implements a mathematical formula (more probably a series formula inside the =CUMPRINC() function.
    – RonJohn
    Jun 12 '19 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 '19 at 20:28

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



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 or ask your own question.