How to Calculate Home Loan Interest in Excel (2 Easy Ways)

You may take a suitable home loan from a bank or any financial institution. This article will help you to calculate home loan Interest in Excel. Here, we take a dataset at column B containing, the total home loan amount, interest rate, Loan Amount, Period Term in Months, and Compounding Periods Per Month. Using all the information mentioned above, we need to calculate total interest in Excel against our home loan.

How to Calculate Home Loan Interest in Excel


Download Practice Book


1. Calculate Home Loan Interest Using Generic CUMIPMT Function

You can easily calculate the total home loan interest easily by using the CUMIPMT function in Excel. By using this function, you will be able to calculate every year-end interest against your home loan. Then by adding those individual interests, you can calculate the total home loan interest in this Excel sheet. For this, you need to follow several steps.

  • First of all, you have to create a new data table with two columns. Here, columns F and G represent the respective year and interest.

How to Calculate Home Loan Interest in Excel

  • Now to calculate the interest after 1st year, you have to select cell G4 Then you have to apply the following formula:
=CUMIPMT(rate,nper,pv,start,end,type)
Formula Explanation

In this example, we will calculate the total interest during each year in a 5-year loan against $40,000 with a fix interest rate of 10%. To do so, you need to apply CUMIPMT here like this:

rate – It refers the interest rate per period. We divide 10% by 12 because 10% represents annual interest.

nper – It refers the total number of payment periods for the loan, here it is 60.

pv – It refers the present value, or total value of all payments now, $40000.

start_period – It refers the starting period for the given year.

end_period – It refers the ending period for the given year.

So, for year 1, got the following formula:

=CUMIPMT(5%/12,60,30000,1,12,0)

How to Calculate Home Loan Interest in Excel

  • After that, click on ENTER button and there appears the 1st year-end interest at cell G4.

How to Calculate Home Loan Interest in Excel

  • Next, you need to apply the same formula in the range of G5:G8 to calculate the other year-end interests. Here, the used formula in the other four cells are:
=CUMIPMT(10%/12,60,40000,13,24,0) // year 2


=CUMIPMT(10%/12,60,40000,25,36,0) // year 3

=CUMIPMT(10%/12,60,40000,37,48,0) // year 4

=CUMIPMT(10%/12,60,40000,49,60,0) // year 5

How to Calculate Home Loan Interest in Excel

  • After calculating all the 5 years of individual interest, now you need to select cell C8 to calculate the total home loan interest by using the SUM Function. Therefore, put the SUM formula in cell C8.
=SUM(G4:G8)
Formula Explanation

Here, G4:G8 is the range where all the 5 years’ interests are stored and the SUM will be executed on this.

Immediately after clicking on the ENTER button, you will see that there appears the total interest in Excel.

Read more: How to Calculate Interest on a Loan in Excel


Similar Readings


2. Calculate Home Loan Interest by Using known EMI value

You can calculate the total home loan interest in Excel even not having the interest rate, only having the known Loan Amount, Period Term in Months, Compounding Periods Per Month, and the EMI Value.

For this, you need to follow the following methods.

  • First, select cell C8.

how to calculate home loan interest in excel

  • Then you need to type the following formula.
=C7*C5-C4
Formula Explanation

Here, C7*C5 represents the total amount that will be paid after 5 years including interest. That’s why you need to substruct the loan amount to get the total home loan interest.

How to Calculate Home Loan Interest in Excel

  • Finally, click ENTER value and get the total interest.


Practice Book

I’ve given a practice sheet in the workbook to practice these explained ways to calculate the home loan interest in Excel. You can download it from the link given above.


Conclusion

In this article, I have tried to explain 2 different methods to calculate the home loan interest in Excel. Last but not least, I will be extremely grateful if you comment down below any of your suggestions, ideas, or feedback.


Further Readings

Foyez

Foyez

Hello everyone, This is Foyez Alam. I have recently completed my MBA from Institute of Business Administration (IBA), University of Dhaka. Earlier I have completed my B.Sc in Leather Engineering from University of Dhaka. Now I am working as an Excel and VBA Content Developer in SOFTEKO. Through patience and hard work, I have a passion for learning new things and applying my knowledge to new situations. That's why I am here. Besides this, I love to interact with different people and I love to spend my spare time reading, watching movies, dancing, etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo