Banking calculator finance mortgage
Prices of real estate assets such as homes have gone well beyond the reach of common man. Therefore, banks and other lenders have stepped in to facilitate purchasing of such assets. These assets may be bought from finance provided by the bank. In return, the property being acquired has to be mortgaged in favor of the lender bank. Such loans are broadly classified as mortgage finance.
Another feature of mortgage finance is that they are payable over a long period, in regular equated monthly installments. These installments are made partly of interest, and partly of principal. This concept becomes more complicated when interest is calculated on reducing balance method. In such case, there is difference in interest and principal component in every successive equated monthly installment. Sounds confusing?
There is an easier way to understand the calculations in mortgage finance schemes offered by the bankers.
For this, a simple mortgage finance calculator needs to be developed on a spreadsheet, such as Microsofts Excel.
Suppose, Mr. U wanted to buy a house, but ran short of 500,000. He approached xxx bank for home loan, and the bank agreed to grant home loan provided Mr. U mortgaged the property being purchased by him with the mortgage finance. The term for repayment agreed between Mr. U and the bank is 10 years. Interest rate applicable is 9 percent per annum and this is to be calculated on reducing balance method.
To develop the calculator, first a framework with legends is required.
Type at
A1 = Month
B1 = Principal outstanding
C1 = Interest rate
D1 = Interest component in the installment
E1 = Installment
F1 = Principal component in the installment
G1 = Additional principal repaid, if any
H1 = Balance loan carried forward
Now the formulae
At A3 type 1
At B3 type 500,000
At C3 type 9
At D3 type +B3*C3/100/12
E3 is the variable to be determined by permutations and combinations. Tentatively feed any amount greater than what appears at D3 in this cell
At F3 type +E3-D3
If any amount of mortgage finance is repaid, then the amount is to be mentioned in the G column; otherwise the column content is 0.
At H3 type +B3-F3-G3
Now the second level formulae
At A4 type +A3+1
At B4 type +H3
At C4 type +C3 (Note that this may change somewhere during the term in cases where the interest rate on mortgage finance is flexible)
Copy the formula at D3 and paste it in D4
At E4 type +E3
Copy the formula at F3, and paste it in F4
At G4 type +G3
Copy the formula at H3 and paste it in H4
Since the term is 10 years, the total number of months is 120. Therefore, the last installment should appear in row no. 123 because we started with A3.
Copy the entire row 4, and drag the formulae right up to row number A125.
Because the contents go beyond the screen, at J1 type +B125
Having inserted our formulae, now we need to ascertain the equated monthly installment at E3. For this the amount in the cell can be altered continuously till the amount at Cell J1 is as close to 0 as possible. This is the monthly-equated installment.
This Banking calculator is useful for calculating both fixed and variable interest rates mortgage finance installments.
In case the interest increases or decreases the new rate is fed at the particular month in column C.
In addition, the calculator also accommodates any prepayment of the mortgage finance. Any prepayments are to be fed in column G at the specific month.
Other Articles
