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.

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.

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.

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.

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

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.

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

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.

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

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.

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

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.

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

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

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

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

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

• Then press Enter.

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

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

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

• 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