IPMT Function
Returns how much of a given loan payment is interest.
Category
Financial
Available in
All versions, Microsoft 365
Returns
The interest part of one payment
Overview
Every loan payment is part interest, part principal. IPMT returns the interest part for one specific period. Early payments are mostly interest; by the end almost none is.
Add IPMT and PPMT for the same period and you always get the full PMT payment.
Syntax
Arguments
| Argument | Required? | What it does |
|---|---|---|
| rate | Yes | The interest rate per period (annual rate ÷ 12 for monthly payments). |
| per | Yes | Which period you want, from 1 to nper. |
| nper | Yes | The total number of payment periods. |
| pv | Yes | The present value — the loan amount, or what a series of payments is worth today. |
| fv | No | The balance you want left after the last payment. Omitted = 0 (a loan paid off in full). |
| type | No | When payments fall: 0 or omitted = end of each period, 1 = start of each period. |
Examples
Example 1 — Interest in the first months of the van loan
The $45,000 van loan at 6.5% over 60 months. Fill the formula down the period column.
| A | B | |
|---|---|---|
| 1 | Annual rate | 6.5% |
| 2 | Months | 60 |
| 3 | Loan amount | $45,000 |
| 4 | ||
| 5 | Month | Interest |
| 6 | 1 | -$243.75 |
| 7 | 2 | -$240.30 |
| 8 | 3 | -$236.83 |
| 9 | 60 | -$4.74 |
Month 1's interest is simply $45,000 × 6.5% ÷ 12 = $243.75. By month 60 there's only $4.74 of interest left.
Common mistakes
- Rate and periods in different units. Keep them matched: for monthly payments divide the annual rate by 12 and multiply the years by 12. Mixing an annual rate with monthly periods gives wildly wrong answers.
- #NUM! error. per must be between 1 and nper. Period 0 or 61 on a 60-month loan fails.
Notes

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.