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.
4
-
exceljet.net/excel-functions/excel-cumprinc-function– RonJohnCommented Jun 12, 2019 at 19:00
-
but aside of excel there is no mathematical formula for this purpose?– kprincipeCommented Jun 12, 2019 at 19:16
-
Excel certainly implements a mathematical formula (more probably a series formula inside the =CUMPRINC() function.– RonJohnCommented Jun 12, 2019 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.– JayCommented Jun 12, 2019 at 20:28
Add a comment
|
1 Answer
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.
