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

## 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)`
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)`

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

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

• Finally, click ENTERÂ 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.

## 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

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

Advanced Excel Exercises with Solutions PDF