Need An Amortization Calculator? A Loan And Mortgage Amortization Formula Will Do The Trick

By Robbie T. James

Amortization refers to the changes in the principal balance of a loan – such as a mortgage loan – over time. Each month, a fixed payment is made. A portion of that payment goes toward paying interest on the loan to the lender. The rest goes toward the loan principal, or amount still owed on the loan if it were to be paid off today.

Over time, as the principal gets paid down, a greater portion of the fixed monthly payment amount goes toward paying down the loan’s principal. Therefore, the loan gets paid down faster as time goes by.

If you are looking for an amortization calculator for a mortgage loan, you may want to learn the formula for amortization. That way, you can set up your own calculator in a spreadsheet program such as Excel.

The following are two formulas for mortgage loan amortization. The first formula tells you how to figure out your monthly payment based upon certain assumptions about the loan. The second formula helps you to actually build an amortization table – month by month – for the life of the loan. This is useful if you want to figure out how much principal you will owe at any time in the future during the life of the loan.

The Formula to Calculate Your Monthly Mortgage Payment

Note: the formulas below assume that you have a conventional loan whereby interest is compounded monthly.

Let’s start by defining some variables for use in the formula:

P = principal, the amount owed on the loan

I = the annual interest rate (expressed as a number from 1 to 100)

L = loan term, in years

YouTube Preview Image

J = monthly interest amount in decimal form = I / (12 x 100)

N = loan term, in months = L x 12

M = monthly payment

Here is the formula:

M = P * ( J / (1 – (1 + J) ^ -N))

Note that ^ means “to the power of”:

To solve, just follow these steps:

1. Calculate 1 + J, then take the result to the power of -N (minus N).

2. Subtract the result from 1.

3. Take the inverse of this result (1 / X).

4. Now, multiply the result by J, then by P.

The Formula to Calculate the Amortization Table

And now, here is the formula to create your own amortization table.

Again, let’s start by defining the variables:

P = principal, the amount owed on the loan

J = monthly interest amount in decimal form = I / (12 x 100)

M = monthly payment

H = your current monthly interest = P x J

C = the amount of principal you pay for the given month = M- H

Q = new principal balance (after current payment) of your loan

Now, to calculate the amortization table month by month, you will need to follow these steps:

1. Calculate H, which is P x J. This is your current monthly interest.

2. Calculate C, which is M – H. This is the amount of principal you pay down for the given month.

3. Calculate Q, which is P – C. This is the new balance of your loan.

4. Now, set P = Q and repeat steps 1 to 3 for the following month. Repeat for each month of the loan.

Knowing how to calculate your own monthly payment and amortization schedule is a powerful way to not only understand the process better, but also to allow you to set this up in your own spreadsheet program.

About the Author: Lower your monthly mortgage payments with a better interest rate. Check out:

Home Mortgage Refinance Loan Deals



Permanent Link: