InvestorQ : How do i calculate my monthly EMI ?
Rohan Bhadani made post

How do i calculate my monthly EMI ?

Ankita Patil answered.
3 years ago

One of the easiest ways of calculating the EMI is by using the Excel spreadsheet. In Excel, the function for calculating the EMI is PMT and not EMI. You need three variables. These are rate of interest (rate), number of periods (nper) and, lastly, the value of the loan or present value (pv).

The formula which you can use in excel is:


Let us check the EMI of Mr. X by using the above formula.

It must be noted that the rate used in the formula should be the monthly rate, that is, 12%/12=1% or 0.01.

The number of periods represents the number of EMIs.

=PMT(0.12/12, 4*12, 445,000)= 11,718

The result will come in negative or red, which indicates the cash outflow of the borrower.

Let's take another example. Suppose you are paying a quarterly instalment on a loan of Rs 10 lakh at 10% interest per annum for 20 years. In such a case, instead of 12, you should divide the rate by four and multiply the number of years by four. The equated quarterly instalment for the given figures will be =PMT(10%/4, 20*4, 10,00,000).