PMT is a spreadsheet function in Excel and Google Sheets that calculates the fixed payment amount for a loan or investment based on a constant interest rate and a set number of payments. If you want to know your monthly mortgage payment, your car loan installment, or how much you need to save each month to hit a financial goal, PMT does the math for you in a single formula.
How the PMT Formula Works
The basic syntax looks like this:
=PMT(rate, nper, pv, fv, type)
It has three required arguments and two optional ones:
- Rate: The interest rate per payment period. If you have an annual rate of 6% and you’re making monthly payments, you’d enter 6%/12 (or 0.06/12) so the rate matches the period.
- Nper: The total number of payments over the life of the loan or investment. For a 30-year mortgage with monthly payments, that’s 30 × 12 = 360.
- Pv: The present value, which is the loan principal or the current lump sum. For a $250,000 mortgage, this is 250000.
- Fv: The future value you want left over after all payments are made. For a standard loan you’re paying off completely, this is 0. If you’re saving toward a goal, this is the target balance. When omitted, it defaults to 0.
- Type: Enter 0 (or leave it blank) if payments are due at the end of each period, which is the standard for most loans. Enter 1 if payments are due at the beginning of each period, which applies to some lease arrangements.
A Loan Payment Example
Say you’re borrowing $20,000 for a car at a 7.2% annual interest rate, repaid over 5 years with monthly payments. Here’s how you’d set it up:
=PMT(7.2%/12, 5*12, 20000)
That divides the 7.2% annual rate by 12 to get the monthly rate, multiplies 5 years by 12 to get 60 total payments, and uses 20,000 as the loan amount. The result is approximately -$398. That negative sign means money is leaving your account each month. Over 60 payments, you’d pay roughly $23,880 total, meaning about $3,880 goes toward interest.
Why the Result Is Negative
PMT follows a cash flow sign convention. Money you receive (like a loan deposited into your account) is positive. Money you pay out is negative. When you enter a positive present value, the function assumes you received that amount, so the payment comes back negative because it’s money flowing out.
If the negative number bothers you in your spreadsheet, you can flip the sign by placing a minus sign before the function: =-PMT(7.2%/12, 60, 20000). This returns a positive number, which is easier to read in a budget or payment schedule.
Matching Rate and Period
The single most common mistake with PMT is mismatching the interest rate and the number of periods. If your payments are monthly, the rate must be a monthly rate and nper must be expressed in months. If your payments are quarterly, divide the annual rate by 4 and multiply years by 4. Plugging in an annual rate of 7.2% alongside 60 monthly periods will give you a wildly wrong answer because the function will treat 7.2% as a monthly rate.
Using PMT for Savings Goals
PMT isn’t just for loans. You can use it to figure out how much to save each month to reach a target balance. Suppose you want $50,000 in an account in 10 years, and you expect to earn 5% annually. You’re starting from zero.
=PMT(5%/12, 10*12, 0, 50000)
Here the present value is 0 (you’re starting with nothing) and the future value is 50,000. The result is approximately -$322 per month. Again, it’s negative because it represents money leaving your pocket and going into the account. To read it as a positive savings target, add a minus sign in front of the function.
Where PMT Works
PMT is available in Excel, Google Sheets, LibreOffice Calc, and most other spreadsheet programs with identical syntax. Financial calculators use the same underlying math, and the variables (rate, n, PV, FV, PMT) appear on most business calculators as dedicated buttons. Once you understand what each argument means in a spreadsheet, you can use any financial calculator the same way.
The function assumes a fixed interest rate and equal payments throughout the life of the loan or savings plan. It won’t account for variable rates, extra payments, or changing contribution amounts. For those scenarios, you’d need to build a full amortization table, but PMT gives you the baseline fixed payment to start from.
Related Functions
Once you’re comfortable with PMT, a few companion functions let you dig deeper into the same calculation:
- IPMT: Returns just the interest portion of a specific payment. Useful for seeing how much of payment number 1 versus payment number 200 goes toward interest.
- PPMT: Returns just the principal portion of a specific payment, showing how much of each installment actually reduces your balance.
- NPER: Calculates how many payments you’d need given a rate, payment amount, and loan balance. Handy if you want to see how quickly you could pay off a loan by increasing your monthly payment.
- RATE: Works backward to find the interest rate when you know the payment, number of periods, and loan amount.
Together, these functions let you model nearly any fixed-rate loan or savings scenario without needing a financial calculator or an online tool.