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

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

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

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

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

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

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