Calculate mortgage payment
To calculate mortgage payment, it is necessary to know the following variables.
a. Interest rates and method of calculation
b. Term of the loan
c. Repayable period (monthly, quarterly, half-yearly or annually)
d. Loan amount
Interest may be calculated either on fixed rates or flexible rates. Fixed interest rates imply that there will be no change in the rate of interest throughout the mortgage term. Unlike this, in mortgages with flexible interest rates, the interest rates vary depending upon selected index. mortgage products that carry fixed interest rate for a specified period, and flexible interest rates for the rest of the loan term are also available in the market.
Elementary formula for calculating mortgage payment for one year is
((Loan amount x interest rate x term) + Loan amount)
Since amounts are payable on monthly basis, this product is divided by 12. However, in this type of mortgage payments, the effective interest paid to the lender is much higher than what is quoted. This is because after the first month, the principal amount keeps on diminishing, as a result of which the amount of interest paid on this reduced balance keeps increasing.
mortgage loans play a crucial role in economies. Not many can afford to buy homes without borrowing from future, as real estate prices have reached such a level. To facilitate purchase of homes, and costly assets, lenders came up with s mortgage product wherein interest was calculated on reduced principal instead of being calculated in the above manner. This is the reason mortgage loans became popular.
A simple calculator for mortgage payment can be prepared in Excel sheet. For this, the first step is to give legends in this fashion.
A1 = "Month"
B1 = "Principal"
C1 = "Interest rate"
D1 = "Interest amount per month"
E1 = "Installment"
F1 = "Principal repaid"
G1 = "Principal remaining at the end of the month"
Next step is to feed the variables and formulae. For example, a borrower takes a mortgage loan of 20,000, at 10 percent p.a. Interest is to be calculate d on fixed rate. The term of loan is 15 years.
A2 = +1
B2 = + 20000
C2 = +10/100
D2 = +B2*$C$2/12
E2 = +D2 +10 (tentative amount)
F2 = +E2 D2
G2= +B2-F2
A3 =+A2+1
B3 = +G2
C3 = +C2
Copy formulae in cells D2 to G2 to cells from D3 to G3. At E3, type +E2.
Now copy the entire 3rd row till the number in column A equals the total number of months in the loan term. Since the mortgage loan is repayable over a period of 15 years, this would mean that the last number in column A would be 180.
Keep on increasing E2 till the amount in G2 gets as close to 0 as possible. This would be the mortgage payment or EMI on the mortgage loan of 20,000, whichever currency.
Importance of mortgage payments:
a. mortgage payments vary as per the listed variables. This spreadsheet may be used to calculate mortgage payments by changing the term. In addition, the calculator may be used to calculate mortgage payments in both fixed and variable interest rates bearing mortgages.
b. Calculating mortgage payments is important for both businesses as well as individuals. Most countries offer tax relief on interest paid on mortgages. Quantum of such interest component in the mortgage installment can also be easily calculate d from the above calculator. In case of home loans, which are a type of mortgage loans, the principal repaid may also be eligible for tax rebates.
c. Lenders offer mortgage loans on terms and conditions comparable with other lenders. It is necessary to understand the implications of these terms and conditions. The spreadsheet facilitates comparison between different mortgages so that the borrower may opt for mortgage paymentsuiting personal monthly cash flow.
Other Articles
