Its calculates the interest portion of a fixed periodic payment (annuity) for a loan or investment under the annuity repayment method.
Syntax
IPMT(Rate; Per; Nper; Pv; [Fv]; [Type])
Arguments
| Argument | Description |
| Rate (required) | Periodic interest rate (e.g., 5.5%/12 for monthly payments). |
| Per (required) | Specific period number (e.g., 18 for the 18th payment). |
| Nper (required) | Total number of payment periods (e.g., 30*12 for a 30-year loan). |
| Pv (required) | Present value (loan principal). |
| Fv (optional) | Future value (residual value after Nper). Default: 0. |
| Type (optional) | 0 = end of period (default), 1 = start of period. |
Key Features
- Annuity Repayment: Payments combine interest (decreasing over time) and principal (increasing over time).
- Formula:
Interest in Period=Remaining Principal×RateInterest in Period=Remaining Principal×Rate
- Sign Convention:
- Negative result: Cash outflow (e.g., loan interest paid).
- Positive result: Cash inflow (e.g., interest earned on investments).
Example
Loan Repayment Calculation
- Loan Amount (Pv): $176,121.76
- Annual Rate: 5.5% → Monthly Rate: 5.5%/12
- Term: 30 years → 360 months (Nper = 30*12)
- 18th Month Interest:
=IPMT(5.5%/12, 18, 360, 176121.76) → **-$791.64**
(Interest paid in the 18th month)
Practical Notes
- Rounding Errors:
- Banks round to 2 decimal places. Use ROUND(IPMT(…), 2) for accuracy.
- Full Payment Plan:
- Combine with PPMT() (principal portion) to verify total payment:
=IPMT(Rate, Per, Nper, Pv) + PPMT(Rate, Per, Nper, Pv) = PMT(Rate, Nper, Pv)
- Type Matters:
- For leases/advance payments, set Type=1.
Comparison to Related Functions
| Function | Purpose |
| PMT() | Total periodic payment (interest + principal). |
| PPMT() | Principal portion of payment. |
| CUMIPMT() | Cumulative interest over multiple periods. |