Need an Amortization Calculator? A Loan and Mortgage Amortization Formula Will Do the Trick

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 us 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)

Read about:   How Does a Mortgage Work? |

L = loan term, in years

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.

Read about:   Mortgage vs. Loan? - Personal Finance & Money Stack Exchange

37 thoughts on “Need an Amortization Calculator? A Loan and Mortgage Amortization Formula Will Do the Trick

  1. Kasey Chi says:

    Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You definitely know what youre talking about, why throw away your intelligence on just posting videos to your weblog when you could be giving us something informative to read?

Leave a Reply

Your email address will not be published.