Select Page

Is the urban Indian living a life of debt? Education loan, auto loan, multiple credit cards, a home loan. The word credit,loans, EMI is integrated with his life. In this article we will cover what is Loan? What is EMI or Equated Monthly Installment, Features of EMI, how is EMI calculated? What is reducing balance? What is Loan Amortization Schedule ? How to make it in excel? Secured, Unsecured Loans. Getting a loan is nowdays dependent on a good credit history which can be obtained from CIBIL, Equifax, Experian.

Loan

In a loan, the borrower borrows an amount of money, called the principal, from the lender. Borrower is obligated to pay back to the lender at a later time. The loan is generally provided at a cost, referred to as interest, which is an incentive for the lender to provide the loan. Borrower has to pay the principal, amount borrowed, plus interest. Money can be paid after a certain date, or partial repayments, or in regular installments,  Acting as a provider of loans is one of the principal tasks of Banks.

Suppose Nishant borrows Rs 50,000 for 5 year and the loan is to be repaid every year.

  • Principal or Amount borrowed  = Rs 50,000
  • Time for which Nishant borrowed  = 5 year
  • Interest charged = 10% per annum,
  • Nishant would have to pay principal 50,000/5 i.e Rs 10,000 and interest at the rate of 10% of 50,000 i.e Rs 5,000  = Rs 15,000 every year.

Over the tenure of the loan, Nishant ends up  paying Rs 15,000 X 6 = 75,000

Equated Monthly Installment or EMI

Equated Monthly Installment, or the EMI, is the amount of money paid by borrowers, each calendar month, to the lender, for clearing their outstanding loan. EMI payments are generally made every month on a fixed date, for the entire tenure of the loan, till the outstanding amount has been completely repaid. The EMI of a loan is determined by the following factors:

  1. Principal – The actual amount borrowed.
  2. Rate of interest – The rate at which the amount is being borrowed. The higher the interest rate, the higher the EMI.
  3. Tenure of the loan – This is the duration for which the loan has been taken.
  4. Method of computation – Method used by the loan provider, flat rate or  daily, monthly or on annual reducing basis.
  5. Processing Fees: Many banks charge a processing fee at the time of disbursing a loan. Processing fees typically vary between 0% to 3% of the loan amount.

Method of Computation: Flat and Reducing Balance

Interest rates can be calculated at a flat rate keeping the outstanding amount (i.e, the amount on which interest is calculated) constant throughout the loan tenure or at a reducing balance rate, which lowers the outstanding amount as the loan is paid back.

Flat Rate: In this method of computation payment of installment does not reduce the loan amount. It remains constant or flat throughout the tenure. For example If you took a loan of Rs 1 lakh(1,00,000) with a flat rate of interest of 10% over 5 years, then you would pay principal 1,00,000/5 i.e Rs 20,000 and interest at the rate of 10% Rs 10,000  = Rs 30,000 every year. Over the tenure of the loan, you would end up paying Rs 1,50,000. The calculation of a flat interest rate loans is easy to understand.

Reducing Balance Rate :  In a reducing balance loan with each payment, the outstanding loan amount is recalculated. It can be recalculated after every year, every quarter, every month, every week or every day. The  period in which the bank recalculates the loan amount outstanding based upon the amount of loan paid back is called rest.  For Ex:

If you were charged a 10 per cent annual reducing balance rate on loan of Rs 1 lakh (1,00,000) you would pay

  1. In first year loan amount is 1,00,000, interest  so Rs 10,000 as interest, so payment made is 20,000 + 10,000. Loan amount at end of first year becomes 1,00,000 – 20,000 = 80,000
  2. In the second year the interest is Rs 8,000(10% of 80,000)  amount paid is 20,000+8000, loan amount becomes 80,000 – 20,000 = 60,000
  3. In the third year the interest is Rs 6,000(10% of 60,000)  amount paid is 20,000+6000, loan amount becomes 60,000 – 20,000 = 40,000.
  4. In the fourth year the interest is Rs 4,000(10% of 40,000)  amount paid is 20,000+4000, loan amount becomes 40,000 – 20,000 = 20,000
  5. In the fifth year the interest is Rs 2,000(10% of 20,000)  amount paid is 20,000+2000, loan amount becomes 20,000 – 20,000 = 0

