IPMT Function
The Excel IPMT function is used to calculate the interest paid during a period of a loan or investment.
It could be used by an investment company to find out the interest earned on a loan given to a customer. The IPMT function could be used to decide what interest rate to apply to the loan, as changing the rate will change the potential interest earned, ensuring the loan is worth giving.
The IPMT function can also be used to find out the amount of interest earned during a specific period during the length of a loan or investment, such as the 3rd year.
The syntax for the IPMT function is:
=IPMT(rate, per, nper, pv, [fv], [type])
Argument 
Purpose 

rate 
The interest rate per period 
per 
The period for which you want to find the interest amount. It must be entered in the range 1 to nper 
nper 
The number of payment periods during the lifetime of the loan. If payments are monthly for 5 years, this would be entered 5*12 to calculate 60 payment periods 
pv 
The present value, or current amount of the loan or investment. 
fv 
The future value, or amount after the last payment is made. FV is optional and if omitted the value is assumed to be 0 i.e. the future value of a loan will be 0 
type 
When the payments are due. It can be entered as 1 or 0 and is optional. If omitted the value is assumed to be 0

The examples below show the IPMT function being used to return the interest paid on a loan using different parameters.
Function 
Result 

=IPMT(C4/12,3,B4*12,A4) 
£912 Interest paid in the 3rd period of a 5 year loan 
=PPMT(C4,5,B4,A4,0,1) 
£2,406 Interest paid in the last year of a 5 year loan, with a future value of 0 and payments made at the beginning of each year 
Popular Excel Tips