Skip to content

Excel function · Financial

Excel PMT function

PMT works out the equal amount paid each period on a fixed-rate loan or savings plan: the monthly payment on a 25-year mortgage, or how much to put aside each month to reach a goal in 3 years.

In Turkish Excel: DEVRESEL_ÖDEME

What it does

The function needs three things: the interest rate per period, the total number of periods and the present value (the loan amount). If you pay monthly, the rate must be monthly too: for 6% a year use 0.06/12; for a 25-year loan the number of periods is 25*12, or 300. Forgetting to divide the annual rate by 12 makes the payment many times too large.

Excel's financial functions track the direction of money with its sign: money you receive is positive, money you pay out is negative. When you take out a loan the amount comes to you (positive) and the payments go out, so =PMT(0.06/12,300,300000) returns a negative number. To see the payment as a positive number, put a minus sign in front of the formula. In a savings plan the target amount is positive and the monthly deposit comes back negative.

IPMT and PPMT split each payment into interest and principal. To go from a payment to a loan amount use PV, to find the term use NPER, and to find the rate use RATE. A lender's quoted payment can differ from this result because of fees, insurance and taxes.

Syntax

English Excel=PMT(rate, nper, pv, [fv], [type])
Turkish Excel=DEVRESEL_ÖDEME(oran; dönem_sayısı; bd; [gd]; [tür])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
rateRequiredThe interest rate per period. For monthly payments, the annual rate divided by 12: 0.06/12 or C2/12.
nperRequiredThe total number of payments. For a 25-year loan paid monthly, 25*12, or 300.
pvRequiredThe present value: the amount the loan gives you (money in, positive). For a savings plan that starts from nothing, 0.
fvOptionalThe amount to reach after the last payment. Leave it out (0) for a loan; for a savings plan, enter the target as a positive number.
typeOptionalWhen payments are made: 0 or omitted at the end of each period (usual for loans), 1 at the start.

Examples

1Monthly payment on a mortgage and a car loan

Column E divides the annual rate by 12, multiplies the term by 12 and gives the loan amount as a positive number, so the payment comes back negative. Column F writes the total repaid as a positive number by flipping the sign. The rates are assumptions.

E2fx=PMT(C2/12,D2*12,B2)
Monthly payment on a mortgage and a car loan: example table
ABCDEF
1LoanAmountAnnual RateTerm (Years)Monthly PaymentTotal Repaid
2Mortgage (25 yrs)300,000.006.00%25-1,932.90579,871.26
3Mortgage (30 yrs)300,000.006.00%30-1,798.65647,514.57
4Car loan25,000.007.50%5-500.9530,056.92
  • E2 (filled down to E4)=PMT(C2/12,D2*12,B2)
  • F2 (filled down to F4)=-E2*D2*12

The 25-year mortgage costs -1,932.90 a month; the minus sign shows the money leaves you. Stretching the term to 30 years lowers the payment (-1,798.65) but raises the total repaid (647,514.57).

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2How much to save each month to reach a goal

There are no savings at the start, so pv is 0 and the target goes in fv as a positive number. The monthly deposit leaves your pocket, so the result is negative. The returns are assumptions.

E2fx=PMT(C2/12,D2,0,B2)
How much to save each month to reach a goal: example table
ABCDE
1GoalTargetAnnual ReturnMonthsMonthly Deposit
2House deposit40,000.004.50%36-1,039.88
3Holiday5,000.004.50%12-408.14
4University60,000.004.50%60-893.58
  • E2 (filled down to E4)=PMT(C2/12,D2,0,B2)

Saving a house deposit in 36 months takes -1,039.88 a month. Because returns do part of the work, the total paid in is less than the target. The holiday goal needs -408.14 a month.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

Payment far too large

Cause: The annual rate was not divided by 12, so Excel applied the full annual rate to every month.

Fix: Put the rate in the same unit as the periods: 0.06/12.

Negative result

Cause: The loan amount was entered as a positive number, so the payment comes back negative. That is not an error; it shows the money leaving you.

Fix: To see it as positive, use =-PMT(…) or enter pv as a negative number.

#NUM!

Cause: nper is 0, so there are no periods to spread the payment over.

Fix: Make sure the number of periods is at least 1 and the term cell is not empty.

Good to know

  • Keep the rate and the term in their own cells; the formula becomes =PMT(C2/12,D2*12,B2) and trying different scenarios gets easy.
  • The total repaid is the payment times the number of periods; the total interest is that minus the loan amount. Because the payment is negative, put a minus sign in front of the product.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add monthly payment and total repaid columns to the loan table based on the amount, annual rate and term

Convert to table

Bring your loan table into the tool and type this. Nexell adds both columns with real Excel formulas and shows them in the preview; in the downloaded file, the payment changes when the rate or term does.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

How do I calculate a loan payment in Excel?

Use =PMT(annual_rate/12,years*12,loan_amount). For example, for a 25-year 300,000 mortgage at 6% a year, =PMT(0.06/12,300,300000). The result is negative because the payment is money you pay out.

Why is the PMT result negative?

In Excel, the loan amount is money you receive, so it is positive, and the payment is money you pay, so it is negative. To show it as positive, put a minus sign in front of the formula: =-PMT(0.06/12,300,300000).

Related functions

All financial functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free