## What Is IPMT Function In Excel?

The

Interest Payment or the IPMT Excel Functioncalculates a loan’s periodic interest amount, i.e., the interest portion of the loan amount. The periodic loan payments and the interest are constant. TheIPMT Function in Excelis an inbuilt function, so that we can insert the formula from the “Function Library” or enter it directly in the worksheet.

For example, we apply the **IPMT Excel Function **to evaluate the present value of the loan amount of ‘$20,000’, and the period is “12 years” at ‘10%’ of the interest rate.

Select cell **B6**, enter the formula ** =IPMT(B4/12,1,B3*12,-B2), **and press the “

**Enter**” key to get the result of $166.67.

###### Key Takeaways

- The
**IPMT Excel Function**is a financial function that calculates the interest amount in a loan where there are constant periodic payments and interest rates. - Using this function, we can calculate the interest amount for any specific period, such as weekly, monthly, quarterly, semi-annually, or yearly/annually.
- The
argument of the*rate***IPMT function**divides the periodic interest rate by the number of periodic loan EMIs. - Cash paid out is shown as negative numbers, and cash received is shown as positive numbers.

### IPMT() Excel Formula

The syntax of the **IPMT Excel formula **is,

The arguments of the **IPMT Excel formula **are,

It is a mandatory argument. It is the interest amount per loan period.*rate:*: It is a mandatory argument. It is the period to find the interest amount. It must be in the range from 1 to*per*.*nper*: It is a mandatory argument. It is the total time period to pay the loan.*nper*It is a mandatory argument. It is the present amount value.*pv:*It is an optional argument. It is the future amount value to acquire after the last payment. If the*fv:*argument input is not entered, the formula considers it as 0, by default.*fv*: It is an optional argument. The*type*is*type***0**if the payment is done at the end of the period, and if payments are done or are due at the start of the period, then theis*type***1**. If omitted, the default value is 0.

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

**Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)**

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

### How to Use IPMT Excel Function?

We can use the **IPMT Excel Function **in 2 methods, namely,

**Access from the Excel ribbon.****Enter in the worksheet manually.**

#### Method #1 – Access from the Excel ribbon

First, choose an empty cell → select the **“Formulas” **tab → go to the “**Functions Library**” group → click the **“Financial” **option drop-down → select the **“IPMT”** function, as shown below.

The **“Function Arguments” **window appears. Enter the argument values in the **“rate”, “per”, “ nper”, “pv”, “fv”, **and the

**“**fields à click “

*type*”**OK**”, as shown below.

#### Method #2 – Enter in the worksheet manually

