Question:

I want to create a payment schedule for my mortgage. Is there a mortgage amortization equation or formula which can help me?

Answer:

The calculations for mortgage payments are not that difficult and in this article we'll show you how to create a payment schedule using some simple equations.

Consider a simple $100,000 mortgage, to be paid back in 10 yrs at 5% interest rate.

Note: For mortgages, even though the quoted interest rate is the annual interest rate, it is not compounded annually. In fact, for monthly payments, interest is compounded monthly.  We will need to keep this fact in mind while making calculations.

Now,
P= 100,000
r=5%=0.05
n=10x12= 120
i= r/12 =0.05/12 =0.004167

Where: P is the Principal amount; r is the Annual Interest rate; n is the no. of compounding periods (in this case no. of months); i is the Monthly interest rate.

There is a simple formula that calculates the monthly payments needed to pay off a loan. It is:

M = P [ i(1 + i)n ] / [ (1 + i)n - 1]
(M being the monthly payment)

Note: If you have MS Excel, you can find ‘M' in a minute. All you need to do is use the following function: =PMT(i,n,P)

In our case, we find M= $1060.66

Each month the amount you pay back to your lender is made up of two parts:

  • The first part is a bit of the Principal you return to the lender i.e. the amount by which Principal is lessened (amortized, to use accounting jargon) for the next period.
  • The second part is the amount you pay to the lender for borrowing his/her money i.e. the interest payment.

Therefore ‘M' is a sum of the interest payment and the amortization of Principal. Now we create a mortgage amortization schedule as follows:

Month Principal Interest Payment Monthly Payment Amortization
0 $100,000.00 $0.00 $0.00 $0.00
1 $99,356.01 $416.67 $1,060.66 $643.99
2 $98,709.34 $413.98 $1,060.66 $646.67
3 $98,059.97 $411.29 $1,060.66 $649.37
4 $97,407.90 $408.58 $1,060.66 $652.07
5 $96,753.11 $405.87 $1,060.66 $654.79
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
119 $1,056.25 $8.78 $1,060.66 $1,051.87
120 $0.00 $4.40 $1,060.66 $1,056.25

Where,
Interest Payment = i * (Principal leftover at end of each month)
Amortization = M - (Interest Payment)

The table contains simple addition or subtraction and you should have no problem setting up your mortgage amortization and payment schedule.

Mortgage rates hit their lowest since 1955. Ask the home loan experts we recommend Quicken Loans how to take advantage of them.
Was this Mortgage QnA helpful?
Not at all
  • Currently 3/5 Stars
  • 1
  • 2
  • 3
  • 4
  • 5
Definitely
Add to this Answer

Mortgage QnA is not a common forum. We have special rules:

  • Post no questions here. To ask a question, click the Ask a Question link
  • We will not publish answers that include any form of advertising
  • Add your answer only if it will contrubute to the quality of this Mortgage QnA and help future readers
If you have trouble reading the code, click on the code itself to generate a new random code. Verification Code Above:
Bookmark and share this QnA: