The **IPMT function **in Excel is used to find the interest payment of a loan amount for a certain period.

## IPMT Function: Syntax and Arguments

**⦿**** Function Objective**

The **IPMT function **returns the interest payment for a given period for an investment, based on periodic, constant payments and a constant interest rate.

**⦿**** Syntax**

`IPMT(rate, per, nper, pv, [fv], [type])`

**⦿**** Arguments**

Argument |
Required/Optional |
Explanation |
---|---|---|

rate |
Required | The constant interest rate for a period on the loan/investment |

per |
Required | The period for which you want to calculate the interest payment |

nper |
Required | The total number of payments during the whole period of the loan/investment |

pv |
Required | The present value of the loan/investment |

[fv] |
Optional | The future value of the loan/investment; if it is not used then zero will be taken automatically for this argument |

[type] |
Optional | It defines the due time of the payment.
**0**is for the payments at the end of each period.**1**is for the payments at the beginning of each period.
When it is not used then zero will be taken automatically for this argument |

**⦿**** Return Value**

It returns a numeric value which is the interest payment for the loan for a certain period.

**⦿**** Version**

The **IPMT function **has been introduced in **Excel 2007 **version and available for all versions after that.

## How to Use the IPMT Function in Excel: 8 Examples

We’ll use the following table for demonstrating the applications of the **IPMT function **in Excel.

### Example 1 – Using the IPMT Function Without Optional Arguments for a Yearly Interest Payment

For the interest payment for the loans at the end of the first year, you can use the **IPMT function** without optional arguments.

- Select the output cell
**E5.** - Use the following formula:

`=IPMT(C5,1,D5,B5)`

Here, **C5 **is the annual rate of interest, **1 **is the period for which you want to calculate the interest payment ( as we want to know the interest payment for the first year so we have used **1**), **D5 **is the total number of payments during the whole period and **B5 **is the present value of the loan.

*As we have used no future value and type arguments so it will consider future value as zero and also type as zero which means payment will be made at the end of each period.*

- Press
**Enter.**

- Drag down the
**Fill Handle**tool.

** Result**:

You will get the values as negative value because of the cash flow sign convention. The format of the results will be

**Currency**and you will get the values in red and enclosed with parentheses (the

**Currency**format for negative numbers).

You can change the format into **General**.

You can get the same result by inserting direct input instead of reference like below.

`=IPMT(0.05,1,D7,1000)`

### Example 2 – Using the IPMT Function with Optional Arguments

We will use the optional arguments of the **IPMT function **for calculating the yearly interest payments.

- Select the output cell
**F5.**

- Insert the following formula:

`=IPMT(D5,2,E5,B5,C5,0)`

**D5 **is the annual rate of interest, **2 **is the period for which you want to calculate the interest payment (as we want to know the interest payment for the second year so we have used **2**), **E5 **is the total number of payments during the whole period and **B5 **is the present value of the loan. The future value is **C5, **and **0 **is for the payment at the end of the period.

- Press
**Enter.**

- Drag down the
**Fill Handle**tool.

** Result**:

You will get the interest payments for the end of the second year.

You can get the interest payments at the beginning of the second year also by using the following formula

`=IPMT(D5,2,E5,B5,C5,1)`

### Example 3 – Using the IPMT Function for Semi-Annual Interest Payments

We will determine the interest payment at the end of the second half of the year by using the **IPMT function**.

- Select the output cell
**F5.**

- Insert the following formula:

`=IPMT(D5/2,2,E5*2,B5)`

**D5/2→ D5**is the annual rate of interest and it divided by**2**for semi-annual payment

**Output→**0.025

**2 →**The period for which you want to calculate the interest payment (as we want to know the interest payment for the second half of the first year so we have used**2**),**E5*2→**It is the total number of payments during the whole period (here, the total period is**E5**or**7**and for semi-annual payment it is multiplied by**2**)

**Output→**14

**B5**is the present value of the loan.

- Press
**Enter.**

- Drag down the
**Fill Handle**tool.

** Result**:

You will get the interest payments for the end of the second half of the first year.

### Example 4 – Using the IPMT Function for Quarterly Interest Payments

We will get the interest payment at the end of the 3rd quarter of the first year by using the **IPMT function**.

- Select the output cell
**E5.**

- Use the following formula:

`=IPMT(C5/4,3,D5*4,B5)`

**C5/4→ C5**is the annual rate of interest and it divided by**4**for quarterly payment

**Output→**0.0125

**3 →**The period for which you want to calculate the interest payment (as we want to know the interest payment for the third quarter of the first year so we have used**3**),**D5*4→**It is the total number of payments during the whole period (here, the total period is**D5**or**7**and for quarterly payment it is multiplied by**4**)

**Output→**28

**B5**is the present value of the loan.

- Press
**Enter.**

- Drag down the
**Fill Handle**tool.

** Result**:

