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

Get FREE Advanced Excel Exercises with Solutions!

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 in 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 the total interest in Excel against our home loan.

How to Calculate Home Loan Interest in Excel


2 Easy Ways to Calculate Home Loan Interest in Excel

1. Calculate Home Loan Interest Using Generic CUMIPMT Function

You can easily calculate the total home loan interest 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 fixed interest rate of 10%. To do so, you need to apply CUMIPMT here like this:

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

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

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

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

end_period – It refers to 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 the 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


2. Calculate Home Loan Interest by Using known EMI value

You can calculate the total home loan interest in Excel even not have 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 subtract the loan amount to get the total home loan interest.

How to Calculate Home Loan Interest in Excel

  • Finally, click ENTER and get the total interest.

Read More: How to Calculate Gold Loan Interest in Excel


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.


Download Practice Workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Foyez Alam
Foyez Alam

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo