Foyez Alam
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.
Download Practice Book
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 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.
- 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)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)
- After that, click on ENTER button and there appears the 1st year-end interest at cell G4.
- 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
- 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)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
- How to Calculate Interest Rate on a Loan in Excel (2 Criteria)
- Calculate Interest Rate in Excel (3 Ways)
- How to Calculate Gold Loan Interest in Excel (2 Ways)
- Car Loan Calculator in Excel Sheet – Download Free Template
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.
- Then you need to type the following formula.
=C7*C5-C4Here, 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.
- 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.
