Excel IPMT function to calculate interest portion of a loan payment

Excel IPMT function - syntax and basic uses

IPMT is Excel's interest payment function. It returns the interest amount of a loan payment in a given period, assuming the interest rate and the total amount of a payment are constant in all periods.

To better remember the function's name, notice that "I" stands for "interest" and "PMT" for "payment".

The syntax of the IPMT function in Excel is as follows:

Where:

• Rate (required) - the constant interest rate per period. You can supply it as a percentage or decimal number.

  For example, if you make annual payments on a loan with an annual interest rate of 6 percent, use 6% or 0.06 for rate.

  If you make weekly, monthly, or quarterly payments, divide the annual rate by the number of payment periods per year, as shown in this example. Say, if you make quarterly payments on a loan with an annual interest rate of 6 percent, use 6%/4 for rate.

• Per (required) - the period for which you want to calculate the interest. It must be an integer in the range from 1 to nper.
• Nper (required) - the total number of payments during the lifetime of the loan.
• Pv (required) - the present value of the loan or investment. In other words, it is the loan principal, i.e. the amount you borrowed.
• Fv (optional) - the future value, i.e. the desired balance after the last payment is made. If omitted, it is implied to be zero (0).
• Type (optional) - specifies when the payments are due:
  • 0 or omitted - payments are made at the end of each period.
  • 1 - payments are made at the beginning of each period.

For example, if you received a loan of $20,000, which you must pay off in annual installments during the next 3 years with an annual interest rate of 6%, the interest portion of the 1^st year payment can be calculated with this formula:

=IPMT(6%, 1, 3, 20000)

Instead of supplying the numbers directly into a formula, you can input them in some predefined cells and refer to those cells like shown in the screenshot below.

In accordance with the cash flow sign convention, the result is returned as a negative number because you pay out this money. By default, it is highlighted in red and enclosed in parenthesis (Currency format for negative numbers) as shown in the left part of the screenshot below. On the right, you can see the result of the same formula in the General format.

If you'd rather get interest as a positive number, put a minus sign before either the entire IPMT function or the pv argument:

=-IPMT(6%, 1, 3, 20000)

or

=IPMT(6%, 1, 3, -20000)

Examples of using IPMT formula in Excel

Now that you know the basics, let's see how to use the IPMT function to find the amount of interest for different frequencies of payment, and how changing the loan conditions changes the potential interest.

Before we dive in, it should be noted that IPMT formulas are best to be used after the PMT function that calculates the total amount of a periodic payment (interest + principal).

Excel IPMT function not working

If your IPMT formula throws an error, it is most likely to be one of the following:

1. #NUM! error occurs is the per argument is out of the range 1 to nper.
2. #VALUE! error occurs if any of the arguments is non-numeric.

That's how you use the IPMT function in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our Excel IPMT function sample workbook. I thank you for reading and hope to see you on our blog next week!