How To Calculate Monthly Interest On Loans

A loan can simply be defined as a specific amount that a bank lends you on a certain rate of interest and facilitates a monthly repayment schedule for you as per the tenure agreed upon by both parties.

Equated Monthly Instalment or EMI is the monthly sum you have to pay for a fixed number of months to fully repay the loan amount that you have taken. It is the sum of the principal amount and interest levied on that amount for a particular month.

A Loan can be viewed as the Bank investing in a borrower

A good way of understanding what a loan interest is and why is it charged by a Bank is to look at it from an investment perspective. We frequently invest in fixed deposits to get returns on our investments. This investment deposit can be viewed as you lending the Bank some money to invest in other assets and you in turn get profits on your investment at the end of the deposit term.

In a similar way, giving loans is the Bank’s way of investing in you. It lends you the money which you invest in buying a car, home, education or even a personal venture. In return, the bank charges an Interest which is effectively their profit from the investment that they have made in you.

Calculation of Monthly Interest

While the rate of interest varies for different types of loans. the calculation of EMI and Interest are largely governed by the principal amount and loan tenure. A very easy and simple way of calculating the component of monthly loan interest is the use of Microsoft Excel. Just follow these few simple steps: –

  • Open an Excel spreadsheet
  • Locate the fx or the Insert Function symbol (Just below the Font options under Home tab)
  • Select “Financial” in the category drop-down list and “IPMT” in the function list
  • Once you click OK, you will be able to view the “Function Arguments” box. The calculation variables that need to be entered in here are:
    • Rate – Rate of Interest adhering to an amortized (blended) loan schedule

In Loan Schedule Amortization, EMI is a blend of principal amount and interest. In the initial months, a large part of the EMI goes toward paying the interest amount and as the tenor increases, the major part of the EMI transitions towards paying the principal amount.

    • Period (Per) – The specific month or instalment for which you want to calculate the interest
    • Loan Tenure (Nper) – The total number of instalments within the loan tenure
    • Principal Amount (Pv) – The total value of the loan taken by you

For example, let’s consider that Customer A has taken a loan of 1,00,000 INR with a 9% interest rate to be payable in 24 months. With these values in mind, you can calculate the monthly (12 payments), quarterly (4 payments) and half-yearly (2 payments in a year) interest amount.

Monthly – 9%/12

Quarterly – 9%/4

Half-Yearly – 9%/2

Also, Fv is the future value which is not applicable here so we just keep it as 0. Once you enter all these values, you will get the exact amount of interest that you are paying for each instalment. Check these sample screenshots of interest calculation to get a better idea of the process:

Monthly Interest = 750 INR

Quarterly Interest = 2250

Half-Yearly Interest = 4500 INR

You will note that if you change the input value in Per, say from the 1 st month to the 5th month, the values of interest will be different as they will be calculated as per the amortized or repaid amount corresponding to the 5 th month.

Awareness allows Planning

Knowing how to calculate the interest that you are paying for each instalment along with the total interest allows you to regulate your finances accordingly so that there is never a default on payment. In case your repayment capacity increases during the loan term, you can get the loan tenure restructured and increase the monthly instalment amount to save on the interest.


Category: Credit

Similar articles: