compute the principal and interest for a specific month (period) on an amortized loan

Question

I can easily compute the monthly payment for a loan given the amount, the number of periods, and the periodic rate. From that, I can easily build an amortization table iteratively by subtracting the periodic interest from the principal and re-calculating it for the next period. What I'm looking for is a method that will allow me to compute the {principal,interest} values for a specific period in the loan without having to compute all of the values before it.

Said another way, I want a formula that spits out the answer to: "In month 78 of a 360-month loan of $100,000 at 5%, what is the breakdown of principal vs. interest". Again, without having to do this iteratively.

Answer 1

You can do it this way:

First, calculate the value, at the time of Payment #77, of the principal amount, ignoring all the payments. So it would just be, in your example, 100000 X (1 + 0.05/12)^77.

Secondly, calculate the value, as of Payment #77 of all those 77 payments accumulating, again with interest. This is just the future value of an ordinary annuity:

where r = .05/12, and n = 77, You've already calculated P, the regular payment.

Having brought the two amounts (original loan and scheduled payments) to the same moment in time, it is now correct to manipulate them with ordinary arithmetic.

The difference between these two values is the balance owed as of Payment #77.

That balance, multiplied by the interest rate, 0.05/12, will be the interest that accumulates on the debt by Payment #78. You pay it all (hopefully!) with part of Payment #78. Whatever is left is the principal portion of Payment #78

Answer 2

Use IPMT and PPMT functions in excel. It is quite straight forward for fixed periodic instalments.

Answer 3

Similar to the answer to this question:-

Calculate mortgage rate with a different interest rate after certain years

s = principal
r = monthly interest rate
n = number of months
d = monthly payment

s = 100000
r = 5/100/12 = 0.00416667
n = 360

Payment amount to pay off at 5% over 360 months (ref. formula)

d = r (1 + 1/((1 + r)^n - 1)) s = 536.82

Calculating the principal remaining p after 78 months, x

x = 78
p = (d + (1 + r)^x (r s - d))/r =  88952.58

The interest repayment one month later is p r = 370.64

E.g. the principal remaining after 0 months

x = 0
p = (d + (1 + r)^x (r s - d))/r =  100000

The interest repayment at the end of the first month is p r = 416.67