IPMT Function
Finds the interest portion of a loan payment in a given period.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| rate | Interest rate per period. | Required |
| per | The period for which to calculate interest (1 to nper). | Required |
| nper | Total number of payment periods. | Required |
| pv | Present value or loan principal. | Required |
| fv | Future value or desired cash balance after the last payment (default 0). | Optional |
| type | 0 = end of period (default), 1 = beginning of period. | Optional |
Basic Example
Interest portion of the first payment on a loan
=IPMT(0.05/12, 1, 360, 300000)For a $300,000 loan at 5% annual interest over 30 years, the first month's interest is $1,250.
Advanced Examples
Example 1: Interest in a later period
Amortization scheduleInterest portion of the 60th payment
=IPMT(0.05/12, 60, 360, 300000)How IPMT Works
IPMT calculates the interest due in a specific period based on the outstanding principal at the start of that period. The result is negative because it represents a cash outflow.
Important Notes & Limitations
Returns negative values by default because payments are outflows.
Requires consistent period units for rate, nper, and per.
Does not include fees or taxes.
Common Errors & Fixes
#NUM! errorper is less than 1 or greater than nper.Fix: Ensure per is between 1 and nper inclusive.
Wrong signThe result is negative when expecting positive.Fix: Multiply by -1 or use ABS if you need a positive value.
Download Practice File
IPMT
ipmtPractice IPMT with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the IPMT function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps