The Excel IPMT Function

Related Functions:
PMT Function
PPMT Function

Basic 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:

rate - The interest rate, per period
per - The period for which the interest payment is to be calculated (must be an integer between 1 and nper)
nper - The number of periods over which the loan or investment is to be paid
pv - The present value of the loan / investment
[fv] -

An optional argument that specifies the future value of the loan / investment, at the end of nper payments

If omitted, [fv] takes on the default value of 0
[type] -

An optional argument that defines whether the payment is made at the start or the end of the period

The [type] argument can have the value 0 or 1, meaning:
0   -   the payment is made at the end of the period
1   -   the payment is made at the beginning of the period
If the [type] argument is omitted, it takes on the default value of 0 (denoting payments made at the end of the period).

Cash Flow Convention :

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

Excel Ipmt Function Examples

In each of the examples below, the spreadsheet on the left shows the format of the Ipmt function, and the spreadsheet on the right shows the result.


Example 1

The following spreadsheet shows the Excel Ipmt function used to calculate 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.

 Formulas:
Example of use of the Excel Ipmt Function
 Results:
Excel Ipmt Function Result

Note that in this example :


Example 2

In the spreadsheet below, the Excel Ipmt function is used to calculate the interest payment during quarters 1 and 2 of a loan for $10,000, that is to be reduced to $5,000 over a period of 2 years, by a series of constant quarterly payments. Interest is charged at a rate of 3.5% per year and the payment is made at the beginning of each quarter.

 Formulas:
Example of use of the Excel Ipmt Function
 Results:
Excel Ipmt Function Result

Note that, in this example :


Further examples of the Excel Ipmt function can be found on 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
#NUM! - Occurs if the supplied per argument is < 0 or is > the supplied value of nper
#VALUE! - Occurs if any of the supplied arguments are not recognised as numeric values

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

months = 12 * years; monthly rate = annual rate / 12
quarters = 4 * years; quarterly rate = annual rate / 4
Return to the Excel Financial Functions Page

Return to the List of All Built-In Excel Functions

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2014 ExcelFunctions.net