Home Loan EMI Calculator with Reducing Balance in Excel

EMI, or Equated Monthly Installment the amount to be paid every month in the bank or other institution that over time makes up for the total loans and interest. It is a very common form of purchase too in many different sectors for various kinds of products. EMIs are done for a certain period and the interest amount and total cost vary with the rate of interest and EMI period. So EMI calculators come in handy for such measurements. In this tutorial, we are going to make a home loan EMI calculator reducing Balance in Excel.


Download Practice Workbook

You can download the workbook containing the calculator created in the example from the link below. You can also use it as a template for your home loan EMI calculations.


Step-by-Step Procedure to Make Loan EMI Calculator with Reducing Balance in Excel

A home loan EMI calculator consisting of reducing balance consists of three main sections- a place where you can input your loan values, a summary or report of the loan, and a monthly breakdown of the loan and interests that also includes the reducing balance after each transaction. Below are the steps we can take to make a template for the home EMI calculator with the reducing balance in Excel.


Step 1: Create Dataset for Loan Values

First of all, let’s create the input section of the home loan EMI calculator. An input section can contain the loan taken, down payment, insurance, interest, duration of the month, start and end date, etc.

Let’s add a chart that can contain the parameters.

home loan emi calculator reducing balance excel

If you have other parameters like loan taken, insurance, etc. you can calculate the total loan left and input it here.

Follow these steps to add formats to the cells. Keep in mind that these formats are very important as depending on the percentage or not, formulas used in the later steps change.

  • Firstly, select cell D5 and press Ctrl+1 to open up the Format Cells
  • Then select the Currency format from the Number tab and select the preferred decimal places. We are selecting two decimal places for our example here. After that, click on OK.

  • Secondly, select cell D6 and press Ctrl+1 on your keyboard.
  • Then select the Percentage format from the Number tab and click on OK.

  • Finally, select cell D8 and press Ctrl+1 on your keyboard.
  • Then select the Date format from the Number tab in the Format Cells box and select the date type you prefer. After that, click on OK.

Now our input section is complete for later usage.

Read More: Create Home Loan EMI Calculator in Excel Sheet with Prepayment Option


Step 2: Construct Columns for Payment Number and Date

Now let’s start making the EMI calculator for each payment that also returns the reducing balance of the loan. At first, we should make columns to track down payment nos and when the payment is due.

  • So let’s make these columns under the previous chart first.

home loan emi calculator reducing balance excel

  • Then select cell B11 and write 1 in it. As we are starting our track number from 1.

  • After that, select cell C11 and write down the following formula in it.

=D8

home loan emi calculator reducing balance excel

As a result, we should have something like this for the blank values.

For formulas to be used in the later cells, we need some cell references that we will create in the later steps. So, let’s leave it here for now.


Step 3: Calculate Starting Balance for Each Payment

Now we should make a column for starting balance at each payment. Similar to the previous step, we can calculate the first one for now.

  • First, make a column in the second dataset and name it “Starting Balance”.

  • Then select cell D11 and write down the following formula.

=D5

home loan emi calculator reducing balance excel

As a result, we will have the starting balance of the first payment.

home loan emi calculator reducing balance excel

The amount is 0 for now because of the blank input cells in the upper chart.


Step 4: Evaluate Principal Amount for Each Payment

Now let’s make a column and necessary calculations for the principal amount to be paid in each transaction. We need the help of the IFERROR, IF, and PPMT functions to calculate this value in the calculator.

  • For that, let’s create a column first for it.

  • Then select cell E11 and write down the following formula.

=IFERROR(IF(B11="","",-PPMT($D$6/12,B11,$D$7,$D$5)),"")

home loan emi calculator reducing balance excel

  • After that, press Enter on your keyboard and you will have the principal amount of the first payment.

home loan emi calculator reducing balance excel

The output here is blank because the cell references of the input values are blank in it. For more details, see the breakdown of the formula.

🔍 Breakdown of the formula

IFERROR(IF(B11=””,””,-PPMT($D$6/12,B11,$D$7,$D$5)),””)

👉 -PPMT($D$6/12,B11,$D$7,$D$5) is the main portion of the formula used to find the principal amount of the payment. It takes the one-twelfth value of cell D6 as the interest rate (as each transaction is done monthly, 1/12 of the annual interest is taken as the rate), the value of cell B11 as the number of the current period, the value of cell D7 as the total number of periods, and the value of cell D5 as the principal value. This function returns the value as negative, so we have put a negative sign before that for a positive value that helps our calculations here.

