What Is the PMT Function?
PMT calculates the fixed periodic payment required to pay off a loan or reach a savings goal, given a constant interest rate and a set number of periods. Whether you are figuring out monthly mortgage payments, car loan installments, or how much to save each month for a down payment, PMT gives you the answer in a single formula. The result is negative by convention because it represents money going out.
Syntax
=PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning])
| Parameter | Description |
|---|---|
| rate | The interest rate per period. For monthly payments with an annual rate of 6%, use 6%/12 or 0.06/12. |
| number_of_periods | Total number of payment periods. A 30-year mortgage with monthly payments is 30*12 = 360. |
| present_value | The current loan amount (principal) or the current value of an investment. |
| future_value | Optional. The balance you want after the last payment. Defaults to 0 (fully paid off). |
| end_or_beginning | Optional. 0 for payments at end of period (default), 1 for payments at beginning of period. |
Basic Examples
Monthly Mortgage Payment
You are buying a home with a $350,000 loan at 6.5% annual interest for 30 years:
=PMT(6.5%/12, 30*12, 350000)
Result: -$2,212.24. The negative sign means this is money you pay. Your monthly mortgage payment (principal + interest) would be approximately $2,212.
Car Loan Payment
A $28,000 car loan at 4.9% annual interest over 5 years:
=PMT(4.9%/12, 5*12, 28000)
Result: -$527.18 per month.
Comparing Loan Terms Side by Side
| A | B | C | D (Formula) | D (Result) | |
|---|---|---|---|---|---|
| 1 | Loan Amount | Rate | Years | Monthly Payment | Monthly Payment |
| 2 | 250000 | 6.0% | 15 | =PMT(B2/12, C2*12, A2) | -$2,109.64 |
| 3 | 250000 | 6.0% | 30 | =PMT(B3/12, C3*12, A3) | -$1,498.88 |
The 30-year term has lower monthly payments but you pay significantly more in total interest over the life of the loan.
Advanced Examples
Saving for a Goal (Future Value)
How much do you need to save monthly to accumulate $50,000 in 5 years, assuming 4% annual return?
=PMT(4%/12, 5*12, 0, 50000)
Result: -$754.85. You would need to set aside about $755 per month. Notice that present_value is 0 (you are starting from nothing) and future_value is 50,000 (your goal).
Building a Loan Amortization Table
PMT gives you the total payment, but you can break it into interest and principal components for each period:
| A | B (Formula) | C (Formula) | D (Formula) | |
|---|---|---|---|---|
| 1 | Period | Payment | Interest | Principal |
| 2 | 1 | =PMT(6%/12,360,300000) | =300000*6%/12 | =B2-C2 |
| 3 | 2 | =PMT(6%/12,360,300000) | =(300000+D2)*6%/12 | =B3-C3 |
For a proper amortization schedule, use the IPMT and PPMT functions to calculate the interest and principal portions directly:
=IPMT(6%/12, A2, 360, 300000) // Interest portion for period A2
=PPMT(6%/12, A2, 360, 300000) // Principal portion for period A2
This breaks down exactly how much of each payment goes toward interest versus reducing the balance.
Payments at the Beginning of the Period
Lease payments are typically due at the start of each period. Set the last argument to 1:
=PMT(5%/12, 3*12, 25000, 0, 1)
Result: -$746.33. This is slightly less than end-of-period payments because each payment earns one extra period of interest reduction.
Common Mistakes
- Using the annual rate instead of the periodic rate. This is the single most common PMT mistake. If you enter 6% as the rate for monthly payments, the formula assumes 6% per month (72% per year). Always divide the annual rate by 12 for monthly payments:
6%/12. - Forgetting to match periods. The rate, number_of_periods, and payment frequency must all use the same time unit. For monthly payments: divide the annual rate by 12 and multiply years by 12. For quarterly: divide by 4 and multiply by 4.
- Being confused by the negative result. PMT returns a negative number because it represents a cash outflow. If you prefer a positive number, negate the formula:
=-PMT(...). Just remember that the sign convention is intentional and consistent with other financial functions.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.