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.
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.
- 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 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)
- After that, click on the 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
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.
- 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 subtract the loan amount to get the total home loan interest.
- 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
- How to Calculate Interest in Excel with Payments
- How to Calculate Principal and Interest on a Loan in Excel
- How to Calculate Credit Card Interest in Excel
- How to Calculate Accrued Interest on Fixed Deposit in Excel
- How to Calculate Accrued Interest on a Bond in Excel
- How to Calculate Accrued Interest on a Loan in Excel
<< Go Back to Calculate Interest In Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
