Assume you have secured a loan of $1,000 at the annual rate of 14.49%. You can repay the loan in 12 equated monthly installments (EMI).
Step 1 – Calculate EMI Amount with PMT Function
- Enter the following formula in cell E8 to estimate the EMI amount. Here the PMT function returns negative numbers. Therefore a negative sign has been used at the beginning of the formula.
=-PMT(E5/12,E6,E4,0,0)
Step 2 – Estimate Total Amount Payable
- Enter the following formula in cell E9 to calculate the total payable amount:
=E8*E6
Step 3 – Calculate Total Interest
- Enter the following formula in cell E10 to get the total interest:
=E9-E4
Read More: Home Loan EMI Calculator with Reducing Balance in Excel
Step 4 – Create a Reducing Balance EMI Table
- Input 1 to 12 in cells B13 to B24, respectively.
- Insert the following formula in cell C13:
=E4
- Enter the following formula in cell D13:
=E9
- Apply the following formula in cell E13 to calculate the monthly payment need to be made on the principal loan amount.
=-PPMT($E$5/12,B13,$E$6,$E$4,0,0)- Drag the Fill Handle icon down to cell E24. Here, the PPMT function also returns negative values as it considers the payment as cash outflow.
- Enter the following formula in cell F13 to calculate the monthly payment need to be made on the interest:
=-IPMT($E$5/12,B13,$E$6,$E$4,0,0)
- Drag the Fill Handle icon down to cell F24. Here, the IPMT function also considers the payments as cash outflow. So a negative sign is used at the beginning to avoid a negative result.
- Enter the following formula in cell G13 to calculate the total monthly payment. You can see that the result is equal to the EMI amount.
=SUM(E13:F13)
- Enter the following formula in cell H13 to calculate the remaining loan balance at the end of the month, then drag the Fill Handle icon down to cell H24.
=C13-E13
- Enter the following formula in cell I13 to calculate the total amount payable at the end of the month and drag the Fill Handle icon down to cell I24.
=D13-G13
- Enter the following formula in cell C14 and drag the Fill Handle icon down to cell C24:
=H13
- Use the following formula in cell D14 and drag the Fill Handle icon down to cell D24.
=I13
Step 5 – Finalize the EMI Calculator Sheet
- You will see the following result.
- Enter the following formula in cell E25 to verify the results.
=SUM(E13:E24)- Drag the Fill Handle icon to cell G25.
- You will see that the total payment made on the loan, the total payment made on interest, and the total amount payable match the results calculated earlier.
Note:
Don’t forget to use a negative sign before the formulas containing the financial function to avoid any negative results.
Read More: How to Create Reverse EMI Calculator in Excel
Download the Sample Workbook
Related Articles
- Personal Loan EMI Calculator Excel Format
- SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- How to Make Two Wheeler Loan EMI Calculator in Excel
- Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option
- EMI Calculator with Prepayment Option in Excel Sheet
<< Go Back to EMI Calculator | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
