Downloads
DAXFinancialIntermediate

IPMT

Returns the interest part of the payment for a given period of a loan with a fixed rate.

Syntax
IPMT(<rate>, <per>, <nper>, <pv> [, <fv>] [, <type>])

Returns: A number. It is negative when the loan amount is positive, because interest is a cost you pay.

What it does

IPMT gives the interest in one specific payment of a loan. Every payment has two parts: interest, which is the cost of borrowing, and principal, which reduces the balance. Early payments are mostly interest, and later payments are mostly principal. Finance teams use IPMT for interest expense by period, for the tax and accounting split of a loan payment, and for the interest accrual that the close must record.

Syntax and arguments

Example

The buyer from the PMT example borrows 300,000 over 360 monthly payments at 6% a year. The controller wants the interest in the first payment and in the twelfth payment.

Interest Payment 1 = IPMT(0.06 / 12, 1, 30 * 12, 300000)

Interest Payment 12 = IPMT(0.06 / 12, 12, 30 * 12, 300000)

The first payment has interest of 1,500.00, which is 300,000 times 0.5 percent. The result is -1,500.00, with the minus sign showing interest paid. The twelfth payment has interest of 1,483.16, a little less, because the balance has come down slightly.

Example: a second, different use

Interest expense for the whole first year of the loan. The measure adds the first twelve monthly interest amounts, with GENERATESERIES providing the payment numbers.

Interest Year 1 =
SUMX(
    GENERATESERIES(1, 12),
    IPMT(0.06 / 12, [Value], 30 * 12, 300000)
)

The result is -17,899.78, the total interest paid in the first twelve months. The minus sign is the Excel convention.

Things to watch

In business terms

IPMT supports interest expense schedules, the monthly accrual of interest on loans, the split of debt payments for the income statement and the cash flow statement, and the interest coverage ratio that lenders check. Accountants use it to match the bank's interest statement with the books.

Related functions

All DAX functions · View as Markdown · DAX fundamentals