I wrote about how EMI is calculated and how to calculate EMI using MS Excel. It is not a big surprise to me that these are often the most viewed posts on my blog. Some of the readers commented on giving an example along with the posts, so here is the post explaining EMI with an example:
Example 1: Assume that you take a loan of Rs 100,000 at 8.75% per annum rate for a tenure of 10 years. How to calculate the EMI?
Choose the Financial category and select the PMT function.
In the function arguments, enter the values. In our example, our interest rate is 8.75%, so enter “8.75%/12” The Nper is the tenure in terms of months, in our example it will be 120 months, the Pv is the Loan amount and keep Fv & Type to 0. Press OK.
When you press OK, you will see the result in the selected cell as shown. The EMI is Rs 1253.27 for our example.
What is the interest component of say 10th EMI?
Proceed as in above, but choose the IPMT function rather than the PMT function. The following inputs are required:
The only new input is the Per, which is the EMI for which you want to know interest component. In our example, we want to know the interest component for the 10th EMI, so input as 10. The result is Rs 693.75.
So, when you will pay the 10th EMI of Rs 1253.27, you are paying Rs 693.75 as interest component and remaining for reducing the principal.
What is the principal component of 10th EMI?
It works same as above except that you need to choose the PPMT function rather than PMT function.
If you want to know the principal and interest amount for each of your EMIs, then there are many calculators (excel sheets) available on internet. I like this advanced excel calculator since it takes into account any additional payment made.