The tutorial shows how to use the IPMT function in Excel to find the interest portion of a periodic payment on a loan or mortgage.
Whenever you take out a loan, whether its a mortgage, home loan or car loan, you need to pay back the amount you originally borrowed and interest on top of it. In simple terms, interest is the cost of using someones (usually a banks) money.
The interest portion of a loan payment can be calculated manually by multiplying the periods interest rate by the remaining balance. But Microsoft Excel has a special function for this – the IPMT function. In this tutorial, we will go in-depth explaining its syntax and providing real-life formula examples.
Calculating loan interest in Excel is an essential skill for anyone dealing with loans, mortgages, or other types of financing With the right Excel formulas, you can quickly determine monthly payments, total interest costs, loan terms, and more
In this comprehensive guide, I’ll walk you through step-by-step how to calculate different types of loan interest and payments in Excel. You’ll learn:
- How to calculate simple loan interest using the present value (PV) formula
- How to find monthly loan payments with the PMT function
- How to determine total interest paid over the life of a loan
- How to create an amortization schedule that breaks down each payment
Whether you’re a banker accountant financial analyst, or just trying to understand your personal finances, this guide has everything you need to become an Excel loan interest calculation expert. Let’s dive in!
Overview of Calculating Loan Interest in Excel
Before we get into the specifics, let’s do a quick overview of the key principles and Excel functions used for calculating loan interest.
Key Principles
-
Present Value (PV) – The present value is the principal amount borrowed. This is the starting value we’ll use in the loan calculation formulas.
-
Future Value (FV) – The future value is the total amount owed after all payments, including interest, are made.
-
Interest Rate (Rate) – The interest rate is expressed as a percentage (e.g. 6% would be input as 0.06).
-
Number of Periods (Nper) – The number of payment periods over the full loan term.
-
Periodic Payment (PMT) – The regular payment made each period toward the loan.
Key Excel Functions
-
=PV – Calculates present value based on future payments, interest rate, and number of periods.
-
=PMT – Determines the periodic payment amount based on present value, interest rate, and number of periods.
-
=IPMT – Calculates interest payment portion of a periodic payment.
-
=CUMPRINC – Determines principal portion of a periodic payment.
Now let’s look at how to use these principles and functions for various loan interest calculation scenarios.
Calculating Simple Loan Interest
Simple interest loans charge interest based only on the principal amount, not the changing balance with accumulated interest. Here’s how to calculate simple loan interest in Excel:
-
Enter the Principal amount, Annual Interest Rate, and Term in Years as input values.
-
Use the formula =PV(Rate, Nper, PMT) to calculate the total interest amount owed.
- Rate is the annual interest rate divided by 12 or number of periods per year.
- Nper is the loan term in years multiplied by 12.
- PMT is left blank.
-
Add the interest amount to the original principal for the total future value owed.
For example, for a $10,000 loan at 6% annual interest over 5 years:
- Principal = $10,000
- Annual Rate = 6%
- Number of Periods = 5 * 12 = 60 months
- Payment = leave blank
The formula would be =PV(0.06/12, 60, ) which returns $3,000. Add that to the $10,000 principal for a $13,000 total future value.
This calculates all the simple interest owed over the life of the loan.
Finding Monthly Loan Payments
For installment loans, mortgages, and other types of amortized loans, we need to calculate the monthly payment amount needed to pay off the loan over the set term. The PMT function does this for us.
Follow these steps:
-
Enter the Principal, Annual Interest Rate, and Loan Term in Years.
-
Use the formula =PMT(Rate, Nper, PV)
- Rate is the annual interest rate divided by 12.
- Nper is the loan term in years multiplied by 12.
- PV is the principal amount borrowed.
-
The calculated PMT value is the monthly payment.
For a $200,000 mortgage at 5% interest over 30 years:
- Principal = $200,000
- Annual Rate = 5%
- Term = 30 years x 12 months = 360 months
The formula is =PMT(0.05/12, 360, 200000) which returns a monthly payment of $1,073.64.
This payment amount will fully amortize the loan over the 360 month term at 5% interest.
Calculating Total Interest Paid
To determine the total interest paid over the full loan term, we can use the CUMPRINC and PMT functions together:
-
Calculate the monthly payment with PMT.
-
Use CUMPRINC to find the Total Principal Paid over the term.
- The formula is =CUMPRINC(rate, nper, pmt, pv, 0)
- Enter all the same inputs used in PMT. The 0 is for the start period.
-
Subtract the Total Principal Paid from the original Principal amount borrowed to get the Total Interest Paid.
Using the mortgage example above with a $200,000 principal and $1,073.64 monthly payment:
-
CUMPRINC(0.05/12, 360, 1073.64, 200000, 0) = $200,000
-
$200,000 (original principal) – $200,000 (principal paid) = $0 total interest
This quickExcel method lets you validate that the payment fully covers interest owed over the term.
Building a Loan Amortization Schedule
To see a month-by-month breakdown of how each payment is applied to interest vs principal, we can create a loan amortization schedule.
Follow these steps:
-
Make a table with columns for Period, Payment, Principal Paid, Interest Paid, and Balance.
-
In the Period column, enter consecutive numbers starting from 1 for each payment period.
-
The Payment column should use the PMT value calculated previously.
-
Principal Paid uses the CUMPRINC formula, increasing the Start Period by 1 each row.
-
Interest Paid uses the IPMT formula based on the same inputs used in PMT.
-
Balance subtracts the Principal Paid from the prior period’s Balance.
This table will show how much of the constant periodic payment is applied to interest vs principal each month as the remaining balance declines.
The flexibility of Excel allows modeling any loan or interest scenario. You can adjust inputs to instantly see the impact on monthly payments, total interest costs, amortization schedules, and more.
IPMT formula for different payment frequencies (weeks, months, quarters)
To get the interest portion of a loan payment right, you should always convert the annual interest rate to the corresponding periods rate and the number of years to the total number of payment periods:
- For the rate argument, divide the annual interest rate by the number of payments per year, assuming the latter is equal to the number of compounding periods per year.
- For the nper argument, multiply the number of years by the number of payments per year.
The following table shows the calculations:
| Frequency of payment | Rate argument | Nper argument |
| Weekly | annual interest rate / 52 | years * 52 |
| Monthly | annual interest rate / 12 | years * 12 |
| Quarterly | annual interest rate / 4 | years * 4 |
| Semi-annual | annual interest rate / 2 | years * 2 |
As an example, lets find the amount of interest you will have to pay on the same loan but in different payment frequencies:
- Annual interest rate: 6%
- Loan duration: 2 years
- Loan amount: $20,000
- Period: 1
The balance after the last payment is to be $0 (the fv argument omitted), and the payments are due at the end of each period (the type argument omitted).
Weekly:
=IPMT(6%/52, 1, 2*52, 20000)
Monthly:
=IPMT(6%/12, 1, 2*12, 20000)
Quarterly:
=IPMT(6%/4, 1, 2*4, 20000)
Semi-annual:
=IPMT(6%/2, 1, 2*2, 20000)
Looking at the screenshot below, you can notice that the interest amount decreases with each subsequent period. This is because any payment contributes to reducing the loan principal, and this reduces the remaining balance on which interest is calculated.
Also, please notice that the total amount of interest payable on the same loan differs for annual, semi-annual and quarterly installments: 
Full form of the IPMT function
In this example, we are going to calculate interest for the same loan, the same payment frequency, but different annuity types (regular and annuity-due). For this, we will need to use the full form of the IPMT function.
To begin with, lets define the input cells:
- B1 – annual interest rate
- B2 – loan term in years
- B3 – number of payments per year
- B4 – loan amount (pv)
- B5 – future value (fv)
- B6 – when the payments are due (type):
- 0 – at the end of a period (regular annuity)
- 1 – at the beginning of a period (annuity due)
Assuming the first period number is in A9, our interest formula goes as follows:
=IPMT($B$1/$B$3, A9, $B$2*$B$3, $B$4, $B$5, $B$6)
Note. If you plan to use the IPMT formula for more than one period, please mind the cell references. All the references to the input cells shall be absolute (with the dollar sign) so they are locked to those cells. The per argument must be a relative cell reference (without the dollar sign like A9) because it should change based on the relative position of a row to which the formula is copied.
So, we enter the above formula in B9, drag it down for the remaining periods, and get the following result. If you compare the numbers in the Interest columns (regular annuity on the left and annuity-due on the right), you will notice that interest is a little lower when you pay at the beginning of period. 
How to find Interest & Principal payments on a Loan in Excel
How to calculate interest on a loan in Excel?
Let’s break down how to calculate interest on a loan in Excel using the PMT function. Using the PMT Function: The PMT function calculates the fixed repayment for a loan based on constant payments and a constant interest rate. Here’s the syntax: rate : The interest rate per period (monthly in our case). Divide the annual rate by 12: C4/12.
How to calculate annual interest rate in Excel?
To get an annual interest rate, we multiply by 12: With the percent number format applied to cell C10, the result is displayed as 4.50%. To calculate the periodic interest rate for a loan, given the loan amount, the number of payment periods, and the payment amount, you can use the RATE function.
How do I calculate a loan interest rate?
As simple as calculating a payment with basic loan details, you can do the same to determine the interest rate. Get the loan term, monthly payment, and loan amount and enter them in your sheet. Select the cell where you want to see the interest rate. You’ll then enter the formula for the RATE function.
How to calculate interest payment in Excel?
Fill out the principal amount, interest rate, and the number of payment periods. In the Payment row, use the formula =IPMT(B2, 1, B3, B1) to calculate the interest payment. Open a Blank Workbook. Launch Excel, then click Blank workbook to get started. Skip this step on Mac. Set up your rows.
