EMI

EQUATED MONTHLY INSTALMENT

It is a fixed payment made by a borrower to a lender.

It covers principal and interest.

Interest is payable only on outstanding loan amount.

EMI = (P*r) (1+r)ⁿ

                  (1+r)ⁿ-1

Where:

P = Principal

r = rate of interest per instalment period

n = number of instalments in the tenure

Question.1

Ram took a loan for Rs. 1,00,000/- at an interest rate of 12% per annum for a period of 12 months. Find out the EMI and prepare a schedule of repayments.

Solution:

DATA
Principal (P)	1.00.000
Interest rate per annum	0.12
Number of instalments (n)	12
Rate of interest per instalment period (r )= 0.12/12	0.01

EMI = (1,00,000 * 0.01) (1+0.01)¹²

                (1+0.01)¹²- 1                         

        = 1,000 * (1.01)¹²

            (1.01)¹²- 1

        = 1,000 * 1.126825

           1.126825 -  1

        = 1,000 * 1.126825

           0.126825

        = 1126.825

         0.126825

        = Rs. 8,885

HOW TO CALCULATE EQUATED MONTHLY INSTALMENTS (EMI) IN MICROSOFT EXCEL

The function PMT is used to calculate EMI in MS Excel. It requires 5 inputs. They are

INPUT	MEANING	VALUES TO BE GIVEN IN MS EXCEL FOR THE ABOVE EXAMPLE
Rate	Rate means interest rate per instalment	0.12/12
Nper	Nper means total number of instalments of loan	12
pv	Present Value or the total amount that a series of future payments is worth now	1,00,000
fv	Future value or cash balance that you want to achieve after the last payment is made. If future value is omitted it is assumed as zero	0
type	Type means when payments are due. Type zero or omitted means the payments are due at the end of the period. Type one means payments are due at the beginning of the period	0

The function IPMT is used to calculate Interest in MS Excel. It requires 5 inputs. They are

INPUT	MEANING	VALUES TO BE GIVEN IN MS EXCEL FOR THE ABOVE EXAMPLE
Rate	Rate means interest rate per instalment	0.12/12
Per	Per is the period for which you want to find the interest. It means one instalment. Hence give it 1.	1
Nper	Nper means total number of instalments of loan	12
pv	Present Value or the total amount that a series of future payments is worth now (Principal)	1,00,000
fv	Future value or cash balance that you want to achieve after the last payment is made. If future value is omitted it is assumed as zero	0

 Schedule of Repayment

MONTH   LOAN                           EMI       INTEREST  PRINCIPAL   LOAN AT END              OUTSTANDING              AT THE              BEGINNING

Jan-15                                   100,000         8,885         1,000         7,885                           92,115 Feb-15                                    92,115         8,885            921         7,964                           84,151 Mar-15                                     84,151         8,885            842         8,043                           76,108 Apr-15                                     76,108         8,885            761         8,124                           67,984 May-15                                     67,984         8,885            680         8,205                           59,779 Jun-15                                     59,779         8,885            598         8,287                           51,492 Jul-15                                     51,492         8,885            515         8,370                           43,122 Aug-15                                     43,122         8,885            431         8,454                           34,668 Sep-15                                     34,668         8,885            347         8,538                           26,130 Oct-15                                     26,130         8,885            261         8,624                           17,507 Nov-15                                     17,507         8,885            175         8,710                              8,797 Dec-15                                       8,797         8,885               88         8,797                                      0