That is, over the tenure of the loan you would end up paying Rs 1,00,000 + 10,000 + 8,000 + 6,000 + 4,000 + 2,000 = 1,30,000. Compared to flat rate this method of computation results in  Rs 20,000 less.

Annual rest or Annual reducing loans: The  outstanding loan amount  is recalculated at the end of every year. That is, even though the borrower pays his EMI every month and the loan balance reduces every month, the outstanding loan amount is not adjusted till the end of the year.

Monthly Rest or  Monthly reducing loan: The outstanding loan amount is recalculated at the end of each month. That is, the outstanding loan amount on which the interest is charged goes down every month.

Daily Reducing Loan: in which the outstanding loan amount is reduced every day. Of course, this is not always possible because of the impracticality of making daily payments ,you don’t repay your loan in equated daily installments or EDIs! The benefit of the daily reducing cycle comes into play when you are prepaying a loan. Suppose your EMI is due on the 7th of every month. A week after paying the EMI, you make a partial prepayment of the loan. In the monthly reducing cycle, your prepayment will be taken into account only when the next EMI is paid. But in the daily reducing cycle, you will get the benefit of the prepayment immediately. The outstanding balance of your loan will get reduced on the 14th instead of the 7th of the next month.

How different reduction cycles affect your borrowing costs?

The following table shows EMIs on a Rs 1 lakh loan for two years at 10%. Ref:Business Today:What does a monthly reducing loan mean?

Reducing cycle EMI Total payment Interest outgo
Annual 4,802 1,15,248 15,248
Monthly 4,614 1,10,736 10,736
Daily 4,535 1,08,840 8,840
All figures are in RsEMIs on a Rs 1 lakh loan for two years at 10%

As the table above shows, a borrower’s outgo is lowest in the daily reducing method. In comparison, in the the annual reducing method, he has to pay almost double the interest he pays on a daily reducing cycle.

Annual reducing loans are rare. Most home, vehicle and personal loans are computed on a monthly reducing basis.

Fixed, Floating Rate Loan

We often hear terms like Fixed Rate Loan, Floating Rate Loan especially related to Home Loans. Let’s learn about them.

Fixed Rate Loan: The rate of interest is fixed either for the entire tenure of the loan or a certain part of the tenure of the loan. In case of a pure fixed loan, the EMI due to the bank remains constant throughout the tenure of the loan. But in case of interest rate fixed for tenure of loan the rate of interest could change in order to keep in accord with the market condition. This loan is taken by people who prefer to know the exact cost of the loan and are happy to pay a fixed amount or when there is an expectation of a rise in the market value. If the inflation and the interest rate in the economy move up over the years, a fixed EMI is attractively stagnant and is easier to plan for. However, for fixed EMI, any reduction in interest rates in the market, does not benefit borrower.

Floating Rate Loan : The rate of interest is not fixed during the tenure of the loan, interest rate fluctuates with the change in the rates in the economy. Hence the EMI of a floating rate loan changes with changes in market interest rates.  The floating interest rate is made up of two parts: the index and the spread. The index is a measure of interest rates generally (based on say, government securities prices), and the spread is an extra amount that the banker adds to cover credit risk, profit mark-up etc. The amount of the spread may differ from one lender to another, but it is usually constant over the life of the loan. If the index rate moves up, so does the interest rate in most circumstances and one has to pay a higher EMI. Conversely, if the interest rate moves down, EMI amount should be lower.Also, sometimes banks make some adjustments so that your EMI remains constant. In such cases, when a lender increases the floating interest rate, the tenure of the loan is increased and EMI kept constant. The borrower can gain form this type of interest charge when there is a dip in the rate of interest, bringing down the cost of the loan.

