Why does IPMT return a negative number?

IPMT follows cash-flow convention: outflows (like interest payments) are negative. If you're building a payment schedule for display, use ABS(IPMT()) or multiply by −1. For loans, negative values represent money leaving your account.

What's the difference between IPMT and PPMT?

IPMT returns the interest portion of a payment in a specific period, while PPMT returns the principal portion. Together, IPMT(per) + PPMT(per) = PMT() for any period. IPMT decreases over time; PPMT increases.

How do I calculate total interest paid over the entire loan?

Use CUMIPMT(rate, nper, pv, 1, nper) to sum all interest across all periods. Alternatively, calculate total payments (PMT × nper) and subtract the original principal amount.

Can I use IPMT for investment accounts, or only for loans?

IPMT works for any scenario with constant periodic payments and a fixed interest rate—including investments, savings plans, and bond amortization. Use type=1 if payments arrive at period start rather than end.

IPMT function

IPMT returns the interest portion of a loan or investment payment for a specific period with constant payments and interest rate.

=IPMT(rate, per, nper, pv, [fv], [type])

Generate a IPMT formula

Describe what you need. The generator will reach for IPMT where IPMT is the right tool, and tell you when it is not.

Arguments

How IPMT reads its arguments

Argument	Required	Description
rate	Required	Interest rate per period (must be positive; annual rates are divided by 12 for monthly periods).
per	Required	Period number (1-based integer from 1 to nper; must fall within the loan term).
nper	Required	Total number of payment periods (must be positive; e.g., 360 for 30-year monthly payments).
pv	Required	Present value or principal (typically positive for loans in this context; the amount borrowed).
fv	Optional	Future value remaining after all payments (optional, defaults to 0 for fully amortized loans).
type	Optional	Payment timing: 0 for end-of-period (default), 1 for beginning-of-period payments.

Returns

A number representing the interest payment (typically negative, as an outflow).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate interest in first mortgage payment

Address	Beds	Baths	List Price	Days on Market
123 Oak St	3	2	450000	45
456 Maple Ave	4	3	650000	120
789 Pine Ln	2	1	280000	30

=IPMT(0.065/12, 1, 360, 450000)

Result: -2437.50

123 Oak St is listed at $450,000. A buyer finances the full amount at 6.5% annual over 30 years (360 monthly payments). The first month's interest = $450,000 × (6.5%÷12) = $2,437.50 (shown as negative because it represents a payment outflow).

2. Calculate interest payment mid-term in a mortgage

Address	Beds	Baths	List Price	Days on Market
123 Oak St	3	2	450000	45
456 Maple Ave	4	3	650000	120
789 Pine Ln	2	1	280000	30

=IPMT(0.065/12, 180, 360, 450000)

Result: -1218.75

After 15 years of payments on the same $450,000 mortgage, month 180's interest payment is approximately half of month 1's ($1,218.75 vs $2,437.50). This shows how the interest portion shrinks as the principal balance decreases over the loan term.

3. Calculate interest in the final mortgage payment

Address	Beds	Baths	List Price	Days on Market
123 Oak St	3	2	450000	45
456 Maple Ave	4	3	650000	120
789 Pine Ln	2	1	280000	30

=IPMT(0.055/12, 180, 180, 650000)

Result: -3.25

456 Maple Ave is financed at $650,000 for 15 years (180 months) at 5.5% annual interest. By the final payment (month 180), the remaining principal balance is minimal, so only $3.25 of interest remains—demonstrating how the interest portion shrinks to nearly zero by the loan's end.

Common errors

Which IPMT error are you seeing?

Error	Why it happens	How to fix it
#NUM!	The 'per' argument is outside the valid range (1 to nper). For example, =IPMT(0.065/12, 361, 360, 450000) asks for interest in period 361 of a 360-period loan.	Verify that 'per' is ≥ 1 and ≤ nper. For a 30-year monthly mortgage, per must be between 1 and 360.
#VALUE!	A non-numeric argument is passed where a number is required. For example, =IPMT('6.5%', 1, 360, 450000) passes text instead of a decimal.	Convert text rates to decimal form: '6.5%' becomes 0.065, or 0.065/12 for monthly rates. Use VALUE() or direct arithmetic if needed.
#NUM!	The nper argument is zero or negative, or the rate argument is negative, creating an invalid loan scenario. For example, =IPMT(-0.065/12, 1, 360, 450000).	Ensure rate is non-negative and nper is a positive integer greater than 0.

Tips and when to use something else

IPMT returns a negative number by convention (representing a payment outflow). Use ABS() if you need the absolute value for display or further calculations.
Verify your mortgage math: IPMT(per) + PPMT(per) always equals PMT() for the same period—use this identity to check your formulas.
For total interest paid over a loan, use CUMIPMT(rate, nper, pv, start_period, end_period) instead of summing individual IPMT calls.
When a different function is needed: use PPMT to find the principal portion, or PMT to find the total payment. IPMT alone only gives the interest part.

Related functions

PMTPMT calculates the constant periodic payment needed to repay a loan or investment over a fixed number of periods at a fixed interest rate.PPMTReturns the principal payment for a given period of a loan or investment, given constant periodic payments and a constant interest rate.CUMIPMTCUMIPMT returns the cumulative interest paid on a loan between two specified periods, given a fixed interest rate and payment schedule.FVFV returns the future value of a series of regular cash flows at a constant interest rate, modeling compound growth over time.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.

Frequently asked questions

Why does IPMT return a negative number?: IPMT follows cash-flow convention: outflows (like interest payments) are negative. If you're building a payment schedule for display, use ABS(IPMT()) or multiply by −1. For loans, negative values represent money leaving your account.
What's the difference between IPMT and PPMT?: IPMT returns the interest portion of a payment in a specific period, while PPMT returns the principal portion. Together, IPMT(per) + PPMT(per) = PMT() for any period. IPMT decreases over time; PPMT increases.
How do I calculate total interest paid over the entire loan?: Use CUMIPMT(rate, nper, pv, 1, nper) to sum all interest across all periods. Alternatively, calculate total payments (PMT × nper) and subtract the original principal amount.
Can I use IPMT for investment accounts, or only for loans?: IPMT works for any scenario with constant periodic payments and a fixed interest rate—including investments, savings plans, and bond amortization. Use type=1 if payments arrive at period start rather than end.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17