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.

## 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 the 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:** How to Calculate Interest on a Loan in Excel

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

**Read More:** How to Calculate Principal and Interest on a Loan in Excel

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

## 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 savings 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])**

### 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!**error. - The
**per**argument in the IPMT function must be within 1 to**nper**. Otherwise, a**#NUM!**error will occur. - Usually, the arguments in third brackets [ ] are optional arguments.
- Red-colored currencies indicate negative values as they are due payments.

**Download Practice Workbook**

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

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