This article shows how to calculate interest in excel with payments using 3 practical examples. It also shows how to calculate mortgage payments, the time period needed to pay off loans, and other practical interest-related problems. The following picture gives an idea about the purpose of the article.

## Download Practice Workbook

You can download the practice workbook from the download button below. It will be helpful to understand the examples more easily.

## 3 Examples to Calculate Interest in Excel with Payments

We can calculate interest in excel with payments using the **IPMT function**. I’m going to explain how to use this function in this case with 3 practical examples.

**Syntax of the IPMT Function:**

The **IPMT function** consists of the following arguments.

**IPMT(rate,per,nper,pv,[fv],[type]**

- rate – interest rate per period
- per – period number for which interest amount will be calculated
- nper – total number of periods
- pv – present value/principal amount/total loan
- [fv] – balance at the end of the last payment. Defaults to 0.
- [type] – 1 if payment is due at the beginning of the period. 0 if payment is due at the end of the period. Defaults to 0.

### 1. Calculate Monthly Interest Payments on a Credit Card Debt

Assume, you have a credit card installment agreement. You have to pay $4,500 in 12 months at a 15% annual interest rate. Now, you want to **calculate the amount of interest** you need to pay each month.

- Then, enter the following formula in cell
**C5**.

`=IPMT($F$4/$F$6,B5,$F$7,$F$8,$F$9,$F$10)`

- After that, apply the formula to the cells below using the
**fill handle**tool.

**Read more:** **How to Calculate Credit Card Interest in Excel**

### 2. Calculate Quarterly Interest Payments for a Car Loan

Suppose you want to get a car for **$20,000**. But you want to pay it in **3** years in quarterly installments at a **9%** annual interest rate. Now, you want to **calculate how much interest you need to pay** each period on the loan.

- Then, enter the following formula in cell
**C5**:

`=IPMT($F$4/$F$6,B5,$F$7,$F$8,$F$9,$F$10)`

**Read more:** **Car Loan Calculator in Excel Sheet – Download Free Template**

### 3. Calculate Semi-Annual Interest Payments on a Personal Loan

Now, consider getting a personal loan of **$10,000** from the bank at a **5.5%** annual interest rate. You want to pay the loan semi-annually in six years.

- Now, to get an idea of how much you need to pay as interest each period, enter the following formula in cell
**C5**.

`=IPMT($F$4/$F$6,B5,$F$7,$F$8,$F$9,$F$10)`

## How to Calculate Total Interest Payment on Loans in Excel

You can easily get the total interest using the **SUM function**.

- Just enter the following formula in cell
**C17**:

`=SUM(C5:C16)`

- Alternatively, you can also apply the following formula in cell
**C17**to get the same result as follows.

`=CUMIPMT($F$4/$F$6,$F$7,$F$8,$B$5,$B$16,$F$10)`

- The above formula consists of the
**CUMIPMT function**with the following arguments:**CUMIPMT(rate,nper,pv,start_period,end_period,type)**

**Read more:** **How to Calculate Accrued Interest on a Loan in Excel**

**Similar Readings**

**How to Calculate Interest Rate in Excel (3 Ways)****Calculate Gold Loan Interest in Excel (2 Ways)****Create Late Payment Interest Calculator in Excel and Download for Free****How to Calculate Daily Interest in Excel (2 Easy Ways)**

## More Examples to Calculate Payments & Savings in Excel

Here, I will illustrate 4 interests, payments & savings-related practical examples using the **FV, PV, NPER**, and **PMT **functions in excel.

### 1. Calculate Accumulated Savings After a Certain Period

Assume, you have **$1,000** in your bank account. You want to deposit **$150** each month for **1.5** years. The bank offers **5%** annual interest on the deposit amount.

- Now, to see how much your saving will be at the end of 1.5 years, enter the following formula in cell
**C12**:

`=FV(C4/C6,C7,C8,C9,C10)`

- The above formula consists of the
**FV function**with the following arguments.

** FV(rate,nper,pmt,[pv],[type])**

- The
**pmt**argument stands for payment at the end of each period.

### 2. Calculate Down Payment Amount for a Car Loan

Assume, you want to buy a $30,000 car at a 2.5% interest rate over 4 years. You cannot pay any more than $500 each month.

- Now to calculate the down payment you need to make at the beginning of the purchase, enter the following formula in cell
**C13**.

`=C4-PV(C5/C7,C8,C9,C10,C11)`

- The above formula consists of the
**PV function**with the following arguments.

** PV(rate,nper,pmt,[fv],[type])**

### 3. Calculate Payoff Period for a Bank Loan

Imagine, you want to get a bank loan of $3,000 at a 2.5% annual interest rate. You can pay $200 a month to pay off the loan.

- Now, to find out how many months it will take you to pay the loan off, enter the following formula in cell
**C11**:

`=NPER(C4/C5,C6,C7,C8,C9)`

- The above formula consists of the
**NPER function**with the following arguments.

** NPER(rate,pmt,pv,[fv],[type])**

**Read more:** **Bank Interest Calculator in Excel Sheet – Download Free Template**

### 4. Calculate Mortgage Payments in Excel

Imagine, you want to buy a **$200,000** home at a **4.9%** interest rate with a **25**-year mortgage.

- Then, apply the following formula in cell
**C12**:

`=PMT(C4/C6,C7,C8,C9,C10)`

- The above formula consists of the
**PMT function**with the following arguments.

** PMT(rate,nper,pv,[fv],[type])**

## Things to Remember

- Entering a non-numeric value for any arguments in the IPMT function will result in
**#Value!** - The
**per**argument in the**IPMT function**must be within 1 to**nper**. Otherwise, a**#NUM!**will occur. - Usually, the arguments in third brackets
**[ ]**are optional arguments. - Red-colored currencies indicate negative values as they are due payments.

## Conclusion

Now, you can easily calculate payments, interests, savings, payment periods, etc. in excel. I hope this article helps you understand how to calculate interest in excel with payments. For further queries or suggestions, please use the comment section below.