The tutorial shows how to use PMT function in Excel to calculate payments for a loan or investment based on the interest rate, number of payments, and the total loan amount.
Before you borrow money it's good to know how a loan works. Thanks to the Excel financial functions such as PMT, PPMT and IPMT, computing the monthly or any other periodic payment for a loan is easy. In this tutorial, we will have a closer look at the PMT function, discuss its syntax in detail, and show how to build your own PMT calculator in Excel.
The Excel PMT function is a financial function that calculates the payment for a loan based on a constant interest rate, the number of periods and the loan amount.
"PMT" stands for "payment", hence the function's name.
For example, if you are applying for a two-year car loan with an annual interest rate of 7% and the loan amount of $30,000, a PMT formula can tell you what your monthly payments will be.
For the PMT function to work correctly in your worksheets, please keep in mind these facts:
The PMT function is available in Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007.
The PMT function has the following arguments:
For example, if you make annual payments on a loan at an annual interest rate of 10 percent, use 10% or 0.1 for rate. If you make monthly payments on the same loan, then use 10%/12 or 0.00833 for rate.
For example, if you make annual payments on a 5-year loan, supply 5 for nper. If you make monthly payments on the same loan, then multiply the number of years by 12, and use 5*12 or 60 for nper.
For example, if you borrow $100,000 for 5 years with an annual interest rate of 7%, the following formula will calculate the annual payment:
=PMT(7%, 5, 100000)
To find the monthly payment for the same loan, use this formula:
=PMT(7%/12, 5*12, 100000)
Or, you can enter the known components of a loan in separate cells and reference those cells in your PMT formula. With the interest rate in B1, no. of years in B2, and loan amount in B3, the formula is as simple as this:
=PMT(B1, B2, B3)
Please remember that the payment is returned as a negative number because this amount will be debited (subtracted) from your bank account.
By default, Excel display the result in the Currency format, rounded to 2 decimal places, highlighted in red and enclosed in parenthesis, as shown in the left part of the image below. The image on the right shows the same result in the General format.
If you'd like to have the payment as a positive number, put a minus sign before either the entire PMT formula or the pv argument (loan amount):
=-PMT(B1, B2, B3)
=PMT(B1, B2, -B3)
Tip. To calculate the total amount paid for the loan, multiply the returned PMT value by the number of periods (nper value). In our case, we'd use this equation: 24,389.07*5 and find that the total amount equals $121,945.35.
Below you will find a few more examples of an Excel PMT formula that show how to calculate different periodic payments for a car loan, home loan, mortgage loan, and the like.
For the most part, you can omit the last two arguments in your PMT formulas (like we did in the above examples) because their default values cover the most typical uses cases:
If your loan conditions are different from the defaults, then use the full form of PMT formula.
As an example, let's calculate the amount of annual payments based on these input cells:
Supply these references to your Excel PMT formula:
=PMT(B1, B2, B3, B4, B5)
And you will have this result:
Depending on the payment frequency, you need to use the following calculations for rate and nper arguments:
The below table provides the details:
|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|
For instance, to find the amount of a periodic payment on a $5,000 loan with an 8% annual interest rate and a duration of 3 years, use one of the below formulas.
=PMT(8%/52, 3*52, 5000)
=PMT(8%/12, 3*12, 5000)
=PMT(8%/4, 3*4, 5000)
=PMT(8%/2, 3*2, 5000)
In all cases, the balance after the last payment is assumed to be $0, and the payments are due at the end of each period.
The screenshot below shows the results of these formulas:
Before you go ahead and borrow money, it stands to reason to compare different loan conditions to find out the options that suit you most. For this, let's create our own Excel loan payment calculator.
Formula for Periods (C6):
=IFERROR(VLOOKUP(B6, E2:F6, 2, 0), "")
Formula for Payments are Due (C7):
=IFERROR(VLOOKUP(B7, E8:F9, 2, 0), "")
=IFERROR(-PMT(B4/C6, B5*C6, B3, 0, C7), "")
Please notice the following things:
The above formula goes in B9. And in the neighboring cell (A9) we display a label corresponding to the selected period (B6). For this, simply concatenate the value in B6 and the desired text:
If your Excel PMT formula is not working or produces wrong results, it's likely to be because of the following reasons:
That's how you calculate PMT function in Excel. To have a closer look at the formulas discussed in this tutorial you are welcome to download our PMT Formula Examples. I thank you for reading and hope to see you on our blog next week!
Table of contents