How to Use the Excel IPMT Function to Calculate Interest Payments on a Loan

The Cost of Borrowed Money

When you take out a loan—whether it is a 30-year mortgage for a house, a 5-year auto loan, or a small business equipment loan—your monthly payment is generally a fixed amount. For example, you might pay exactly $1,200 every single month.

However, what happens inside that $1,200 changes drastically over time.

In the first month of a loan, the vast majority of your payment goes straight to the bank as pure profit (Interest), and very little goes toward paying down the actual debt (Principal). By the final year of the loan, the ratio flips: almost all of your payment pays off the debt, with very little going to interest.

This process is called amortization. If you want to know exactly how much of your money is being burned on interest in any specific month (perhaps for tax deduction purposes), you cannot simply divide the total interest by the number of months. You must use Excel’s IPMT (Interest Payment) function.

Step 1: Understanding the IPMT Syntax

The IPMT function requires four pieces of mandatory information to calculate the interest portion of a specific payment period.

The Syntax:
=IPMT(rate, per, nper, pv)

• rate: The interest rate per period.
• per: The specific period (month) you want to investigate (must be between 1 and the total number of payments).
• nper: The total number of payment periods.
• pv: The present value, or the total original amount of the loan.

Step 2: Preparing the Data Correctly

The most common mistake users make with financial functions is failing to align the time periods.

Banks quote interest as an Annual Percentage Rate (APR), and loans in years. But you make payments monthly. Therefore, you must divide the annual rate by 12, and multiply the years by 12.

Let’s set up a scenario in Excel:

• Cell B1 (Loan Amount): $250,000 (pv)
• Cell B2 (Annual Interest Rate): 6.5% (rate)
• Cell B3 (Loan Term in Years): 30 (nper)

Step 3: Calculating the First Month’s Interest

You want to know exactly how much of your very first payment on this $250,000 mortgage is going straight to the bank as interest.

Select an empty cell and enter the formula, ensuring you convert the annual numbers to monthly numbers:

=IPMT(B2/12, 1, B3*12, B1)

Breaking down the math:

• B2/12: The 6.5% annual rate divided by 12 months.
• 1: We are asking for the interest on payment number 1.
• B3*12: 30 years multiplied by 12 months (360 total payments).
• B1: The $250,000 loan balance.

The Result: Excel will output -$1,354.17.
(Note: Financial functions output negative numbers because they represent cash leaving your wallet. Your first month’s payment includes over $1,350 in pure interest!).

Step 4: Calculating Interest Years Later

Now, let’s look at the future. You want to know how much interest you will be paying on the exact same loan 15 years from now.

Fifteen years into a monthly loan is payment number 180 (15 x 12 = 180).

Change the per argument in your formula from 1 to 180:

=IPMT(B2/12, 180, B3*12, B1)

The Result: -$1,023.75. Even halfway through the 30-year mortgage, you are still paying over a thousand dollars a month in pure interest.

Step 5: Building a Full Amortization Schedule

The true power of IPMT is realized when you combine it with its sister function, PPMT (Principal Payment), to build a complete amortization table.

1. Create a column listing the numbers 1 through 360 (representing every month of the loan).
2. In the next column, write the IPMT formula, but point the per argument to the cell containing the month number (e.g., A10).
3. Ensure you use absolute references (dollar signs, e.g., $B$2/12) for the rate, nper, and pv so they do not shift when you drag the formula.
4. Drag the formula down all 360 rows.

You now have a complete, penny-accurate roadmap of exactly how much interest the bank is extracting from you every single month for the next thirty years.