MS Excel: IPMT function to calculate interest payment

The IPMT function in Excel calculates the interest payment for a given period of a loan or investment, based on constant periodic payments and a constant interest rate.

In simple words: It tells you how much of your payment is just interest for a specific month or period.

Syntax

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

Argument	Description
rate	Interest rate per period. (Annual rate divided by number of periods.)
per	The specific period number you want the interest for (must be between 1 and nper).
nper	Total number of payment periods.
pv	Present value or principal amount (e.g., loan amount).
[fv] (optional)	Future value after the last payment. Default is 0.
[type] (optional)	Timing of payment: 0 = end of period (default), 1 = beginning of period.

How IPMT Works

When you borrow or invest money, each payment is part interest and part principal.IPMT helps you find the interest part of any single payment.

Example 1: Basic Loan Interest Calculation

You borrow $10,000 with an annual interest rate of 6%, to be repaid over 5 years with monthly payments.

Find the interest portion of the first payment.

Table Setup:

Cell	Data
A2	Annual Interest Rate: 6%
A3	Period to check: 1
A4	Total Periods (months): 60
A5	Loan Amount (Present Value): 10000

Formula in Excel:

=IPMT(A2/12, A3, A4, A5)

Result:

Interest Payment = -50

(Negative because it's money you pay.)

Step-by-Step:

• Monthly interest rate = 6% / 12 = 0.5% per month

• First payment:Interest = 10,000 × 0.5% = $50

(Note: Result is negative because it’s an outgoing payment.)

Example 2: Later Payment (Period 10)

Now, find the interest portion in the 10th month.

Cell	Data
A3	Period: 10

Formula:

=IPMT(A2/12, A3, A4, A5)

Result:

Interest Payment ≈ -46.02

Because after several payments, the principal has been reduced slightly, and so the interest is less.

Example 3: Payments at Beginning of Period

Suppose you make payments at the start of each month.Set type = 1.

Cell	Data
A6	Type: 1

Formula:

=IPMT(A2/12, A3, A4, A5, 0, A6)

Now the interest payment will be slightly different because the principal decreases faster.

Important Notes

• rate must match the periods (monthly if payments are monthly).

• per must be between 1 and nper.

• If you don't set fv or type, Excel assumes 0 (loan fully repaid) and 0 (payments made at end of period).

• Negative results indicate money flowing out (payment you make).

Real-World Applications

• Mortgage amortization schedules.

• Loan interest tracking.

• Car loan calculations.

• Investment interest analysis.

Full Example Table

Period	Formula	Result (Interest)
1	=IPMT(6%/12,1,60,10000)	-50.00
2	=IPMT(6%/12,2,60,10000)	-49.58
10	=IPMT(6%/12,10,60,10000)	-46.02
60	=IPMT(6%/12,60,60,10000)	-0.25

Conclusion

The IPMT function is an essential financial tool in Excel, helping you easily separate the interest from each payment when dealing with loans or investments.By mastering it, you can create accurate loan schedules, plan repayments, and better understand the structure of your debts.