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

Method 1 – Calculate EMI

Use the PMT function to calculate EMI and then produce the EMI calculator, taking some values. With these values, calculate the EMI, and at the same time and produce the EMI calculator.

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)

Note

Pay this money every month until you finish the loan payment; the PMT function will give you a negative result. 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.

You need to have the same EMI for every value. Find all the cells that 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.

Method 2 – Estimate Interest

Select cell E6.
Write down the following formula.

=$I$5/12*F5

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.

Method 3 – Calculate the Principal Amount from EMI in Excel

Select cell D6.
Write down the following formula.

=C6-E6

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.

Method 4 – Calculate Principal Remaining Amount

Select cell F6.
Write down the following formula.

=F5-D6

Press Enter to apply the formula.

Drag the Fill Handle icon down the column.

Get the actual values of interest and principal in EMI. The remaining loan amount should be zero, which implies that the calculation is correct.

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

Things to Remember

After splitting the principal and interest amount, we must get the remaining loan amount to zero. Otherwise, the calculation is incorrect.
Before calculating the EMI and interest amount, you must ensure that you take the monthly interest by dividing the annual interest rate by 12.