What is the Excel IPMT Function?
The IPMT Function in Excel determines the interest component of a loan payment, assuming a fixed interest rate throughout the borrowing period.
Table of Contents
 How to Use IPMT Function in Excel?
 Excel IPMT vs. PMT Function: What is the Difference?
 Excel IPMT Function Formula
 IPMT Excel Function Syntax
 IPMT Function Calculator – Excel Template
 1. Interest on Loan Exercise Assumptions
 2. Frequency of Payments (Create Dropdown List)
 3. Calculate Interest Payment Using IPMT Excel Function
How to Use IPMT Function in Excel?
The Excel “IPMT” function calculates the periodic interest payments owed to a lender by a borrower on a loan, such as a mortgage or car loan.
Upon committing to a loan, the borrower is required to pay interest periodically to the lender, as well as repay the original loan principal by the end of the borrowing term.
 Borrower (Debtor) ➝ The interest rate reflects the cost of financing to the borrower, which directly impacts the size of the interest payment (i.e. “cash outflow”)
 Lender (Creditor) ➝ The interest rate reflects the expected return given the risk profile of the borrower, with interest being one of the sources of returns to the lender (i.e. “cash inflow”).
The interest portion of a loan payment can be calculated manually by multiplying the period’s interest rate by the loan principal, which tends to be the norm in financial models.
But the Excel IPMT function was created with that specific purpose in mind, i.e. to calculate the periodic interest owed.
The amount owed in each period is a function of the fixed interest rate and the number of periods that have passed since the date of issuance.
Closer to maturity, the value of the interest payments decline in value alongside the amortizing loan principal balance.
But while the interest paid in each period is based on the outstanding principal balance, the interest payments themselves do NOT reduce the principal.
Excel IPMT vs. PMT Function: What is the Difference?
The PMT function in Excel calculates the periodic payment on a loan. For example, the monthly mortgage payments a borrower owes.
In contrast, the IPMT calculates only the interest owed; hence the “I” in front.
 IPMT Function ➝ Interest
 PMT Function ➝ Principal + Interest
IPMT function is thereby a part of the PMT function, but the former calculates only the interest component, whereas the latter calculates the entire payment inclusive of both the principal repayment and interest.
Under either calculation, however, there can be other fees and costs incurred, such as taxes, that could affect the yield earned by the lender.
Excel IPMT Function Formula
The formula for using the IPMT function in Excel is as follows.
The inputs with brackets around them—“fv” and “type”—are optional and can be omitted, i.e. either left blank or a zero can be entered.
Since the interest payment is an “outflow” of cash from the perspective of the borrower, the calculated payment will be negative.
For our calculation of the interest payment to be accurate, we must be consistent with our units.
Frequency  Interest Rate Adjustment (rate)  Number of Periods Adjustment (nper) 

Monthly 


Quarterly 


SemiAnnual 


Annual 


For a quick example, let’s say a borrower took out a 4year loan with an annual interest rate of 9.0% paid on a monthly basis. In this case, the adjusted monthly interest rate is 0.75%.
 Monthly Interest Rate (rate) = 9.0% ÷ 12 = 0.75%
In addition, the number of periods must be converted appropriately to months by multiplying the borrowing term stated in years by the frequency of payments.
 Number of Periods (nper) = 4 × 12 = 48 Periods
IPMT Excel Function Syntax
The table below describes the syntax of the Excel IPMT function in more detail.
Argument  Description  Required? 

“rate” 


“nper” 


“pv” 


“fv” 


“type” 


IPMT Function Calculator – Excel Template
We’ll now move on to a modeling exercise, which you can access by filling out the form below.
1. Interest on Loan Exercise Assumptions
Suppose a consumer has taken out a $200k loan to finance the purchase of an office space.
The loan is priced at an annual interest rate of 6.00% per annum, with payments made on a monthly basis at the end of each month.
 Loan Principal (pv) = $200,000
 Annual Interest Rate (%) = 6.00%
 Borrowing Term = 10 Years
 Compounding Frequency = Monthly (12x)
Because our units are not consistent with one another, the next step is to convert the annual interest rate to a monthly interest rate and convert our borrowing term into a monthly figure.
 Monthly Interest Rate (rate) = 6.00% ÷ 12 = 0.50%
 Number of Periods (nper) = 10 Years × 12 = 120 Periods
2. Frequency of Payments (Create Dropdown List)
As an optional next step, we’ll create a dropdown list to toggle between the frequency of payments using the following steps:
 Step 1 ➝ Select the “Compounding Frequency” Cell (E8)
 Step 2 ➝ “Alt + A + V + V” Opens Data Validation Box
 Step 3 ➝ Pick “List” in the Criteria
 Step 4 ➝ Enter “Monthly”, “Quarterly”, “SemiAnnual”, or “Annual” into the “Source” line
In Cell E9, we’ll create a formula with a string of “IF” statements to output the corresponding figure we selected in the list.
The remaining two arguments are the “fv” and “type”.
 Future Value ➝ For “fv”, the input will be kept blank because we’ll assume the loan was fully repaid by the end of the term (i.e. the borrower did not default).
 Type ➝ The other assumption, “type”, refers to the timing of the payments, which we’ll omit to assume the payments come due at the end of each month.
3. Calculate Interest Payment Using IPMT Excel Function
In the final part of our Excel tutorial, we’ll build our interest payment schedule using the assumptions from the prior steps.
The IPMT formula in Excel we’ll use to calculate the interest each period is as follows.
Except for the period column (e.g. B13), the other cells must be anchored by clicking F4.
In conclusion, once our inputs have been entered into the “IPMT” function in Excel, the total interest paid over the tenyear loan comes out to $9,722. The total interest owed on a monthly basis can be seen in our completed interest payment schedule build.
Shouldnt the number of periods of be 120 and not 10?
Hi, Sheharyar,
Yes, and if you look at the function solution, it is referencing 120 periods.
BB