PMT Function in Excel, Google Sheets, Airtable & Notion
PMT returns the fixed payment per period that pays off a loan (or reaches a savings target) at a constant interest rate. Payments come back negative because they are money going out.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | PMT(rate, nper, pv, [fv], [type]) |
| Google Sheets | ✔ Built in | PMT(rate, number_of_periods, present_value, [future_value], [end_or_beginning]) |
| Airtable | ≈ Workaround | -pv * rate / (1 - POWER(1 + rate, -nper))Airtable has no financial functions; this is the standard loan-payment formula with Excel's sign. Remove the leading minus for a positive payment. |
| Notion | ≈ Workaround | -pv * rate / (1 - pow(1 + rate, -nper))Notion has no pmt(); use the closed-form formula. If the rate can be 0, guard it: if(rate == 0, -pv / nper, …). |
| Excel | =PMT(6%/12, 30*12, 250000) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =PMT(6%/12, 30*12, 250000) | open sheets.new ↗ |
| Airtable workaround | -250000 * 0.06/12 / (1 - POWER(1 + 0.06/12, -30*12)) | |
| Notion workaround | -250000 * 0.06/12 / (1 - pow(1 + 0.06/12, -30*12)) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
The annual rate is divided by the number of payments per year and the years are multiplied by it, so rate and nper are both per period. The result is negative because, from the borrower's side, payments are money going out.
Arguments
| Argument | What it means |
|---|---|
rate | Interest rate per period. For monthly payments on a 6% annual rate, use 6%/12. |
nper | Total number of payments, e.g. 30*12 for 30 years of monthly payments. |
pv | Present value: the loan amount (positive when you receive the money). |
[fv] optional | Balance you want left after the last payment. Defaults to 0 (loan fully repaid). |
[type] optional | 0 (default) = payments at the end of each period, 1 = at the beginning. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=PMT(6%/12, 30*12, 250000) | -1498.88 | Monthly payment on a $250,000, 30-year mortgage at 6%. | |
=PMT(5%, 10, 10000) | -1295.05 | Yearly payments: no conversion needed. | |
=PMT(4%/12, 5*12, 0, 50000) | -754.16 | Monthly saving needed to reach $50,000 in 5 years. | |
=PMT(0, 12, 1200) | -100 | At 0% it is simply the amount divided by the number of payments. |
Examples use Excel / Google Sheets syntax unless noted.
How PMT works
PMT works in periods, not years. The rate must be the rate per payment period and nper the total number of payments, so a 30-year mortgage at 6% paid monthly is PMT(6%/12, 30*12, 250000). Passing 6% and 30 instead gives the yearly payment, and mixing the two (annual rate with monthly periods) is the most common reason a PMT result looks wildly wrong.
Excel and Google Sheets use a cash-flow sign convention: money you receive is positive and money you pay is negative. The loan amount (pv) comes in to you, so the payment comes back negative. Put a minus sign in front (=-PMT(…)) or enter pv as a negative number if you prefer to display a positive payment. The result covers principal and interest only; taxes, insurance and fees are extra.
Airtable and Notion have no financial functions, but the payment has a closed form: pv × r / (1 − (1 + r)−n). The copy buttons build it with Excel's sign so all four apps agree; in practice you would use fields, e.g. -{Loan} * {Rate}/12 / (1 - POWER(1 + {Rate}/12, -{Years}*12)) in Airtable. That formula divides by zero when the rate is 0, so guard it with an IF if a 0% rate is possible.
Frequently Asked Questions
- Why is my PMT result negative?
- Payments are money leaving your account, and Excel and Google Sheets show outgoing cash as negative. Use
=-PMT(…)or=ABS(PMT(…))to display it as a positive number. - How do I get the total interest paid?
- Multiply the payment by the number of payments and add the loan amount:
=PMT(6%/12, 360, 250000)*360 + 250000gives the (negative) total interest.