Principle and interest calculations [closed]

Question

I have a loan at 5%, I know the total owed and the monthly payment and need aschedule giving me the interest and principle paid each month until paid in full. Where can i find a schedule calculator which will give me this breakdown.

Answer 1

This is an alternative to setting up a chaining amortisation table.

Taking formulae and example figures from this answer: Loan balance for a specific month

Given

s = principal
i = periodic rate
m = number of periods
d = periodic payment

d = s * i * (1 + 1/((1 + i)^m - 1))

the balance b remaining in month x is

b = (d + (1 + i)^x * (i * s - d))/i

Applying example figures, with 10% nominal APR compounded monthly over 10 years

  s = 100000
  i = 0.1/12
  m = 10 * 12

∴ payment d = s * i * (1 + 1/((1 + i)^m - 1)) = 1321.51

Balance in final month (120), should be zero

  x = 120
∴ balance b = (d + (1 + i)^x * (i * s - d))/i = 0

Interest at the end of month x

interest = (d + (1 + i)^(x - 1) * (i * s - d))

so interest at the end of month 1

  x = 1
∴ interest = (d + (1 + i)^(x - 1) * (i * s - d)) = 833.33

& principal paid = d - interest = 488.17

At the end of month 2, etc.

  x = 2
∴ interest = (d + (1 + i)^(x - 1) * (i * s - d)) = 829.27

& principal paid = d - interest = 492.24

... and so on, until at the end of the final month (120)

  x = 120
∴ interest = (d + (1 + i)^(x - 1) * (i * s - d)) = 10.92

& principal paid = d - interest = 1310.59

The total interest is m * d - s = 58580.88

Demonstrated in Excel, showing formulas on the right.