👉 IF(B11=””,””,-PPMT($D$6/12,B11,$D$7,$D$5) portion of the formula checks whether the value of cell B11 is blank or not. If it is blank, the function returns a blank value, else it returns a principal value discussed in the previous point.

👉 Finally, IFERROR(IF(B11=””,””,-PPMT($D$6/12,B11,$D$7,$D$5)),””) prevents an error from showing if there is an error during the execution of the formula. Usually, the error occurs while executing the PPMT function, if there are problems with the input values. Our dataset at this point, too, would have resulted in an error. We have prevented the chart from getting filled up with errors with this portion of the formula.


Step 5: Compute Interest for Each Payment

At this instant, let’s make a column that can calculate interest in each payment. We can compute the interest for each payment with the help of the IPMT function. We also need the use of the IFERROR and IF functions to make our calculator end where we want and the reducing balance chart look good. Follow these steps.

  • First, make a column to count interest for each payment.

  • Then select cell C11 and write down the following formula.

=IFERROR(IF(B11="","",-IPMT($D$6/12,B11,$D$7,$D$5)),"")

home loan emi calculator reducing balance excel

  • After that press Enter. The cell will look blank for now.

home loan emi calculator reducing balance excel

🔍 Breakdown of the Formula

IFERROR(IF(B11=””,””,-IPMT($D$6/12,B11,$D$7,$D$5)),””)

👉 The IPMT($D$6/12,B11,$D$7,$D$5) function takes one-twelfth of the value in cell D6 as the rate argument (because cell D6 gives us a yearly rate, we need a monthly rate value), the value of cell B11 as the current number of the period (which is 1 here). It also takes cell D7 as the total number of periods there is for the loan to be paid and cell D5 as the principal value of the function. This function returns Interest payments for each given period.

👉 The previous function usually returns the value as negative, because it indicates a cash outflow. But for our calculations and to make the reducing balance chart more presentable we have added a minus sign(-) before it. So that it returns a positive result in the end.

👉 IF(B11=””,””,-IPMT($D$6/12,B11,$D$7,$D$5)) portion of the formula checks for whether the value of cell B11 is blank. If it is blank, then the cell contains blank. Otherwise, the formula goes on to calculate interest.

👉 Finally, the IFERROR(IF(B11=””,””,-IPMT($D$6/12,B11,$D$7,$D$5)),””) formula checks whether there was an error from any part of the formula. If there is any error, it goes to return a blank value. This occurs in our demonstration for now because there are no values for the IPMT function. Instead of showing errors in the cell, it is showing a blank value in it.

Read More: Excel Simple Interest Loan Calculator with Payment Schedule


Step 6: Calculate Ending Balance After Each Payment

Now we need a column that can calculate the ending balance after one has paid the EMI in the respective month. We only need the IFERROR and IF functions for this. Follow these steps to see how we can create one.

  • First, create a new column beside our reducing balance chart.

  • Then select cell G11 and write down the following formula.

=IFERROR(IF(B11="","",D11-E11),"")

home loan emi calculator reducing balance excel

  • Finally, press Enter and you will have the ending balance after the first payment.

home loan emi calculator reducing balance excel

The value is blank for now as the cell references used in the formula are either zero or blank.

🔍 Breakdown of the Formula

=IFERROR(IF(B11=””,””,D11-E11),””)

👉 The IF(B11=””,””,D11-E11) function in the formula checks for whether the value of cell B11 is blank or not. If the cell is blank, the function returns blank. Otherwise, it returns the difference between values of cells D11 and E11 as the output.

👉 Finally, IFERROR(IF(B11=””,””,D11-E11),””) checks if there was an error in the previous function. It returns a blank string if there was an error in it.


Step 7: Compute Total Amounts Paid

In like manner, we also need to see how much of an amount one is paying in each EMI in this reducing balance chart. For this column too, we need the IFERROR and IF functions for the calculations. See the steps for a detailed guide.

  • First, create a column where we can compute and record the total amounts of each payment.

  • Now select cell H11 and write down the following formula.

=IFERROR(IF(B11="","",E11+F11),"")

home loan emi calculator reducing balance excel

  • After pressing Enter you will have the total payment of the first transaction. For now, the cell is empty because the cell references that the formula used is empty.

home loan emi calculator reducing balance excel

🔍 Breakdown of the Formula

IFERROR(IF(B11=””,””,E11+F11),””)

👉 Firstly, IF(B11=””,””,E11+F11) checks for whether the cell value of B11 is blank or not. If it is blank, then the function returns blank. This helps the numbers to stop repeating when the chart ends. If the cell value of B11 is not empty, it returns the sum of cells E11 and F11 as the output.

👉 Finally, IFERROR(IF(B11=””,””,E11+F11),””) checks for whether there was an error in the inner function. If there were indeed any errors, it returns an empty string instead of showing the error. We use this as an added safety so that our reducing balance chart looks clean in case of missing inputs.


Step 8: Replicate Formulas for Later Rows

Up until the previous step, we have only created formulas in the first row. It will return the first transactions. Now we will have to replicate these formulas for later rows and we have to replicate them in such ways that the chart stops when the loan is fully paid. Besides using the IFERROR and IF functions, we are gonna need the EDATE function in this step.

Follow these steps for a thorough process.

  • First of all, select cell B12 and write down the following formula.

=IFERROR(IF(G11>1,B11+1,""),"")

home loan emi calculator reducing balance excel

🔍 Breakdown of the Formula

IFERROR(IF(G11>1,B11+1,””),””)

👉 IF(G11>1,B11+1,””) portion of the formula checks whether the value of cell G11 is greater than one or not. If it is true, then the function returns a value one greater than that of cell B11. Else, it returns an empty string.

👉 IFERROR(IF(G11>1,B11+1,””),””) checks for an error in the previous function. If there are any errors there, it returns an empty string. Otherwise, it proceeds to print the values as it is. We use it as a safety feature so that no error shows in the case of blank values in the EMI calculator.

  • After that press Enter.

  • Now select the cell again and click and drag for a large number of rows so that it can store data of a long period.
  • Similarly, select cell C12 and write down the following formula.

=IF(B12="","",EDATE(C11,1))

home loan emi calculator reducing balance excel

🔍 Breakdown of the Formula

IF(B12=””,””,EDATE(C11,1))

👉 EDATE(C11,1) takes the value of cell C11 as the initial month argument and returns the value of the exact date of the next month.

👉 Finally, IF(B12=””,””,EDATE(C11,1)) checks if there are any missing value in the previous cell (cell B12). If the value is empty, it returns an empty string. Otherwise, it executes the previous function.

  • Then press Enter.

home loan emi calculator reducing balance excel

  • Now, select the cell again and double-click the fill handle icon to automatically fill up the values for the rest of the cells.
  • Next, for the “Starting Balance”, select cell D12 and write down the following formula.

=IF(B12="","",G11)

home loan emi calculator reducing balance excel

  • Now press Enter.

  • The cell is still blank because the value of cell G11 is blank at this point. Nevertheless, select the cell again and double-click the fill handle icon to replicate the formula for the rest of the cells in this column.
  • To replicate formulas for the “Principal Amount”, “Interest”, “Ending Balance”, and “Total Payment” columns, select the range E11:H11 and double-click the fill handle icon. You will end up with a chart looking like this.

Our reducing balance chart for a home loan EMI calculator in Excel is now complete.


Step 9: Generate Loan Summary

Now let’s add a portion that can overview the loan summary.

  • First, make a chart with the parameters like this.

  • Now select cell H5 and write down the following formula.

=IFERROR(-PMT(D6/12,D7,D5),"")

home loan emi calculator reducing balance excel

🔍 Breakdown of the Formula

IFERROR(-PMT(D6/12,D7,D5),””)

👉 PMT(D6/12,D7,D5) returns the payment loan for each transaction taking 1/12th of cell D6 as the rate, cell D7 as the total number of periods available to pay, and cell D5 as the principal value.

👉 The previous function returns a negative value as it indicates a cash outflow. So we put a negative sign (-) before it to get a positive value to show on the summary.

👉 IFERROR(-PMT(D6/12,D7,D5),””) checks whether there are any errors within the function. If there are any, it returns an empty string instead of showing an error warning. This helps our calculator look clean.

  • After pressing Enter you will have the amount of monthly payment for each EMI.

  • Now select cell H6 and write down the following formula.

=D7

home loan emi calculator reducing balance excel

  • Then press Enter.

  • After that, select cell H7 and write down the following formula.

=SUM(H11:H500)

home loan emi calculator reducing balance excel

  • Then press Enter and you will have something like this.

home loan emi calculator reducing balance excel

  • Next, select cell H8 and write down the following formula.

=SUM(G11:G500)

home loan emi calculator reducing balance excel

  • Finally, press Enter. You will end up something like this.

As a result of all these steps, the home loan EMI calculator with the reducing balance chart in Excel is now complete.


Step 10: Verify Home Loan EMI Calculator with Data

If you have completed all of the steps and sub-steps until now, the home loan EMI calculator with reducing balance is complete now in Excel. Given this point, let’s test our home loan EMI calculator with suitable values and see if it works or not.

In the input section, let’s add values of the loan amount of $4,00,000, the annual interest rate of 5%, and a total of 5 years loan, so a tenure of 60 months, and let’s say the customer took the loan at 20th June of 2022. Let’s put these values in the input sections under “Loan Values” and the sheet looks something like this now.

home loan emi calculator reducing balance excel

We can see that the payment and payment date for each transaction are divided by the reducing balance in the Excel spreadsheet. The value of monthly payment in the “Loan Summary” section and from the reducing balance chart also matches. Keep in mind that, these two values were calculated differently. So we can say that our home loan EMI calculator with reducing balance created in Excel is working fine.

If we go down the reducting balance chart, we can also find that the payment ended at the 60th transaction too.

home loan emi calculator reducing balance excel

So we can say that our calculator gives us the exact amount of transactions too.

Read More: SBI Home Loan EMI Calculator in Excel Sheet with Prepayment Option


Conclusion

As our home loan EMI calculator with reducting balance in Excel is working completely fine, this concludes the article. Hopefully, you can now create your own EMI calculators even for other types than a home loan with the reducing balance chart in Excel with the help of these formulas. And if it still feels too much, you can always download the workbook and use it as a template. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.

For more guides like this, visit Exceldemy.com.


Related Articles

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo