What function calculates the periodic payment for a loan with a fixed interest rate and fixed term?
|
The PMT function calculates the periodic payment for an annuity investment based on constant-amount periodic payments and a constant interest rate. PMT(0.05/12, 30*12, 100000) PMT(2,12,100) PMT(A2,B2,C2,D2,1) PMT(rate, number_of_periods, present_value, [future_value, end_or_beginning]) rate - The interest rate. number_of_periods - The number of payments to be made. present_value -
The current value of the annuity. future_value - [ OPTIONAL ] - The future value remaining after the final payment has been made. end_or_beginning - [ OPTIONAL - 0 by default ] - Whether payments are due at the end (0) or beginning (1) of each period. PV: Calculates the present value of an annuity investment based on
constant-amount periodic payments and a constant interest rate. PPMT: The PPMT function calculates the payment on the principal of an investment based on constant-amount periodic payments and a constant interest rate. NPER: The NPER function calculates the number of payment periods for an investment based on constant-amount
periodic payments and a constant interest rate. IPMT: The IPMT function calculates the payment on interest for an investment based on constant-amount periodic payments and a constant interest rate. FVSCHEDULE: The FVSCHEDULE function calculates the future value of some principal based on a specified series of potentially varying
interest rates. FV: The FV function calculates the future value of an annuity investment based on constant-amount periodic payments and a constant interest rate. ExamplesGeneral usage Mortgage payments Was this helpful? How can we improve it? 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 it's 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 someone's (usually a bank's) money. The interest portion of a loan payment can be calculated manually by multiplying the period's 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. Excel IPMT function - syntax and basic usesIPMT is Excel's interest payment function. It returns the interest amount of a loan payment in a given period, assuming the interest rate and the total amount of a payment are constant in all periods. To better remember the function's name, notice that "I" stands for "interest" and "PMT" for "payment". The syntax of the IPMT function in Excel is as follows: IPMT(rate, per, nper, pv, [fv], [type]) Where:
For example, if you received a loan of $20,000, which you must pay off in annual installments during the next 3 years with an annual interest rate of 6%, the interest portion of the 1st year payment can be calculated with this formula: =IPMT(6%, 1, 3, 20000) Instead of supplying the numbers directly into a formula, you can input them in some predefined cells and refer to those cells like shown in the screenshot below. In accordance with the cash flow sign convention, the result is returned as a negative number because you pay out this money. By default, it is highlighted in red and enclosed in parenthesis (Currency format for negative numbers) as shown in the left part of the screenshot
below. On the right, you can see the result of the same formula in the General format. If you'd rather get interest as a positive number, put a minus sign before either the entire IPMT function or the pv argument: =-IPMT(6%, 1, 3, 20000) or =IPMT(6%, 1, 3, -20000)  Examples of using IPMT formula in ExcelNow that you know the basics, let's see how to use the IPMT function to find the amount of interest for different frequencies of payment, and how changing the loan conditions changes the potential interest. Before we dive in, it should be noted that IPMT formulas are best to be used after the PMT function that calculates the total amount of a periodic payment (interest + principal). 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 period's rate and the number of years to the total number of payment periods:
The following table shows the calculations:
As an example, let's find the amount of interest you will have to pay on the same loan but in different payment frequencies:
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 functionIn 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, let's define the input cells:
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. Excel IPMT function not workingIf your IPMT formula throws an error, it is most likely to be one of the following:
That's how you use the IPMT function in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our Excel IPMT function sample workbook. I thank you for reading and hope to see you on our blog next week! You may also be interested inWhich function is used to find periodic payment for a fixed loans?PMT, one of the financial functions, calculates the payment for a loan based on constant payments and a constant interest rate.
How do you calculate a loan periodic payment?The formula for how to calculate loan payments on an interest loan is simpler. i is the periodic interest rate. To calculate i, divide the nominal annual interest rate as a percentage by 100. Divide that figure by the number of payment periods in a year.
Which function calculates a periodic rate for an investment or loan given the number of payments fix periodic payments and present value?NPER calculates the periodic interest rate of an investment or loan. A loan amortization table is a schedule that calculates the interest per payment period, principal repayment for each payment, and remaining balance after each payment is made. A MATCH function returns the value of a given position.
What function would you use to calculate the total number of periods in a loan or investment?The Excel NPER function is a financial function that returns the number of periods for a loan or investment. You can use the NPER function to get the number of payment periods for a loan, given the amount, the interest rate, and periodic payment amount.
|
