Reducing Balance EMI Calculator in Excel Sheet (Create with Easy Steps)

This article illustrates how to create a reducing balance EMI calculator Excel sheet. The rate of interest diminishes with time in the case of loans based on reducing balance EMI. Therefore, taking loans based on reducing balance EMI can be a better deal than on fixed balance EMI.

For example, the EMI amount for a $5000 loan at 9% fixed interest rate for a 12 month period will be = $5000/12 + $5000*9%/12 = $454.17. On the other hand, the EMI amount at the reducing balance interest rate will be $437.26. Therefore, it can be a better deal to take a loan at the reducing balance interest rate even if the interest rate is a little higher. Follow the article to create a reducing balance EMI calculator in your Excel sheet to find out the better deal for yourself.


Reducing Balance EMI Calculator in Excel Sheet: Create with Easy Steps

Assume you have secured a loan of $1000 at the annual rate of 14.49%. You can repay the loan in 12 equated monthly installments (EMI).

Follow the steps below to create a reducing balance EMI calculator Excel sheet using that information.


📌 Step 1: Calculate EMI Amount with PMT Function

  • First, 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)

Reducing Balance EMI Calculator


📌 Step 2: Estimate Total Amount Payable

  • Then enter the following formula in cell E9 to calculate the total payable amount.
=E8*E6


📌 Step 3: Calculate Total Interest

  • Next 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

  • Now enter 1 to 12 in cells B13 to B24 respectively. After that, enter the following formula in cell C13.
=E4

  • Then, enter the following formula in cell D13.
=E9

  • Next, apply the following formula in cell E13 to calculate the monthly payment need to be made on the principal loan amount. Then 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.
=-PPMT($E$5/12,B13,$E$6,$E$4,0,0)

Reducing Balance EMI Calculator

  • After that, enter the following formula in cell F13 to calculate the monthly payment need to be made on the interest. Then, 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.
=-IPMT($E$5/12,B13,$E$6,$E$4,0,0)

Reducing balance EMI calculator

  • Now 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 as it must be.
=SUM(E13:F13)

  • Now 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

  • After that, enter the following formula in cell I13 to calculate the total amount payable at the end of the month. Then, drag the Fill Handle icon down to cell I24.
=D13-G13

  • Then enter the following formula in cell C14. Next, drag the Fill Handle icon down to cell C24.
=H13

  • Then enter the following formula in cell D14. After that, drag the Fill Handle icon down to cell D24.
=I13


📌 Step 5: Finalize the EMI Calculator Sheet

  • Finally, you will see the following result.

reducing balance emi calculator excel sheet

  • Now enter the following formula in cell E25 to verify the results. Then drag the Fill Handle icon to cell G25. Then 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.
=SUM(E13:E24)

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 Sample Workbook

You can download the sample workbook from the download button below.


Conclusion

Now you know how to create a reducing balance EMI calculator in an Excel sheet. Do you have any further queries or suggestions? Please let us know using the comment section below. Stay with us and keep learning.


Related Articles


<< Go Back to EMI CalculatorFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo