How to calculate EMI in excel with Principle and the Interest components | DataWitzz

How to calculate EMI in excel

How to calculate EMI is most annoying thing when we take loan or planning to take loan. It also very difficult when we wanted to check interest and loan amount components spread over EMI tenure. Though there multiple online stuffs are available to calculate EMI but the spread over EMI tenure is very early. I just tried to make it excel which will surely help you.

Let us first learn How to calculate EMI in excel :

In excel to calculate EMI we would use PMT function of excel. Syntax for PMT function is as below:

PMT function requires below input to calculate EMI:

• rate: interest rate of loan per month
• nper: Tenure of loan in month
• pv: Present value of loan or Loan Amount
• [fv] – optional: The future value or cash balance that you want at the end of loan tenure. The default value would be 0 at then end of loan tenure
• [type] -optional : It means when payments are due. 0 = end of the period. 1 = beginning of the period. Default is 0. 

Now lets understand it by an example as illustrated in the below image:

In the above example we took loan amount as 1010000, interest rate 11.25% yearly and tenure of loan 60 months. Before using this formula we will first convert yearly interest rate into month by dividing it to 12. Not we will use the PMT formula and put inouts accordingly. The value in cell B6 reflects EMI value.

After calculating EMI value now we will spread it in sequence of the tenure. To create a format like this we will first start will loan tenure number from 1 to last tenure number in a column and will name it EMI Number. Next we will create another column Opening Loan Amount which starts with Actual loan amount in 1st month and subsequently principle and interest of earlier month will be deducted.

Next column would be Interest Amount which actually shows every months interest outgo in EMI. It can be calculated by using Opening Loan Amount and multiplied it by monthly interest rate. After that EMI value column would be added and the last column would be the final outstanding loan amount. For the sake of more convenience we also added Month-Year.

The final format would be like as below:

Month-Year	EMI no.	Opening Loan Amount	Interest Amount	EMI	Closing Loan Amount
Aug-19	1	₹ 10,10,000	₹ 9,469	₹ 22,086	₹ 9,97,383
Sep-19	2	₹ 9,97,383	₹ 9,350	₹ 22,086	₹ 9,84,647
Oct-19	3	₹ 9,84,647	₹ 9,231	₹ 22,086	₹ 9,71,792
Nov-19	4	₹ 9,71,792	₹ 9,111	₹ 22,086	₹ 9,58,817
Dec-19	5	₹ 9,58,817	₹ 8,989	₹ 22,086	₹ 9,45,720
Jan-20	6	₹ 9,45,720	₹ 8,866	₹ 22,086	₹ 9,32,500

You can down Excel EMI calculator from below link. To use it correctly put your input values only against green highlighted cells.

You can read more from the below link on How to calculate EMI in excel .

ahttps://www.exceldemy.com/flat-and-reducing-rate-of-interest-calculator-in-excel/