Financial
IPMT Function in Excel
Returns the interest payment for a given period of an investment.
Syntax
- =IPMT(rate, per, nper, pv, [fv], [type])
Arguments
- rate (required): Interest rate per period
- per (required): Period for which to find interest
- nper (required): Total number of periods
- pv (required): Present value
Examples
- =IPMT(5%/12, 1, 60, 25000) - First month interest - Result: -$104.17
Excel IPMT function documentation
- IPMT returns the interest portion of a loan payment for a specific period in an amortization schedule.
- Syntax: =IPMT(rate, per, nper, pv, [fv], [type]). Keep sign conventions aligned with [PMT](/functions/pmt/) and [PV](/functions/pv/).
- Use IPMT to separate interest expense from principal repayment for monthly budgeting and accounting analysis.
- Related valuation context: [NPV](/functions/npv/), [IRR](/functions/irr/), and the [Financial functions hub](/categories/financial/).
IPMT arguments and cash-flow sign rules
- rate: periodic interest rate (for monthly schedules use annual_rate/12).
- per: payment period to inspect, from 1 through nper.
- nper and pv must match the same periodic unit used by rate.
- If your loan amount is positive, IPMT often returns negative outflow values unless you flip signs.
Step-by-step: interest by month in a loan table
- Step 1 — Put annual rate in B1, years in B2, and loan principal in B3.
- Step 2 — Use monthly rate B1/12 and nper B2*12.
- Step 3 — In period row A2 use =IPMT($B$1/12, A2, $B$2*12, $B$3).
- Step 4 — Copy down to profile interest decline over time.
- Step 5 — Pair with [PMT](/functions/pmt/) and [PPMT](/functions/ppmt/) for full payment decomposition.
IPMT vs PPMT vs PMT
- IPMT returns only interest for one period.
- PPMT returns only principal for one period.
- PMT returns total payment each period.
- Validation rule: IPMT + PPMT should equal PMT in the same period.
Worked examples to copy
- Monthly interest period 1: =IPMT(6%/12,1,60,25000).
- Interest for month 24: =IPMT(6%/12,24,60,25000).
- Beginning-of-period payment model: =IPMT(rate,per,nper,pv,0,1).
- Audit check: =IPMT(rate,per,nper,pv)+PPMT(rate,per,nper,pv).
People also ask
- Why is IPMT negative? — Cash outflow sign convention; change pv sign if needed.
- Does IPMT work with annual periods? — Yes, if rate and nper are annualized consistently.
- Can IPMT model leases? — Yes, for fixed-rate payment structures.
- When to use XNPV instead? — Use [XNPV](/functions/xnpv/) for irregular dated cash flows.
Common errors
- per must be between 1 and nper
Use cases
- Loan amortization
- Interest breakdown
- Tax deductions
Frequently asked questions
- How do I calculate total interest paid over a loan? Use CUMIPMT: =CUMIPMT(rate, nper, pv, 1, nper, 0) gives total interest from period 1 to nper. Or sum IPMT for all periods: =SUMPRODUCT(IPMT(rate, ROW(INDIRECT("1:"&nper)), nper, pv)).
- Why does interest decrease over time? With each payment, you pay down principal, so less interest accrues. IPMT(rate, 1, ...) is highest, IPMT(rate, nper, ...) is lowest. This is how amortizing loans work - early payments are mostly interest, later payments are mostly principal.
- How do I verify IPMT + PPMT = PMT? For any period, IPMT + PPMT should equal PMT. Check: =IPMT(5%/12, 1, 60, 25000) + PPMT(5%/12, 1, 60, 25000) equals =PMT(5%/12, 60, 25000). This is useful for building amortization schedules.
Editorial review
- Reviewed by Excel.Directory Editorial Team. Updated May 2026.
When to use IPMT
- Loan amortization — common Financial scenario for IPMT.
- Interest breakdown — common Financial scenario for IPMT.
- Tax deductions — common Financial scenario for IPMT.
IPMT in the Financial category
- Browse all Financial functions at /categories/financial/ for related formulas.
- IPMT syntax: =IPMT(rate, per, nper, pv, [fv], [type])
- rate (required): Interest rate per period
- per (required): Period for which to find interest
- nper (required): Total number of periods
- pv (required): Present value
- Confirm IPMT arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
Formula checklist before you copy down
- Confirm IPMT arguments match the syntax shown above before filling down.
- Lock table and range references with $ when copying formulas across rows or sheets.
- If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
- Spot-check three known input rows manually against expected output.
Related Excel functions
- PPMT (/functions/ppmt/): Returns the principal payment for a given period of an investment.
- PMT (/functions/pmt/): Calculates the payment for a loan based on constant payments and a constant interest rate.
- CUMIPMT (/functions/cumipmt/): Returns the cumulative interest paid between two periods.
Errors to watch for
- per must be between 1 and nper — review causes on linked error pages in the directory.
Copy-paste audit workflow
- Enter IPMT on three test rows with known expected output documented on a QA tab.
- Fill down only after absolute references are locked on lookup tables and rate tables.
- Compare against manual calculation or a calculator for financial and statistical functions.
- Search this directory for comparison guides when choosing between similar functions in the same category.
IPMT worked examples to copy
- =IPMT(5%/12, 1, 60, 25000) — First month interest. Expected result: -$104.17.
IPMT reference summary for crawlers and offline review
- IPMT belongs to the Financial category in Excel. Returns the interest payment for a given period of an investment.
- Full syntax: =IPMT(rate, per, nper, pv, [fv], [type]). Open /functions/ipmt/ for parameters, FAQs, and related pages.
- Common mistakes: per must be between 1 and nper
- Pair this function with comparison guides when another Excel formula might fit the same task better.
- Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.