Quick Navigation
IPMT Function
Summary
The IPMT function calculates the interest portion of a periodic payment for any specific period in a loan or investment with fixed payments and constant interest rate. Essential for loan amortization schedules and financial analysis.
Syntax
IPMT(rate, per, nper, pv, [fv], [type])Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| rate | Number |
Yes | Interest rate per period (e.g., annual rate/12 for monthly) |
| per | Number |
Yes | The specific period number (must be between 1 and nper) |
| nper | Number |
Yes | Total number of payment periods in the loan/investment |
| pv | Number |
Yes | Present value - initial loan amount or investment (negative for loans) |
| fv | Number |
No | Future value after last payment (usually 0) |
| type | Number |
No | 0=end of period (default), 1=beginning of period |
Using the IPMT Function
IPMT is crucial for building amortization schedules, understanding loan costs over time, and financial planning. Use it to isolate interest costs for budgeting or when payments are highest early in loan terms.
Common IPMT Examples
Monthly Loan Interest (First Payment)
=IPMT(10%/12, 1, 3*12, 8000)Interest portion of first monthly payment on $8,000 loan at 10% APR over 3 years. Returns -$66.67.
Annual Loan Interest (Last Year)
=IPMT(10%, 3, 3, 8000)Interest portion for final year of same loan with annual payments. Returns -$292.45.
Car Loan Period 12 Interest
=IPMT(B2/12, 12, 60, C2)Interest paid in 12th month of 5-year car loan where B2=annual rate, C2=loan amount.
Frequently Asked Questions
Common Errors and Solutions
#VALUE! error
Cause: Non-numeric values in arguments or per outside 1-nper range
Solution: Verify all inputs are numbers and per is valid period
#NUM! error
Cause: Rate and nper don't produce valid payments or inconsistent units
Solution: Ensure rate and nper use same time period (monthly rate with monthly nper)
Notes
- Cash outflows (loan payments) use negative pv values
- Always match rate period with nper (monthly rate needs monthly nper)
- Combine with PPMT for complete payment breakdown
- Use CUMIPMT to get total interest over multiple periods
Compatibility
Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365
Not available in:
Content last reviewed: December 11, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+