Mortgage finance calculator
Banks grant loans on security of assets such as real estate. In consideration of such loan, the borrower signed documents, conferring rights on the bank to sell this asset in the event of failure of the borrower to repay the loan along with interest as agreed. Such documentation is known as mortgage in legal parlance .
Because real estate offers Banker a greater security than other forms of collaterals, such as stocks, the banks agree to give long-term loans on such mortgage. Interest on such loans is also generally lower. This is because the banker has adequate security in the underlying asset .Apart from it, the bank is assured of a systematic return for a long time, enabling it to plan its borrowings in a more profitable manner.
If the bank had chosen to lend the same amount at a higher interest rate for a shorter tenure, then there would be some time lag between the day the amount was repaid completely, and the day new borrower was selected. During this period, the amount would earn no interest .However, the bank would continue to be liable to the depositors for interest .Effectively, the higher interest rate collected by the bank from the first borrower would not be as high .
Another constraint faced by the banker is vetting the borrowers. Not every borrower is reliable. Therefore, risk of default increases with the increase in number of borrowers .Examining the credit worthiness of and financial discipline in a borrower is a time consuming exercise, not to mention labor intensive. If the bank chooses to lend to borrowers for shorter tenure at higher interest rates, the number of different borrowers for same funds will naturally be higher. Consequently, the number of bank personnel required for assessing the borrowers and their rights on property will also be higher. This, in turn, increases the expenses of staff salary as well as rentals, as more staff members have to be accommodated in banks premises. Such administrative costs have a domino effect, bringing down the benefit of higher interest earned on shorter-term loans. Moreover, higher-interest rates are directly proportionate to higher delinquency rates in loans.
Having understood the reasoning behind the longer tenure preferred by the banker, the next concept to be understood is the equated installments. The prices of properties have shot up considerably, making it impossible for any borrower to repay the amounts in shorter tenure. Mortgage loans are availed generally to acquire a real estate property .To overcome this impasse a new concept called equated installments was presented. Borrower pays a fixed amount every month throughout the long mortgage tenure .The principal and interest components are built within this overall amount .
Mortgage loans may carry interest at a fixed rate or adjustable rate. The differences will be obvious with the mortgage calculator example given here.
For the purpose, consider a borrower taking a loan of USD100000 at interest rate of 10 percent per annum payable over a period of 10 years. In ten years, there are 120 months. Installment is payable per month. What would be the installment payable by the borrower?
Open an excel sheet. Type the following legends.
A3= Month number
B3 = Interest rate
C3 = Opening balance
D3 = Equated installment
E3 = Interest for the month
F3 = Principal repaid
Now fill in
A4 = 1
B4 = 10
C4 = 100000
D4 is to be arrived at with permutation and combination after all formulas are in place
At A5 type +A4+1
Drag this formula down till cell no. A124. This cell will contain the month number 121, which is beyond the 10-year period considered in our example.
Type +C124 at H3 (this is because the cell is out of screen range, and every time, a permutation or combination is tried at D4, going down will be time consuming)
Type the following formulae
E4 = +C4*B4/100/12
F4 = +D4-E4
Drag these formulae on to E5 and F5
B5 = +B4
C5= +C4-F4
D5=+D4
Now drag the entire row starting from B5 to F5 right up to row 124 so that all cells in the row of A124 contain some formulae.
Now come back to D4, which is the crucial cell here. Add a tentative figure higher than the amount appearing at E4. Keep increasing this till the amount appearing in cell H3 is as close to zero as possible.
This is a mortgage calculator that can be used for both fixed as well as adjustable rate mortgages.
In the same example, if the interest rate was decreased around 62nd month, H3 would become negative. Which means that loan would be cleared a month or two before the 120th month.
If, on the other hand, interest increases under adjustable rate mortgage, H3 would turn positive. And the number of months would have to be extended further till the amount at column C beyond Cell C124 becomes negative.
Other Articles
