Excel > Functions

# 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

• 0 - Payments are made at the end of the period
• 1 - Payments are made at the beginning of the period

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