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:
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. This can have the value 0 or 1, meaning: 0  The payment is made at the end of the period; 
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.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.
Formula:
 Result:

Note that in this example:
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.
Formula:
 Result:

Note that, in this example:
For further examples of the Excel Ipmt function, see the Microsoft Office website.
If you get an error from the Excel Ipmt function, this is likely to be one of the following:
#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 nonnumeric. 
Also, the following problem is encountered by some users:
The result from the Excel Ipmt function is much higher or much lower than expected.
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 quarters = 4 * years  monthly rate = annual rate / 12 quarterly rate = annual rate / 4 