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.
Not at all | Definitely |
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