The tutorial shows how to use IPMT function in Excel to calculate the interest portion for a given period of a loan, mortgage, or investment. The function uses constant payments and a constant interest rate.
This applies to the following Microsoft Excel versions: Excel for Microsoft 365, Excel 2021, 2019, 2016, 2013, 2010, 2007, and 2003.
I recommend downloading the IPTM formula Excel spreadsheet I've used to create this tutorial. It makes it easy for you to follow along.
IPMT is a financial function that returns the interest payment for a loan. IPMT is the abbreviation for interest payment.
The IPMT function uses the following syntax:
=IPMT(rate, per, nper, pv, [fv], [type])
Note: If omitted, the optional parameters fv and type arguments will use the default value of zero.
Let's assume that we have a mortgage loan with a period of 30 years and a constant annual interest rate of 5%. The borrowed amount is $170,000, and payments are due at the end of each month. After the last payment is made, the residual value is $0 (i.e., we want to repay the entire loan).
As I mentioned before, the IPTM function returns the interest portion for a specified period. Let's say that we want to retrieve the interest due at the end of the second year. This means that our per argument should be 24. You can write the IPMT formula in 7 steps:
When you use the IPMT function to calculate the interest portion of an annuity, you should remember that the returned values are negative because they represent outgoing payments. If you need to show positive numbers, you can simply add the minus sign at the beginning of your formula, which will revert the sign of negative numbers (like I did in the previous example).
Whenever you use financial functions, it's essential to make sure that you are setting the correct payment frequency and converting the rate and nper arguments accordingly. I have included a reference table that shows how to convert from annual interest rate and annual payments to weekly, monthly, quarterly, and semi-annual payments:
|weekly||annual interest rate / 52||years * 52|
|monthly||annual interest rate / 12||years * 12|
|quarterly||annual interest rate / 4||years * 4|
|semi-annually||annual interest rate / 2||years * 2|
I have prepared an example to demonstrate how the IPMT formula can be adjusted to match different payment frequencies. Let's calculate the amount of the first interest payment for a $10,000 loan with a 6.00% annual interest rate and duration of 5 years.
=-IPMT(6%/52, 1, 5*52, 10000)
=-IPMT(6%/12, 1, 5*12, 10000)
=-IPMT(6%/4, 1, 5*4, 10000)
=-IPMT(6%/2, 1, 5*2, 10000)
=-IPMT(6%, 1, 5, 10000)
Note: The balance after the last payment (i.e., the fv argument) is assumed to be $0. Interest payments are due at the end of each period, so the type argument is omitted.
If you want your IPMT formula to work properly you need to consider the following:
If your IPMT formula returns an error or is not working properly, there are several things to check:
The formulas from this Excel tutorial return the interest payment (no principal included). If you want to calculate the total loan payment, you can use the PMT function. Alternatively, you can also calculate the principal payment using the PPMT function and sum the interest and principal payments to get the total loan payment.
If you are having difficulties using the IPMT function, please leave a comment.
Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?