Loans & Interests
Most of you would have taken a loan at some point in your lives — either a student loan to fund your Masters in your dream university abroad or a housing loan to purchase that own house that you(or your parents) have always longed to own. You approach a bank, they ask for a set of supporting documents, collateral etc and they sanction a loan amount for which you have to pay a monthly installment based on the interest rate charged by the bank.
If you have ever wondered how banks calculate that monthly installment, this post will attempt to explain the math behind the same and also provide some observations on how much of your monthly installment goes towards paying the interest and if it makes sense to pay off your loan earlier
Before getting our hands dirty with the math, let us define some important terms we might need to know
Some Definitions
This is the amount sanctioned by the bank towards your loan. This might or might not be equal to the amount you ask the bank for. For example, if you ask for a loan of 50 lakhs, depending on your income, credit score and strength of your collateral, the bank will sanction a percentage of this. If the bank sanctions 80% of this, it implies the bank has sanctioned 40 lakhs
This denotes how much money the bank charges you per year. Why does the bank charge, you might ask? Well, the bank is giving away some of their money for you to spend right, only makes sense to charge you for the same
Interest is usually denoted on a yearly basis like 8% p.a (p.a denotes per annum)
- If payments are made on a monthly basis, M = 12 (12 payments per year)
- If payments are made on a semi-annual basis, M = 2
- If payments are made on a quarterly basis, M = 4
The periodic interest rate denotes the interest rate for each installment and is calculated by dividing the yearly interest rate (R) with the frequency of payments (M)
i = R/M
If payments are made monthly, the monthly interest rate (i) = 8%/12 = 0.67%.
For semi-annual payments, the semi-annual interest rate (i) = 8%/2 = 4%
This denotes the number of payments required to close the loan. This can be calculated by multiplying the number of years with the yearly frequency of payments
For example, if you agree to settle the loan over a 10-year period with monthly payments, you would be making a total of 10*12 = 120 payments to settle this loan
Armed with this knowledge, let us get into calculating the monthly installment amount
The Calculations
Suppose you have taken out a loan of 10 lakhs from a bank. You agree to pay it over a series of monthly installments over a span of 10 years. The bank charges you a interest of 9% p.a for the same
The monthly installment to be paid to the bank remains constant over the entire term of the loan and is calculated as per the below formula
Where L is the loan amount, i is the periodic interest rate
M is the frequency of payments, N is the number of years
Seems a bit complicated? Worry not. Let us tackle this term by term
Firstly, let us calculate the periodic interest rate “i” which appears often
i = R/M = 9%/12 = 0.75%
Next, let us calculate the exponent M*N, which is the number of installments required
M*N = 12*10 = 120 which denotes the total number of installments to be paid to close the loan
L = 10 lakhs, the loan amount
Substituting, we get
We find that the monthly installment turns out to be Rs 12,667
But, how do we know that this number is correct. Does it all add up at the end?
To find this out, let us look at something called the “Loan Amortization Table”
The Loan Amortization Table
“Amortization” is used in 2 different contexts
In the context of accounting, “Amortization” is a technique used to lower the value of an intangible asset over its useful life for accounting and taxation purposes.
In the context of loans, “Amortization” refers to the process of paying off a debt over a series of equal installments
The Loan Amortization Schedule/Table is a table which details out the periodic payments needed to be made over time for the repayment of a loan
Each payment in an amortization schedule consists of 2 components: the principal component & the interest component i.e:
Payment = Principal + Interest
The principal amount goes towards the repayment of your principal loan amount (the 10 lakhs) and the interest goes towards paying off the interest charged by the bank
The Loan Amortization table looks similar to the one below:
The Outstanding loan amount (last column) shows how much of the loan amount is left to be repaid after each installment is paid
Let us look at how the Principal & Interest are calculated starting with the 1st installment
The Interest is calculated by multiplying the periodic interest rate(i) with the outstanding balance
Interest = i*Outstanding balance = 0.75%*10 lakhs = Rs 7500
The principal amount is calculated by subtracting the interest from the total installment
Principal = Installment — Interest = 12667.58–7500 = Rs 5167.58
Hence, for the first installment, the interest is Rs 7500 & principal is Rs 5167.58
The Outstanding Loan amount decreases by an amount equal to the principal i.e
Outstanding Loan Amount = Rs 10,00,000–5167.58 = Rs 9,94,832.42
Similarly, for the second installment, the interest is calculated as the product of the monthly interest(0.75%) and the Outstanding Loan Amount
Interest = 0.75%*994,832.42 = Rs 7461.24
Principal = 12667.58–7461.24 = Rs 5206.33
If you continue calculating for the remaining installments, you can observe that in the final installment (120th), your outstanding amount turns out to 0. This implies that you have completely paid off your loan in 120 installments and goes on to prove that our calculations are correct
You can refer to this excel sheet I have created to have a look at the entire working out of the loan amortization schedule
In Microsoft Excel, there is an in-built function called the PMT function that automatically calculates the monthly installment. Similarly the IPMT & PPMT functions can help you calculate the interest & principal components respectively for a specific installment:
Though there are ready-made functions in Excel for these calculations, it is still nice to have our own understanding of how these formulae work and how the interest & principal split works
Next, let us look at some interesting observations we can derive from the same
Interest — The Hidden 5 lakhs
If you think about it, you pay Rs 12667 monthly. Multiplied over 120 months, this turns out to be a little above 15 lakhs. But your loan amount is only 10 lakhs. So, where did this extra 5 lakhs come from? Why are you repaying 15 lakhs to the bank for a 10 lakh loan?
This extra 5 lakhs is the interest you pay to the bank for the money you have borrowed from them. But still, paying 5 lakhs interest for a 10 lakhs loan seems unreasonable right?
While this interest cannot be done away with entirely, there is a way to reduce the same: By paying off your loan sooner
Supposing you choose to pay off the above loan in 5 years instead of 10 years:
- Your monthly installment would be higher at Rs 20,758
- The total interest turns out to be only Rs 2.45 lakhs
The interest is roughly half of what you would have to pay over a 10-year period (5.20 lakhs)
While there is the positive upside of a reduced interest, the potential downside is that you might have to set aside a higher amount towards the monthly installments (Rs 20,758 vs Rs 12,667). This might cause a significant dent in your monthly savings and expenses.
It is quite a trade-off between saving on interest versus managing your monthly expenses
The Reducing Interest
A cursory glance at the loan amortization table shows that a major part of the installments in the initial months goes towards paying off the interest. The below graph shows the split of principal & interest over time (I have chosen intervals of 5 months for better visual representation)
We see that, as the months go by, the share of interest decreases in the monthly payment. This is due to the fact that the interest is calculated as a percentage of the outstanding loan amount.
Over the months, the principal amount repaid increases as you pay the installment amount. This causes the outstanding loan amount to decrease, causing the interest amount also to subsequently decrease
Another way to understand this is:
Principal = Monthly Installment — Interest
The monthly installment is constant each month. The interest component decreases each month, implying the principal component should increase to maintain the sanctity of the equation
That is all for this post.
There are a few more interesting questions that can be answered like:
Suppose during the 30th month, you got an increment in your salary and you wish to pay 20,000 towards your monthly installment going forward:
- How would this impact the loan amortization schedule?
- How sooner would you be able to close your loan?
- If you want to close your loan within the next 2 years (after the 30th month), how much should your monthly installment amount be?
I will be covering these in another future post. Stay tuned for the same
Thanks for reading!!!
Originally published at http://infinitesimallysmall.com on August 11, 2024.
