How to use PMT function in Excel [step by step guide]
The tutorial shows how to use PMT function in Excel to calculate the annuity for a loan or the cash flows for investments based on a fixed interest rate, number of payments, and the total loan or investment amount.
This applies to the following Microsoft Excel versions: Excel for Microsoft 365, Excel 2021, 2019, 2016, 2013, 2010, 2007, and 2003.
Before you begin, I recommend downloading the Excel spreadsheet I’ve used to create this tutorial. It makes it much easier to follow along.
How to use PMT function
PMT is a financial function that returns the periodic payment for a loan based on a constant interest rate, the number of periods, and the loan amount.
PMT is the abbreviation for payment, which makes the function name easy to remember.
The PMT function uses the following syntax:
=PMT(rate, nper, pv, [fv], [type])
- rate (required) – the constant interest rate for the loan or investment; if you have a loan with 12% annual interest rate and yearly payments, then you would use 12% or 0.12; for monthly payment periods you would use 1% (12%/12);
- nper (required) – the total number of payments for the loan; for a 30 years loan that monthly loan payments, you would insert 360 (that’s 30*12);
- pv (required) – the present value of all future payments; in the case of a loan, it’s simply the amount borrowed.
- fv (optional) – the future value is the cash balance that you wish to have after the last payment is made; for a loan, this is usually zero, as you want to fully repay the loan;
- type (optional) – specifies when the payments are due: 0 for the end of the period, 1 for the beginning of the period.
Note: If omitted, the fv and type arguments use the default value of 0.
How to use the PMT function in Excel – formula examples
Let’s look at a simple PMT formula and calculate the future payments for a loan, assuming constant payments and a constant interest rate.
We’ll use an example for a house that costs $200,000, with a down payment of $30,000 and a loan amount of $170,000. The mortgage loan has a period of 30 years and an annual interest rate of 5.00%, and payments are due monthly.
How to write a PMT formula in Excel
Using the Excel file provided for this tutorial, you can write the PMT formula in 6 steps:
- Select the cell where you want to insert the PMT formula. Using your mouse or keyboard navigate to the cell where you want to insert your formula. In my file, this is cell F4.
- Type =-PMT(
- Select the rate argument. In my case, this is C3/12 because we are using monthly payments.
- Enter the number of periods or the nper argument. In this example, this is C4*12 which is equal to 360 months.
- Select the present value of the loan or the pv argument. I’ve used cell C6.
- Type ) to close the function and press ENTER.
Since the fv and type arguments were omitted, Excel used their default value of zero. The minus sign at the beginning of the formula is used to convert the amount to a positive figure.
Calculate weekly, monthly, quarterly, and semi-annual payments
The same function can be used to write formulas that calculate periodic payments for a mortgage loan, a car loan, or a student loan. As long as the interest rate is constant, the PMT function can be used to determine the loan payment.
Depending on the payment frequency, you will have to convert the rate and nper arguments. The following table shows how to easily 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|
To calculate the amount of a periodic payment for a $10,000 loan with a 6.00% annual interest rate and duration of 5 years, you can use one of the following PMT formulas:
=-PMT(6%/52, 5*52, 10000)
=-PMT(6%/12, 5*12, 10000)
=-PMT(6%/4, 5*4, 10000)
=-PMT(6%/2, 5*2, 10000)
=-PMT(6%, 5, 10000)
Note: The balance after the last payment (i.e., the fv argument) is assumed to be $0. Payments are due at the end of each period, so the type argument is omitted.
Things to remember about the PMT function
If you want your PMT formula to work properly you need to take into account the following:
- The returned PMT value is a negative number since these are periodic payments; you can add the minus sign in front of your formula to convert the result to a positive number;
- The value returned by PMT includes principal and interest amounts but does not include any fees, taxes, or reserve payments associated with a loan;
- The PMT function can compute a loan or investment cash flows for different frequencies (yearly, quarterly, monthly, weekly); all you need to do is adjust the total number of periods and convert the annual rate to match your payment frequency.
Excel PMT function not working properly
If your Excel PMT formula is not working or returns an error message, there are a few things to investigate:
- If the result of your PMT formula is significantly higher or lower than expected, check to see if your rate and nper arguments are consistent. Using an annual interest rate with monthly payments will result in a much higher monthly payment.
- If your PMT function returns the #VALUE! error, then one of the arguments is text. Check your data and make sure you don’t have numbers stored as text.
- If your PMT function returns the #NUM! error check if the rate argument is negative or if the nper argument is equal to 0.
What to do next?
The formulas from this Excel tutorial return the total value of a payment (includes principal and interest). If you are looking for a way to split the interest and principal, two financial functions can help you achieve this: the PPMT function and the IPMT function.
As always, if you are having difficulties using the PMT function, please leave a comment, and I’ll reply as soon as possible.