Amortization Formulas

Does anyone know the underlying formulas for creating your own amortization table?

I’ve been using realdata.com and their tables…but would like to do my own in Excel®.

Example:

Loan Amount: $100,000
Interest: 7%
Term: 360 months

First month payment breaks down as follow:

Principal: $81.97
Interest: $583.33
Monthly Payment: $665.30

Would like to know how these amounts for first month principal and interest are calcuated…(underlying forumla)…so I can replicate for following months, etc.

Thanks,
-Mike

Basic amrotization formula for calculating monthly payment is:

Monthly payment = (P x R/12) / (1 - (1 + R/12)^-M)

Where:

P = Initial Balance
R = Interest Rate
M = Term, in months

So, in your case …

Monthly Payment = (100000 x .07/12) / (1 - (1 + .07/12)^-360)
Monthly Payment = $665.30

Interest rate after each month = Previous Balance x R/12

In your case …

1st Month = 100000 * .07/12 = $583.33

Prinicpal paid each month = Monthly Payment - Interest Paid

In your case …

1st Month = $665.30 - $583.33 = $81.97

Balance next Month = Previous Balance - Principal Paid

In your case …

Balance 2nd month = $100,000 - $81.97 = $99,918.03

and so on …

Stephen,

Thanks for the helpful reply…

What does the carrot, (^), stand for?

-Mike

(^) signifies exponent (or “raised to the power of”). You can use it directly in Excel. Matter of fact, you can type the entire calculation straight to Excel (except replace “x” with “*” for multiplication).

IOW, typing the following in an Excel cell …

=(100000*.07/12)/(1-(1+(.07/12))^-360)

… should yield …

665.3

You can also create a macro to do it for you (if Excel doesn’t already have such an animal). I use the following:

Function LoanPayment(Balance, Rate, Term, IntOnly)
  If IntOnly = 0 Then
    LoanPayment = (Balance * Rate / 12.00) / (1 - (1 + (Rate/12.00))^-Term)
  Else
    LoanPayment = Balance * Rate / 12.00
  EndIf
End Function

And then just say something like …

=LOANPAYMENT(100000;.07;360;0)

… in an Excel cell.