Features of EMI

Quoting from CIEL’s 5 things to know about EMI

  1. The EMI depends on three factors: loan amount, interest rate and the duration of the loan
  2. The EMI is decided when the loan is sanctioned and remains constant throughout the period of the loan, provided there is no change in any of the factors on the basis of which it is calculated.
  3. The EMI has an interest and a principal portion. Through the principal, the borrower repays the loan each month. Through the interest, he pays the bank the interest due on the outstanding loan amount.
  4. The EMIs are structured in such a way that the interest portion forms a major part of the payment that is made in the initial years. In the later years, the principal component becomes high.
  5. The EMI can change in the case of an alteration in interest rates or if there is a prepayment. It is also possible to keep the EMI constant and increase or decrease the tenure of the loan to reflect the changes in interest rates or loan prepayment.

 Loan Amortization Schedule 

The loan amortization schedule is a table containing home loan information such as period of scheduled payments, amount borrowed and amount outstanding. It also details the breakup of every EMI towards repayment interest and the outstanding principal of the loan.

Suppose Mrs & Mr R S Sharma take a home loan/mortgage of Rs 20 lakh(20,00,000). Loan is to be repaid over 10 years, Interest is at an annual rate of 10%. The EMI or their monthly payment is 26,430. Total amount that they pay is: Rs 31,71,600. Remember that they had taken loan for Rs 20,00,000 only. The rest of amount Rs 11,71,600 was paid as interest as shown in picture below.

Loan details with Amortization Schedule

Loan details with Amortization Schedule

Monthly view of the amortization table for all 120 months i.e 10 years can be seen here

Formula of EMI

Formula to calculate EMI for monthly reducing balance is as follows:

EMI Formula

EMI Formula

Suppose loan details are as follows

  • Amount E Rupees,
  • Tenure of the loan is n months (for example, n=240 for a 20-year loan),
  • Monthly rate of interest is r (usually calculated by dividing the annual rate of interest by 12, the number of months in a year, and dividing that by 100 as the rate is usually quoted as a percentage)
  • E  is the EMI you have to pay every month.
  • Let us use Pi to denote the amount you still owe to the bank at the end of the i-th month.

At the very beginning of the tenure, i=0 and P0=P, the principal amount you took on as a loan.

At the end of the first month, you owe the bank the original amount P, the interest accrued at the end of the month r×P and you pay back E. In other words:

P1 = P + r×P – E or P1 = P×(1 + r) – E

Similarly, at the end of the second month the amount you still owe to the bank is:

P2 = P1×(1 + r) – E

 Substituting the value of P1 we calculated earlier:

P2 = (P×(1 + r) – E)×(1 + r) – E

Once again expanding it, rearranging it and rewriting it slightly differently:

EMI formula p2To make this look slightly simpler, we substitute “(1 + r)” by “t” and now it looks like this:

p2 in terms of tContinuing in this fashion and calculating P3, P4, etc. we quickly see that Pi is given by:

pi in terms of tAt the end of n months (that is, at the end of the tenure of the loan), the total amount you owe to the bank should have become zero. In other words, Pn=0. This implies that:

pn in terms of twhich means that:

pn rearranged

We can simplify this further by noticing that we have a of n terms here with a common ratio of t and a scale factor of 1. The sum of such a series is given by “(tn – 1)/(t – 1)”, which we substitute in the above equation to yield:

pn in terms of geometric serieswhich can be rewritten as:

E in terms of tn

by substituting the value of t back as (1 + r) E can again be rewritten as:

EMI in terms of r

and this is the formula for calculating your EMI. Our EMI Calculator uses this formula to calculate EMI.

EMI Table in Microsoft Excel

