Excel PMT Function
last modified October 5, 2026
The PMT function calculates loan payments based on constant
payments and interest rate. It's essential for financial planning and loan
analysis. This tutorial provides a comprehensive guide to using the
PMT function with detailed examples. You'll learn basic syntax,
practical applications, and advanced techniques to master this financial
function.
PMT Function Basics
The PMT function calculates the periodic payment for a loan. It
considers interest rate, number of periods, and loan amount. The result
includes both principal and interest components.
| Component | Description |
|---|---|
| Function Name | PMT |
| Syntax | =PMT(rate, nper, pv, [fv], [type]) |
| Required Arguments | rate, nper, pv |
| Optional Arguments | fv, type |
| Return Value | Periodic payment amount |
This table breaks down the essential components of the PMT
function. It shows the function name, syntax format, required and optional
arguments, and return value characteristics.
The first three arguments are required and describe the loan; the two optional arguments fine-tune the result.
| Argument | Meaning |
|---|---|
| rate | Interest rate per period. Divide an annual rate by the number of periods per year, for example 6%/12 for monthly payments. |
| nper | Total number of payment periods. Multiply the years by the periods per year, for example 10*12 for a ten-year monthly loan. |
| pv | Present value: the amount borrowed or invested today. Cash you receive is entered as a positive number. |
| fv | Future value: the cash balance to reach after the last payment. When omitted it defaults to 0, meaning the loan is paid off in full. |
| type | Payment timing: 0 or omitted for the end of a period, 1 for the beginning. |
The rate and nper arguments must use the same time
unit. Use a monthly rate with a count of months, or an annual rate with a count
of years, but never mix the two.
Basic PMT Example
This example demonstrates the simplest use of the PMT function with a basic loan scenario.
=PMT(5%/12, 60, 20000)
This formula calculates monthly payments for a $20,000 loan at 5% annual interest over 5 years (60 months). The result will be -$377.42. The negative sign indicates an outgoing payment.
Understanding the Sign Convention
PMT follows Excel's cash-flow sign convention: money you pay out
is negative and money you receive is positive. Because the loan amount
(pv) is entered as a positive number, the returned payment is
negative.
=PMT(5%/12, 60, 20000) ' -377.42, you pay out =PMT(5%/12, 60, -20000) ' 377.42, you received the loan
The size of the payment is the same either way; only the sign changes. To show
the payment as a positive number, wrap the formula in ABS:
=ABS(PMT(5%/12, 60, 20000)) returns 377.42.
PMT with Annual Payments
This example shows how to calculate annual payments for a loan using the PMT function.
| A | B |
|---|---|
| Rate | 6% |
| Term | 10 |
| Amount | 100000 |
| Payment | =PMT(B1, B2, B3) |
The table shows a loan of $100,000 at 6% annual interest for 10 years. The PMT formula in B4 calculates the annual payment amount.
=PMT(B1, B2, B3)
This formula calculates annual payments for the loan parameters in B1-B3. The result will be -$13,586.80 per year. Note we use the annual rate directly since payments are annual.
PMT with Future Value
This example demonstrates using the optional future value (fv) parameter to calculate payments needed to reach a savings goal.
| A | B |
|---|---|
| Rate | 4% |
| Term | 20 |
| Current | 0 |
| Goal | 100000 |
| Payment | =PMT(B1/12, B2*12, B3, B4) |
The table shows a savings goal of $100,000 in 20 years with 4% annual interest. We calculate the monthly deposit needed starting from $0.
=PMT(B1/12, B2*12, B3, B4)
This formula calculates monthly deposits needed to reach $100,000 in 20 years at 4% interest. The result is -$272.65. The negative value indicates an outgoing payment (deposit).
PMT with Payment Timing
This example shows how the type parameter affects calculations when payments are due at the beginning of the period.
| A | B |
|---|---|
| Rate | 3.5% |
| Term | 30 |
| Amount | 250000 |
| Payment | =PMT(B1/12, B2*12, B3, 0, 1) |
The table shows a $250,000 mortgage at 3.5% for 30 years with payments due at the beginning of each month. The type parameter (1) changes the calculation.
=PMT(B1/12, B2*12, B3, 0, 1)
This formula calculates monthly mortgage payments due at period start. The result is -$1,119.35 compared to -$1,122.61 for end-of-period payments. The difference reflects the earlier payment timing.
PMT with Balloon Payment
This example demonstrates using PMT with a balloon payment (remaining balance) at loan term end.
| A | B |
|---|---|
| Rate | 5.25% |
| Term | 5 |
| Amount | 50000 |
| Balloon | 10000 |
| Payment | =PMT(B1/12, B2*12, B3, -B4) |
The table shows a $50,000 car loan at 5.25% for 5 years with $10,000 balloon payment. The PMT formula calculates monthly payments for this structure.
=PMT(B1/12, B2*12, B3, -B4)
This formula calculates monthly payments for a loan with $10,000 remaining balance. The result is -$803.19, lower than a standard loan's -$949.30 due to the balloon payment reducing the amortized amount.
PMT with Different Compounding Periods
This example shows how to adjust PMT calculations when interest compounds differently than payment frequency.
=PMT((1+6%/4)^(4/12)-1, 36, 15000)
This formula calculates monthly payments for a $15,000 loan at 6% annual interest compounded quarterly for 3 years. We first convert the quarterly rate to an effective monthly rate. The result is -$456.13 per month.
PMT with Zero Interest Rate
When the interest rate is 0, Excel spreads the amount evenly across the periods
instead of failing, so PMT still works for interest-free loans.
=PMT(0, 12, 1200)
This formula splits a $1,200 loan into 12 equal monthly payments with no interest. The result is -$100.00, exactly 1200 / 12.
Splitting Payments with IPMT and PPMT
Every payment produced by PMT contains two parts: interest and
principal. The companion functions IPMT and PPMT
return each part for a given period. Both take the same arguments as
PMT, plus a per argument that selects the period.
| Function | Returns | Syntax |
|---|---|---|
| IPMT | Interest portion of a payment | =IPMT(rate, per, nper, pv, [fv], [type]) |
| PPMT | Principal portion of a payment | =PPMT(rate, per, nper, pv, [fv], [type]) |
=IPMT(5%/12, 1, 60, 20000) ' -83.33 interest =PPMT(5%/12, 1, 60, 20000) ' -294.09 principal
For the first month of the $20,000 loan, $83.33 goes to interest and $294.09
reduces the principal. The two values always add up to the PMT
result of -$377.42. Interest dominates the early payments and shrinks over the
life of the loan, which is why the principal portion grows each period.
PMT Errors and Troubleshooting
Most PMT problems come from mismatched time units or invalid
arguments.
| Cause | Symptom | Fix |
|---|---|---|
| nper is 0 | #NUM! error | Use at least one payment period |
| rate is below -100% | #NUM! error | Use a realistic interest rate |
| An argument is text | #VALUE! error | Make sure the referenced cells contain numbers |
| rate and nper use different units | Payment is far too high or too low | Match units, for example 6%/12 with 10*12 |
| pv and fv have the same sign | Unexpected sign or amount | Enter inflows and outflows with opposite signs |
| type is a value other than 0 or 1 | Payment treated as beginning-of-period | Use exactly 0 or 1 |
A payment that looks wrong is usually a unit mismatch: using a 6% annual rate
directly with a monthly nper produces a payment more than ten
times too small. Always convert the rate to the same period as
nper before calling PMT.
The PMT function is essential for financial calculations in Excel.
From simple loans to complex financial structures, PMT provides accurate
payment calculations. Mastering its parameters enables precise financial
planning. Remember to match rate and period units and consider payment timing
for accurate results.
Author
List all Excel Formulas.