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.

## How to Make Loan EMI Calculator with Reducing Balance in Excel: Easy Steps

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 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.

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.

### 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.

- 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`

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`

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

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

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

### 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)),"")`

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

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 looks 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)),"")`

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

🔍 **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.

### 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),"")`

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

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.

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

### 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),"")`

- 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.

**🔍 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,""),"")`

🔍 **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))`

🔍 **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**.

- 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)`

- 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),"")`

🔍 **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`

- Then press
**Enter**.

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

`=SUM(H11:H500)`

- Then press
**Enter**and you will have something like this.

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

`=SUM(G11:G500)`

- 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.

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.

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

**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.

## Conclusion

As our home loan EMI calculator with reducing 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.

## Related Articles

- Personal Loan EMI Calculator Excel Format
- How to Make Two Wheeler Loan EMI Calculator in Excel
- Reducing Balance EMI Calculator in Excel Sheet
- EMI Calculator with Prepayment Option in Excel Sheet
- How to Create Reverse EMI Calculator in Excel

**<< Go Back to EMI Calculator | Finance Template | Excel Templates**