You will get the interest payments for the end of the third quarter of the first year.

### Example 5 – Using the IPMT Function for Monthly Interest Payments

You can get the interest payment at the beginning of the sixth month of the first year by using the **IPMT function**.

- Select the output cell
**F5.**

- Use the following formula:

`=IPMT(D5/12,6,E5*12,B5,C5,1)`

**D5/12→ D5**is the annual rate of interest and it is divided by**12**(as a year has**12**months) for monthly payment

**Output→**0.004167

**6 →**The period for which you want to calculate the interest payment (as we want to know the interest payment for the sixth month of the first year so we have used**6**),**E5*12→**It is the total number of payments during the whole period (here, the total period is**E5**or**7**and for monthly payment it is multiplied by**12**)

**Output→**84

**B5→**Present value of the loan**C5→**Future value of the loan**1→**Payment is made at the beginning of each month

- Press
**Enter.**

- Drag down the
**Fill Handle**tool.

** Result**:

You will get the interest payments for the beginning of the sixth month of the first year.

### Example 6 – Using the IPMT Function for Weekly Interest Payments

We will determine the interest payment at the beginning of the 14th week of the first year by using the **IPMT function**.

- Select the output cell
**F5.**

- Use the following formula:

`=IPMT(D5/52,14,E5*52,B5,C5,1)`

**D5/52→ D5**is the annual rate of interest and it is divided by**52**(as a year has nearly**52**weeks) for weekly payment

**Output→**0.000962

**14 →**The period for which you want to calculate the interest payment (as we want to know the interest payment for the 14th week of the first year so we have used**14**),**E5*52→**It is the total number of payments during the whole period (here, the total period is**E5**or**7**and for weekly payment it is multiplied by**52**)

**Output→**364

**B5→**Present value of the loan**C5→**Future value of the loan**1→**Payment is made at the beginning of each week

- Press
**Enter.**

- Drag down the
**Fill Handle**tool.

** Result**:

You will get the interest payments for the beginning of the 14th week of the first year.

### Example 7 – Interest Payments Using the Table Option

We’ll get the interest payment for the loans at the end of the first year.

- Go to
**Insert**tab and select**Table.**

The **Create Table **Dialog Box will open up.

- Select the data range.
- Check
**My table has headers.** - Press
**OK.**

You will get the following table.

- Select the output cell
**E5.**

- Use the following formula:

`=IPMT([@[Rate of Interest (Annual)]],1,[@[Total Payment (Annual)]],[@[Loan (PV)]])`

Here, **[Rate of Interest (Annual)] **is the annual rate of interest, **1 **is the period for which you want to calculate the interest payment (as we want to know the interest payment for the first year so we have used **1**), **[Total Payment (Annual)] **is the total number of payments during the whole period and **[Loan (PV)] **is the present value of the loan.

- Press
**Enter.**

** Result**:

You will get the interest payments at the end of the first year.

### Example 8 – Using VBA Code

You can use the **IPMT function **in the **VBA **code.** **

- Go to the
**Developer**tab and select**Visual Basic.**

The **Visual Basic Editor **will open up.

- Go to
**Insert**and select**Module.**

A **Module** will be created.

- Insert the following code:

```
Sub interestpayment()
Range("F5") = Application.WorksheetFunction.IPmt((Range("D5")), 1, _
(Range("E5")), (Range("B5")), (Range("C5")), 0)
Range("F6") = Application.WorksheetFunction.IPmt((Range("D6")), 1, _
(Range("E6")), (Range("B6")), (Range("C6")), 0)
Range("F7") = Application.WorksheetFunction.IPmt((Range("D7")), 1, _
(Range("E7")), (Range("B7")), (Range("C7")), 0)
Range("F8") = Application.WorksheetFunction.IPmt((Range("D8")), 1, _
(Range("E8")), (Range("B8")), (Range("C8")), 0)
Range("F9") = Application.WorksheetFunction.IPmt((Range("D9")), 1, _
(Range("E9")), (Range("B9")), (Range("C9")), 0)
Range("F10") = Application.WorksheetFunction.IPmt((Range("D10")), 1, _
(Range("E10")), (Range("B10")), (Range("C10")), 0)
End Sub
```

It will return the interest payments in cells **F5 **to **F10 **of Column **F**.

- Press
**F5.**

** Result**:

You will get the interest payments at the end of the first year.

## Things to Notice

- You have to be careful about specifying the arguments
**rate**and**nper**for calculating interest payments for different types of periods like yearly, quarterly, monthly, etc. - For any non-numeric arguments, you will get a
**#VALUE!**Error. - The results will be negative for loans (cash paid out) and positive for investments (cash received)
- When
**per**is negative or greater than**nper,**you will get a**#NUM!**Error.

## Practice Section

We have provided a** Practice** section like below in a sheet named **Practice.**

**Download the Practice Workbook**

**<< Go Back to Excel Functions | Learn Excel**