Basic Excel, Excel
Sep 30, 2026
How to Use the Excel PMT Function to Calculate Loan Payments
Key Takeaways
The PMT function in Excel calculates the periodic payment for a loan or mortgage based on a constant interest rate and fixed number of payments.
The three required arguments are RATE (interest rate per period), NPER (total number of payments) and PV (present value or loan amount).
PMT returns a negative number by default because it represents money flowing out; place a minus sign before PMT or multiply by -1 to display a positive result.
Two optional arguments, FV (future value) and TYPE (payment timing), allow you to customize calculations for balloon payments or beginning-of-period payments.
What Is the PMT Function in Excel?
The PMT function in Excel is a built-in financial function that calculates the periodic payment for a loan based on a constant interest rate and a fixed number of payment periods. If you need to figure out how to calculate a loan payment in Excel, PMT is the function to use.
PMT works for any type of installment loan where payments remain the same throughout the term. Common use cases include auto loans, home mortgages, personal loans and student loans. You supply the interest rate, the number of payments and the amount borrowed, and the function returns the payment amount per period. If terms like interest rate and present value feel unfamiliar, a finance and accounting course for non-financial people can build the foundation you need.
Because PMT assumes equal payments and a fixed rate, it is ideal for standard amortizing loans. For adjustable-rate scenarios or irregular payment schedules, you would need a different approach.
PMT Function Syntax and Arguments
The full PMT formula in Excel uses the following syntax:
=PMT(rate, nper, pv, [fv], [type])
Below is a breakdown of each PMT function argument:
rate: The interest rate per period. For monthly payments, divide the annual interest rate by 12.
nper: The total number of payment periods over the life of the loan.
pv: The present value, or the total amount borrowed.
[fv]: (Optional) The future value or remaining balance you want after the last payment. If omitted, Excel assumes 0, meaning the loan is fully paid off.
[type]: (Optional) Indicates when payments are due. Enter 0 or omit for end-of-period payments. Enter 1 for beginning-of-period payments.
The square brackets around fv and type indicate that these arguments are optional. For most standard loan calculations, you only need rate, nper and pv.
How to Calculate a Loan Payment with the PMT Function
In this example, assume you borrow $10,000.00 (D1). Our interest is 3.0% (D2) and the monthly payments are 48 months (D3).
The PMT function calculates the periodic payment for a loan based on constant payments and a constant interest rate. There are three arguments in this function; RATE,NPER,PV. Two additional optional arguments, FV and TYPE, are not required for a basic loan payment.
=PMT(RATE,NPER,PV)
Let's discuss the components of this calculation.
Enter the Rate Argument
Rate is the interest rate on the loan. Payments are usually monthly, and interest is usually annual. You must change annual to monthly by dividing the interest by 12. This gives you a monthly interest rate matching the monthly payment. So, if the interest rate is 3%, you enter D2/12 for the RATE portion of the calculation.
Enter the NPER Argument
NPER is the number of periods over which you repay the loan. This is usually in months. In this example, you would click D3 to represent 48 months.
Enter the PV Argument
PV is the present value. This entry is the amount that was borrowed. You would select D1 here to represent the $10,000 loan.
Understanding the Negative Result
When you do the above steps, your answer will be a negative number. Because this amount represents money you owe, Excel displays the PMT result as a negative value.
Convert the Result to a Positive Number
Should you want to show this as a positive, you have a few options. Multiply the entire function by negative one (-1), or place a negative sign (-) between the equal (=) and the P in PMT. This negates the function's output, resulting in a positive number.
Use the Optional FV and TYPE Arguments
For more complex PMT formulas, two additional arguments allow further customization of your calculations.
FV is the future value. At the end of the payment, you might want to have an unpaid balance for whatever reason. You can enter an amount in FV and Excel will calculate the loan to have that unpaid balance. It lowers your payments, but this FV is still due.
Type is a numeric entry (0 or 1). It allows you to select when you make the payment, either at the beginning of the period (enter 1) or at the end of the period (enter 0 or omit).
These last two arguments are optional and not required for a basic loan calculation.
Common PMT Function Errors and How to Fix Them
Even with a straightforward function like PMT, a few common mistakes can produce unexpected results:
#NUM! error: This occurs when the combination of arguments creates a mathematically impossible calculation. Double-check that your rate, nper and pv values are realistic and that the signs are consistent.
#VALUE! error: Excel returns this when one or more arguments contain text instead of a number. Make sure every cell referenced in the formula holds a numeric valuethe same principle applies to other core functions like the Excel SUM formula.
Incorrect result from mismatched periods: The most frequent PMT mistake is entering an annual interest rate without dividing by 12 when calculating monthly payments. If your rate is 6%, the rate argument for monthly payments must be 6%/12, not 6%.
Forgetting to align rate and nper: If you express the rate as a monthly figure, nper must also be in months. A 30-year loan is 360 monthly periods, not 30. Mixing annual and monthly units will produce a wildly incorrect payment amount.
How to Calculate a Mortgage Payment with the PMT Function
Mortgages are one of the most common reasons people reach for the Excel PMT function. The setup is identical to any other loan calculation, just with larger numbers and a longer term.
Suppose you are financing a $250,000 home at 6.5% annual interest over a 30-year term. The formula would be:
=PMT(6.5%/12, 360, 250000)
This returns approximately -$1,580.17. The negative sign indicates a cash outflow. To display the result as a positive number, use =-PMT(6.5%/12, 360, 250000).
You can adjust the loan amount, rate or term in separate cells and reference them in the formula, just as shown in the loan example above. This makes it easy to compare different mortgage scenarios side by sidean essential step when you plan and monitor a budget.
When to Use PMT vs. Other Financial Functions
PMT is just one of several related financial functions in Excel. Choosing the right one depends on which variable you need to solve for. Here is a quick comparison:
Function | What It Calculates | When to Use It |
|---|---|---|
PMT | Total periodic payment | You know the loan amount, rate and term and need the payment |
IPMT | Interest portion of a specific payment | You need to know how much of payment #12 goes to interest |
PPMT | Principal portion of a specific payment | You need to know how much of payment #12 goes to principal |
NPER | Number of payment periods | You know the payment amount and need to find the loan term |
PV | Present value (loan amount) | You know the payment and need to find how much you can borrow |
FV | Future value | You need the balance remaining after a series of payments |
When building an amortization schedule, you will typically use PMT for the total payment and then IPMT and PPMT to break each payment into its interest and principal components. For a deeper look at all of these functions, see Pryor Learning's guide to Excel finance formulas.
