Financial
All versions
◉ 18 views
=IPMT()
Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=IPMT(rate, per, nper, pv, [fv], [type])
Hover argument names in the table for details.
Output typeNumber
Popularity45 / 100
VolatileNo
Dynamic ArrayNo
Download sample .xlsx ▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =IPMT() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
rate |
Number | Required | The interest rate per period. |
per |
Number | Required | The period for which you want to find the interest and must be in the range 1 to nper. |
nper |
Number | Required | The total number of payment periods in an annuity. |
pv |
Number | Required | The present value, or the lump-sum amount that a series of future payments is worth right now. |
fv |
Number | Optional | The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (the future value of a loan, for example, is 0). |
type |
Number | Optional | The number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. 0 = At the end of the period; 1 = At the beginning of the period. |
▣ Examples
▦ Data
| Cell | Value | Description |
|---|---|---|
A2 |
10.00% | Annual interest |
A3 |
1 | Period for which you want to find the interest paid. |
A4 |
3 | Years of loan |
A5 |
$8,000 | Present value of loan |
A6 |
10.00% | Annual interest |
A7 |
1 | Period for which you want to find the interest paid. |
A8 |
3 | Years of loan |
A9 |
$8,000 | Present value of loan |
Interest due in the first month for a loan with the terms in A2:A5.
=IPMT(A2/12, A3, A4*12, A5)
Result:
($66.67)
Interest due in the last year for a loan with the same terms, where payments are made yearly.
=IPMT(A2, 3, A4, A5)
Result:
($292.45)
§ Remarks
- Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
- For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers. Cash you receive, such as dividend checks, is represented by positive numbers.
✦ Tips
- Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12 percent annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.
- For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers. Cash you receive, such as dividend checks, is represented by positive numbers.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!