Amortization formulas
Amortization related to loans entails division thereof into smaller equated parts, which is repayable over a fixed term such as 180 months or 15 years. Obviously, any loan carries interest. The quantum of this interest comes down every month, because some part of the loan principal is repaid. Therefore, even if the quantum of loan repaid every moth could be made uniform throughout the period, the quantum of interest payable would continue to vary. If, however, the quantum of loan repaid was also made flexible then an equated monthly installment could be arrived. That is the underlying concept in such equated amortization formulas.
The simplest amortization formula is the one that can be calculated in Microsoft Excel. To calculate the equated installments from this, the following information needs to be provided:
- Interest rate if the interest rate is 10 percent it is represented as 0.10. This interest rate is divided by the annual installments, such as 12 in case of monthly installments.
- Number of installments if the loan is repayable in 10 years, in monthly installments, then the total number of installments is 120.
- Amount of loan this is the quantum borrowed.
This information is to be inserted in @PMT (interest rate, number of installments, amount of loan). PMT represents the installment to be paid.
But all amortization schedules do not conform to such simple formulas. There are different situations requiring different measures.
In the above formula, it is presumed that the interest rate is calculated at a fixed rate. If adjustable interest rate is applicable to the loan, then the number of installments payable may come down. The formula is not equipped to accommodate this variation. Similarly, if the borrower repays part of the loan in between the tenure, the number of installments comes down. In such cases, if the number of installments is reduced in the formula, the result will not be accurate. The concept of equated amortization installments is to ensure that only the total number of installments paid by the borrower are affected, and not the installment amounts. Therefore, any variation in interest or principal does not change the equated monthly installments.
Therefore, there is need to develop a flexible amortization formula that accommodates all such variations and gives an accurate picture.
There are many amortization schedules available on net. A simple but comparatively more flexible amortization schedule can be designed using a spreadsheet such as Excel.
For this, give the following legends
B1 Interest rate
C1 Principal
D1 Installment
E1 Interest component
F1 Principal component
G1 Additional principal repaid
h1 Balance Principal
Data considered for our example would be a loan of 100,000 to be amortized in two years (24 months), on adjustable interest rate at 10 percent. In the beginning of 10th month (ending of 9th month) the borrower pays 10,000. In 12th month, the interest goes up to 12 percent.
Type at
A3 = 1
B3 = 10
C3 = 100,000
D3 = 0 (this is a tentative figure it has to be calculated with permutations and combinations)
E3 = +C3*B3/100/12
F3 = +D3-E3
G3 = 0
H3 = +C3-F3-G3
Feed the following formulae at
A4 = +A3+1
B4 = interest rate applicable for the month or +B3
C4 = +H3
D4 = +D3
Copy E3, F3, G3, and H3 on E4, F4, G4, and H4 respectively
Now copy the entire row right up to row number 27.
It is necessary to determine what is the installment amount (D3), and how much is repaid every month. The installment can be arrived by reducing or increasing the amount starting from (100,000/24) = 4166.667. Objective is to ensure that the amount in cell C27 is as close to zero as possible. Therefore, the amount at D3 is almost 4614.5
In the 9th month, the borrower paid 10000
Therefore, type this amount in cell G11.
In 12th month type 12 percent in cell B14.
The schedule so built reveals that loan is cleared around the 22nd month, i.e., in the 24th row of the spreadsheet.
The above examples are essentially related to loans that carry interest on reduced balances. Note that principal amount comes down in every successive cell of Column C.
There is another way of amortization. This is generally availed by people who are desperate for funds. Here the principal is not reduced on monthly basis. Instead interest is calculated for the entire term of the loan and added back to the principal. This resultant sum is then divided by number of installments. Therefore, if A borrows a sum of $5,000 for 3 years on interest rate of 10 percent per annum, the interest for the entire period would be $500 x 3, i.e., 1500. This 1500 is added to 5,000 dollars (principal). Now, the resultant 6,500 is divided by number of months in 3 years, i.e., 36. Therefore, equated installment per month would be $180.55.
Notwithstanding the manner of calculation, amortization makes loans and borrowings more affordable, and is therefore a popular way of borrowing for purchasing homes and cars.
Other Articles
