This is a correct example of the structure of an ipmt function. =ipmt(rate,per,nper,pv,[fv],[type])

Home » Excel-Built-In-Functions » Excel-Financial-Functions » Excel-Ipmt-Function

Related Functions:

PMT
PPMT

Function Description

The Excel IPMT function calculates the interest payment, during a specific period of a loan or investment that is paid in constant periodic payments, with a constant interest rate.

The syntax of the function is:

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

Where the arguments are as follows:

Cash Flow Sign Convention:

Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers. This is seen in the examples below.

Excel Ipmt Function Examples

Example 1

In the following spreadsheet, the Excel Ipmt function is used to calculate the interest payment during months 1 and 2 of a loan of $50,000 which is to be paid off in full after 5 years. Interest is charged at a rate of 5% per year and the payment to the loan is to be made at the end of each month.

Note that in this example:

• The payments are made monthly, so it has been necessary to convert the annual interest rate of 5% into the monthly rate (=5%/12), and the number of periods from years to months (=5*12).
• As the forecast value is zero, and the payment is to be made at the end of the month, the [fv] and [type] arguments can be omitted from the above functions.
• The returned interest payments are negative values, as these represent outgoing payments (for the individual taking out the loan).

Example 2

In the spreadsheet below, the Excel Ipmt function is used to calculate the interest payment during quarters 1 and 2 of an investment that is required to increase an investment from $0 to $5,000 over a period of 2 years. Interest is paid at a rate of 3.5% per year and the payment into the investment is to be made at the beginning of each quarter.

Note that, in this example:

• The payments are made quarterly, so the annual interest rate of 3.5% has been converted into a quarterly rate (3.5%/4), and the number of periods has been converted from years to quarters (=2*4).
• The [type] argument has been set to 1, to indicate that the payment is to be made at the start of each quarter.
• The returned interest payment for the first quarter is zero, as the first payment is made at the start of the quarter.

For further examples of the Excel Ipmt function, see the Microsoft Office website.

Ipmt Function Errors

If you get an error from the Excel Ipmt function, this is likely to be one of the following:

Common Errors

Also, the following problem is encountered by some users:

Common Problem

The result from the Excel Ipmt function is much higher or much lower than expected.

Possible Reason

Many users, when calculating monthly or quarterly payments, forget to convert the interest rate or the number of periods to months or quarters.

Solve this problem by ensuring that the rate and the nper arguments are expressed in the correct units. I.e.:

What is Nper in IPMT function?

What is Ipmt full form in Excel?

What is Nper in Excel fv function?

What is the Ipmt?