In Excel function PMT(Payment) – calculates the EMI payable per month for monthly reducing balance. The formula of PMT is

PMT(Rate,Nper,Pv,Fv,Type)

  • Rate is the interest rate for the loan. Rate should be per installment. i.e. if installment is payable monthly then rate must be rate per annum / 12 months ex:If rate of interest per annum is 12% then rate per month is 1%.
  • Nper is the total number of payments for the loan or installments
  • Pv is the present value, or the amount borrowed also known as the principal.
  • Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.
  • Type is the number 0 (zero) or 1 and indicates when payments are due.
    • 0 Payments are due at the end of the period. (default)
    • 1 Payments are due at the beginning of the period.

To find EMI for a loan of 1,00,000 ( 1 lakh) at 12% interest p.a or 1% per month i.e 12/1200 for 1 year PMT will be

EMI = PMT = (12/1200,12,100000,0,0)

= -8884.88

In Excel “in” and “out” cash flows must have opposite signs. In means you get, out means you pay. With the given input values answer is -(negative) as it means you have to pay.  But the sign that you choose for “in” is arbitrary. If you make principal as – then EMI becomes +.

EMI = PMT = (12/1200,12,-100000,0,0)

= 8884.88

One can make the amortization table using Excel. The amortization schedule for loan of 1 lakh(1,00,000) with fixed annual interest of 12% per annum, tenure of 12 months or 1 year with monthly reducing balance is shown in picture below

Amortization schedule in excel

Amortization schedule in excel

The formulas used to get the table in excel for above picture are shown in picture below.

Amortization schedule in excel formula

Amortization schedule in excel formula

Secured, Unsecured  Loans

Loans can be given against a security or asset or not. Based on this two kinds of loans are Secured, Unsecured  Loans.

Secured Loans : Loan that is supported or borrowed against an asset you own such as your home, which is offered up as collateral, is known as a secured loan.  Collateral is something of value – an asset or property – that you pledge when getting a loan. If you don’t repay the loan as agreed, the lender can take your collateral and sell it.If in any case the borrower fails to comply with the loan amount, the lenders may take possession of the asset or property collateral to the loan. Here, there is less chance of risk. Examples of secured loans:

  • Car loans
  • Mortgages /Home loans

Unsecured loan : These are as the name suggests , not secured against any assets. The bank can only utilize collectors (and freeze your accounts) if you default. Examples of Unsecured Loans:

  • Personal loans
  • Student loans
  • Credit cards/department store cards

Loan and Credit History

These days sanctioning of loan, at what interest rate loan will be made available is determined by credit score of the applicant. Earlier, lenders relied on their own internal assessment of a customer before sanctioning loans or even credit cards. With the availability of individual’s credit history through various credit agencies has enabled lenders to determine creditworthiness of the applicant. So it has become critical  to monitor your score to ensure that your loan does not get rejected on the grounds of a poor credit score.

Credit Information Report is the credit history  – the loans you have taken, the outstanding loan amount on a particular date, the EMIs you’ve paid on time as well as the EMIs you missed out on. Other details which are compiled in the credit report are the number of enquiries you made with different bankers and credit card companies for loans. Defaults on all types of loan payments are reflected in your Credit Information Report.

You can get your credit report from three agencies—

While all three reports are unique in their own way, each has elaborate information on your finances. While one bank may subscribe to Cibil, another to Experian and the third to Equifax.

Credit Information report:CIBIL, Equifax, Experian

Credit Information report:CIBIL, Equifax, Experian

Of the three CIBIL is quite popular. Sample CIBIL report(pdf)  Video explaining Cibil report , onemint’s explanation of Cibil Report

Related :

Borrowing money is a huge responsibility and you need to consider whether it is the correct decision for you. Borrowing money means that you are committing to pay the money back within a certain time frame. You need to consider how borrowing money will affect your finances in the future.The last thing one needs   is a mountain of debt looming over them.

Share