- Select an empty cell for the output.
- Type
**=IPMT(**in the selected cell. [Alternatively, type “**=I**” and double-click the**IPMT function**from the list of suggestions shown by Excel.] - Enter the arguments as values or cell references and close the brackets.
- Press the “
**Enter**” key.

Let us take a basic example to understand this function.

Raj Mohan has taken a loan of $5,00,000 for five years, and the interest rate is 4%. We will calculate his interest amount **using** **IPMT Excel Function**.

In the table, the data is,

- Column A contains a Description.
- Column B contains the Amount.

The steps to calculate the semi-annual interest amount **using** **IPMT Excel Function** are as follows:

**Select cell B7 and enter the formula =IPMT(B4/12, i.e., the rate of interest value.****Put a comma ‘,’, and enter the value of ‘per’ as “1”, i.e., the period value, the formula now is =IPMT(B4/12,1****Put a comma ‘,’, and enter the value of ‘nper’ as “B3****12”, i.e., the total period value, the formula now is =IPMT(B4/12,1,B3**12**Put a comma ‘,’, and enter the value of ‘pv’ as “-B2”, i.e., the present value, the formula now is =IPMT(B4/12,1,B3*12,-B2****Put a comma ‘,’, and enter the value of ‘fv’ as “B5”, i.e., the future value, the formula now is =IPMT(B4/12,1,B3*12,-B2,B5****Put a comma ‘,’, enter the value of ‘type’ as “0”, i.e., the payment type, and close the brackets. The complete formula is =IPMT(B4/12,1,B3*12,-B2,B5,0)****Press the “Enter” key.**

The interest amount is “**$1666.67**”, as shown in the above image.

### Examples

We will understand some advanced scenarios using the **IPMT Excel Function examples.**

#### Example #1

Ms. Kathrine has taken a loan of $5,00,000 for five years; the interest rate is 4%. We will calculate the semi-annual interest amount of her investment using the **IPMT Excel Function**.

In the table, the data is,

- Column A contains a Description.
- Column B contains Amount.

The procedure to calculate the interest amount for the** IPMT Function** is,

- Select cell
**B7**, enter the formulaand press the “*=IPMT(B4/2,1,B3*2,-B2,B5,0),***Enter**” key.

[** ‘ rate’ **as

**“B4/2”, ‘**as

*per’***“1”, ‘**as

*nper’***“B3*2” [2**as it is semi-annual

**], ‘**as

*pv’***“-B2”, ‘fv’**as

**“B5”,**and

**‘type’**as

**“0”**]

The interest amount is “**$8000.00**”, as shown in the above image.

#### Example #2

Mrs. Lorence has borrowed $80,000 for two years; the interest rate is 15%. We calculate the quarterly interest amount of his investment using the **IPMT Excel Function**.

In the table, the data is,

- Column A contains a Description.
- Column B contains the Amount.

The procedure to calculate the quarterly interest amount **using** **IPMT Excel Function** is,

- Select cell
**B7**, enter the formula, and press the “*=IPMT(B4/4,1,B3*4,-B2,B5,0)***Enter**” key.

[**‘ rate’ **as

**“B4/4”, ‘**as

*per*’**“1”, ‘**as

*nper*’**“B3*4” [4**as it is quarterly

**], ‘**as

*pv*’**“-B2”, ‘**as

*fv*’**“B5”,**and

**‘**as

*type*’**“0”**]

The interest amount is “**$3,000.00**”, as shown above.

#### Example #3

XYZ company has taken a loan of $25,00,000 for five years; the interest rate is 14%. We will calculate the weekly interest of the company’s investment using the **IPMT Excel Function**.

In the table, the data is,

- Column A contains a Description.
- Column B contains Amount.

The procedure to calculate the quarterly interest amount **using** **IPMT Excel Function** is,

- Select cell
**B7**, enter the formulaand press the “*=IPMT(B4/52,1,B3*52,-B2,B5,0),***Enter**” key.

__[__**‘ rate’ **as

**“B4/52”, ‘**as

*per’***“1”, ‘**as

*nper*’**“B3*52” [52**as it is weekly and 52 weeks in a year

**], ‘**as

*pv*’**“-B2”, ‘**as

*fv*’**“B5”,**and

**‘**as

*type*’**“0”**]

The weekly interest rate is “**$6,730.77**”, as shown above.

### Important Things to Note

- The
argument of the IPMT function multiplies the number of years by the number of payments per year.*nper* - The
**#NUM!**error occurs ifvalue is less than 0 or is less than the value of*per*.*nper* - The
**#VALUE!**error occurs when any non-numeric value is entered in the**IPMT formula.** - The units must specify the
and the*rate*arguments properly. If payment is made monthly, as on a 4-year loan at 24% annual interest, we need to use 24%/12 for*nper*and 4*24 for*rate*, and if the annual payments are made on the same loan, use 24% for*nper*and 4 for*rate*.*nper*

### Frequently Asked Questions (FAQs)

**1. What does the IPMT function mean in Excel?**

The IPMT function calculates the interest part based on a given loan amount and the payment time. The interest payment for the first, last, or any period in between is evaluated using the function.

The **IPMT Excel Function** is categorized under the Excel Financial functions of the Functional Library.

The syntax of the IPMT function is *=IPMT(rate,per,nper,pv,[fv],[type])*

**2. How does Excel IPMT function work in Excel?**

We can work with the **IPMT Excel function** as follows:

1. Select an empty cell for the output.

2. Type **=IPMT(** in the selected cell. [Alternatively, type “**=I**” and double-click the **IPMT function **from the list of suggestions shown by Excel.]

3. Enter the arguments as values or cell references and close the brackets.

4. Press the “**Enter**” key.

For example, we apply the **IPMT Excel function** to evaluate the present value of the loan amount of ‘$1,00,000’, and the period is “5 years” at ‘3%’ of the interest rate.

Select cell **B6**, enter the formula ** =IPMT(B5/12,1,B4*12,-B2), **and press the “

**Enter**” key.

The result is

**‘$246.13’**, as shown above.

**3. Where is the IPMT Excel Function?**

The **IPMT Excel Function **is in the “Formulas” tab and can be inserted as follows:

First, choose an empty cell → select the **“Formulas” **tab → go to the “**Functions Library**” group → click the **“Financial” **option drop-down → select the **“IPMT”** function, as shown below.

### Download Template

This article must help understand the **IPMT Excel Function’s** formula and examples. You can download the template here to use it instantly.

### Recommended Articles

This has been a guide to IPMT Excel Function. Here we use the formula to calculate the interest amount in a loan, examples and a downloadable excel template. You can learn more from the following articles –

## Leave a Reply