How to Split Principal and Interest in EMI in Excel (with Easy Steps)

When you take out a big amount loan for your personal problem or other problem, you have to repay it through a monthly installment process. The monthly installment can be defined as EMI. But when you split EMI, you will get the principal and interest amounts. The interest will go to the bank and the principal amount will be subtracted from the remaining loan balance. This article will show you how to split principal and interest in EMI in Excel.

Overview of EMI, Principal and Interest

When we take a large amount of loan from a bank, we are aware of the terms EMI, principal, and interest. All of these terms have a strong weight in terms of financial statements.

EMI

EMI means the equated monthly installment. The equated monthly installment is a payment that can be paid by the borrower to the lender at a specific date. EMI is also applied interest and principal each month so that over a certain period of time it can be paid off. So, when someone takes a loan for a personal purpose, he/she must have paid this over a certain period of time. But, when we consider the monthly payment that is the EMI.

Principal

The principal can be defined as a part of EMI through which you can pay the remaining loan amount. This amount will be given to the bank to repay your loan amount.

Interest

The interest amount can also be defined as a part of EMI through which you can get the interest using the product of monthly interest rate and loan amount. This amount will be paid as an interest amount on the given loan amount.

Step-by-Step Procedure to Split Principal and Interest in EMI in Excel

We have found step-by-step procedures to split principal and interest in EMI in Excel. All of these steps will show us how to split them effectively. Firstly, you need to calculate the EMI from the given data. As you have the loan amount, monthly interest rate, and duration, it will be quite easy to calculate the EMI from these data. After that, we calculate the interest from the given data. Finally, we estimate the principal amount by subtracting interest from EMI. To do the whole calculation, we take a dataset where we get the loan amount, duration, and interest rate.

Step 1: Calculate EMI

We can use the PMT function to calculate EMI and then produce the EMI calculator. This process is easier than the previous method. To do this, we take some values. By using these values, we calculate our EMI and at the same time, we produce the EMI calculator.

As we have our principal amount, interest, and tenure, we can easily calculate the monthly interest.
Now, take a single worksheet where you would like to calculate the EMI.
Then, create some row headers including EMI, interest, principal, and principal remaining.
Primarily, 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.

Now, 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)

📑 Note

As you need to pay this money every month until you finish the loan payment, the PMT function will provide you with a negative result. That’s why we use negative before the function to get a positive value.
Here, we divide the interest rate by 12 to get the monthly interest rate. Otherwise, it will be calculated on an annual basis.

Press Enter to apply the formula.

Now, the most important thing to remember is that you need to have the same EMI for every value. So, if you look at the formula, you will find all the cells are made constant by using the ($) sign.
Then, drag the Fill Handle icon down the column up to cell C17. That is our EMI for that specific loan amount and the specific interest rate.

Step 2: Estimate Interest

In our next, We would like to calculate the interest amount. To calculate the interest amount, we need to utilize the principal remaining amount and the monthly interest rate.

Select cell E6.
Then, write down the following formula.

=$I$5/12*F5

Press Enter to apply the formula.

After that, 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.

Step 3: Calculate Principal Amount from EMI in Excel

Next, we would like to calculate the principal amount from EMI in Excel. As we have EMI and interest amount, so, it’s going to be an easy task to calculate the principal amount.

Select cell D6.
Write down the following formula.

=C6-E6

Press Enter to apply the formula.

After that, drag the Fill Handle icon down the column.
This is not the final principal amount because when you estimate the remaining principal amount, you will get the actual result.

Step 4: Calculate Principal Remaining Amount

In the final case, we would like to check whether the remaining amount becomes zero or not after the duration. The interest amount will go to the bank. However, the principal amount will be subtracted from the remaining loan amount. So, when you get the zero loan amount after the end of the period, you can count the calculation as correct.

First, select cell F6.
Write down the following formula.

=F5-D6

Press Enter to apply the formula.

After that, drag the Fill Handle icon down the column.

As a result, you will get actual values of interest and principal amount in EMI. At the end of the period, you will get the remaining loan amount as zero which implies the calculation is correct.

Finally, it produces a calculator. If you change the loan amount, interest rate, or duration, the whole table will alter accordingly.
Here, 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.

Split Principal and Interest in EMI.xlsx

Conclusion

To split principal and interest in EMI in Excel, we have shown step-by-step procedures through which you can get a complete overview of how to do the job. All the steps are fairly easy to understand and the whole calculation creates a calculator. If you change the data, the whole table will change accordingly. If you have any questions, feel free to ask in the comment section.

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

Get FREE Advanced Excel Exercises with Solutions!