EMI calculation in Excel

Process of EMI calculation in Excel

1. EMI
08-05-2017
Prof. Naveen, [email protected],
[phone]
1

2. What is EMI
EMI stands for Equated Monthly Installment
A fixed payment amount made by a borrower to
a lender at a specified date each calendar
month.
08-05-2017
Prof. Naveen, [email protected],
[phone]
2

3. What is EMI
Equated monthly instalments
They are used to pay off both interest and
principal each month, so that over a specified
number of years, the loan is paid off in full.
=
08-05-2017
Prof. Naveen, [email protected],
[phone]
3

4. What is EMI
It further explains that, with most common types of
loans, such as real estate mortgages....
The borrower makes fixed periodic payments to the
lender over the course of several years with the
goal of retiring the loan.
08-05-2017
Prof. Naveen, [email protected],
[phone]
4

5. EMI
The benefit of an EMI for borrowers:-
1.Exact amount every month is known
2. Easier personal budgeting process
08-05-2017
Prof. Naveen, [email protected],
[phone]
5

6. How to calculate EMI?
08-05-2017
Prof. Naveen, [email protected],
[phone]
6

7. Let’s take an Example
Mr. Rich borrows Rs 1800000 for his site from
IDBI Bank.
Rate of interest is 11%
Tenure is 10 years
What is the EMI amount he has to pay every
month to the bank?
08-05-2017
Prof. Naveen, [email protected],
[phone]
7

8. 1800000
120
0.92% 08-05-2017
Prof. Naveen, [email protected],
[phone]
8

9. Have you got Rs 24795?
If so you are right
08-05-2017
Prof. Naveen, [email protected],
[phone]
9

10. •Mr. Car lover buys a car for 50,00,000. He pays
cash of Rs 20,00,000. He borrows Rs 30,00,000
from HDFC bank. The rate interest is 10%.
The loan is for 3 years.
Calculate the EMI.
08-05-2017
Prof. Naveen, [email protected],
[phone]
10

11. Now try using = PMT formula is Excel
08-05-2017
Prof. Naveen, [email protected],
[phone]
11

12. PMT
•Rateis the interest rate for the loan.
•Nperis the total number of payments for the loan.
•Pvis the present value, or the total amount that a series
of future payments is worth now; also known as the
principal.
•Fvis 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.
•Typeis the number 0 (zero) or 1 and indicates when
payments are due.
•Set type equal to If payments are due 0 or omitted At the
end of the period 1 At the beginning of the period
08-05-2017
Prof. Naveen, [email protected],
[phone]
12

13. Notes
•Make sure that you are consistent about the units
–Rate
–nper
•If you make monthly paymThepayment returned by
PMT includes:-
–Principal and interest
–but no taxes, reserve payments, or fees
entson a four-year loan at an annual interest rate of 12
percent, use 12%/12 for rate and 4*12 for nper.
08-05-2017
Prof. Naveen, [email protected],
[phone]
13

14. EMI calculation using Excel Template
08-05-2017
Prof. Naveen, [email protected],
[phone]
14

15. 08-05-2017
Prof. Naveen, [email protected],
[phone]
15