How to Split Principal and Interest in EMI in Excel: 4 Methods

Method 1 – Calculate EMI

We can use the PMT function to calculate EMI and then produce the EMI calculator. To do this, we take some values. With these values, we calculate our EMI, and at the same time, we produce the EMI calculator.

  • We can easily calculate the monthly interest as we have our principal amount, interest, and tenure.
  • Take a single worksheet where you would like to calculate the EMI.
  • Create row headers, including EMI, interest, principal, and principal remaining.
  • The principal remaining is the total amount of the loan that you take.
  • After 12 months, the principal remaining must be zero. That’s the ultimate target.

  • Select cell C6.
  • To calculate the EMI, write down the formula using the PMT function.
=-PMT($I$5/12,$I$6,$I$4,0,0)

Calculate EMI to Split Principal and Interest

Note

  •  As you need to pay this money every month until you finish the loan payment, the PMT function will give you a negative result. That’s why we use negative before the function to get a positive value.
  • Divide the interest rate by 12 to get the monthly interest rate. It will be calculated annually.
  • Press Enter to apply the formula.

  • The most important thing to remember is that you need to have the same EMI for every value. If you look at the formula, you will find all the cells are made constant by using the ($) sign.
  • Drag the Fill Handle icon down the column to cell C17. Our EMI for that specific loan amount and interest rate.

Estimate EMI to Split Principal and Interest


Method 2 – Estimate Interest

  • Select cell E6.
  • Write down the following formula.
=$I$5/12*F5

Split Interest in EMI in Excel

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon down the column.
  • Don’t worry about having zero because it will get updated when you estimate the other columns.

Extract Interest from EMI in Excel


Method 3 – Calculate Principal Amount from EMI in Excel

  • Select cell D6.
  • Write down the following formula.
=C6-E6

Split Principal in EMI in Excel

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon down the column.
  • This is not the final principal amount because estimating the remaining principal amount will give you the actual result.

Extract Principal from EMI in Excel


Method 4 – Calculate Principal Remaining Amount

  • Select cell F6.
  • Write down the following formula.
=F5-D6

Calculate Principal Remaining Amount in Excel

  • Press Enter to apply the formula.

  • Drag the Fill Handle icon down the column.

  • Get actual values of interest and principal amount in EMI. Get the remaining loan amount as zero which implies the calculation is correct.

  • It produces a calculator. The whole table will alter accordingly if you change the loan amount, interest rate, or duration.
  • We set the loan amount to $60000 and the interest rate to 12%. We will get the following results.


Things to Remember

  • After splitting the principal and interest amount, we have to get the remaining loan amount as zero. Otherwise, the calculation is counted as incorrect.
  • Before calculating EMI and interest amount, you have to make sure that you take the monthly interest by dividing the annual interest rate by 12.

Download Practice Workbook

Download the practice workbook below.


Related Articles


<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo