Summary

The Excel IPMT function returns the interest portion of a payment for one specific period in an amortized loan model. It is the right tool when you want to separate financing cost from principal repayment period by period.

In most standard loans, total payment stays fixed, but the interest portion changes over time. Early periods usually have higher interest, while later periods have lower interest. IPMT helps you measure that change clearly.

This function is very useful for amortization audits, tax planning (where interest expense matters), and repayment strategy analysis.

Syntax

=IPMT(rate, per, nper, pv, [fv], [type])

rate is interest per period, per is target period index, nper is total periods, and pv is original principal. Optional fv and type refine ending balance and timing assumptions.

For monthly loans, use monthly units: rate = annual_rate/12 and nper = years*12. If timing is period-end (most common), keep type=0.

IPMT uses period numbering from 1 to nper in amortized workflows, so month 1 uses per=1 and final month uses per=nper.

Arguments

• rate - Interest rate per period in decimal format.
• per - Period number to inspect (typically 1 to nper).
• nper - Total number of payment periods.
• pv - Present value (loan amount).
• fv - [optional] Ending balance target (default 0).
• type - [optional] Payment timing (0 end, 1 beginning).

IPMT vs Other Functions

IPMT is part of a payment-decomposition family, but each function in that family serves a different decision purpose. Choosing the right one depends on what question you are actually trying to answer: interest cost, principal reduction, total installment, or cumulative totals.

A common modeling mistake is using one function for every loan question. For example, using IPMT when the real question is total payment, or using PMT when the real question is cumulative interest. The table below helps you match the function to the exact analysis goal.

Useful identity in amortized schedules: PMT = IPMT + PPMT (under the same sign convention). This identity is also a good audit check when you build payment tables.

Simple selection flow:

• If you need one-period interest, use IPMT.
• If you need one-period principal, use PPMT.
• If you need total payment, use PMT.
• If you need interest total across many periods, use CUMIPMT.

Using the IPMT Function

IPMT acts as an interest splitter. Instead of looking only at the total installment from PMT, IPMT shows exactly how much of one specific payment period is interest expense. This is very helpful when you need to explain where the money goes in an amortized schedule.

Keep in mind: IPMT returns the interest portion for one period only. If you need principal for that same period, pair it with PPMT. If you need total payment context, pair it with PMT. For multi-period interest totals (for example year 1 total interest), use CUMIPMT.

• Use IPMT when you need the interest amount for one selected period.
• Use IPMT + PPMT when you want a full breakdown of one installment (interest vs principal).
• Use IPMT + PMT when you need to show interest as a percentage of the total payment.
• Use CUMIPMT instead of IPMT when your question is about cumulative interest across a period range.

Example 1 - First-Month Interest

Get the interest portion from the first payment of a 30-year mortgage model. This period is usually the highest interest point because almost the full principal is still outstanding. It is a good baseline for understanding early repayment burden.

=IPMT(0.05/12,1,360,100000)

Example 2 - Final-Month Audit

Measure the interest portion in the final payment period. In a standard amortized schedule, this value should be very small compared to early periods. Comparing Example 1 and Example 2 helps you clearly see how interest cost declines over time.

=IPMT(0.05/12,360,360,100000)

Example 3 - Interest Threshold Check

Use boolean logic to flag whether the period interest exceeds your internal cost threshold. This pattern is useful for dashboards and control sheets because it turns raw interest values into clear TRUE/FALSE risk signals.

=IPMT(0.05/12,1,360,100000)<-400

Example 4 - Interest-to-Payment Ratio

Convert the interest amount into a ratio of total payment to measure payment cost intensity. A higher ratio means more of the installment is financing cost, while a lower ratio means more goes to principal. This metric helps communicate amortization quality to non-technical users.

=ABS(IPMT(0.05/12,1,360,100000))/536.82

Conclusion Recap

• Summary: IPMT isolates period-level interest in amortized schedules.
• Syntax: =IPMT(rate,per,nper,pv,[fv],[type]) with correct period indexing.
• Checklist: validate units, period bounds, timing, signs, and one benchmark case.
• Comparison: combine with PMT and PPMT for complete installment analysis.
• Best use: debt cost tracking, tax-focused analysis, and repayment strategy review.