How to calculate a monthly loan payment in Excel and Google Sheets

Excel & Google Sheets

=PMT(B2/12,C2*12,-A2)

✓

Verified example

Computed by a real spreadsheet engine on the sample data below.

Loan Amount	Annual Rate	Years	Monthly Payment
200000	0.05	30
15000	0.07	5
8000	0.06	3
50000	0.045	10

=PMT(B2/12,C2*12,-A2)→1073.6432460243

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Cell row 1 column 1	Cell row 1 column 2	Cell row 1 column 3	Cell row 1 column 4
Cell row 2 column 1	Cell row 2 column 2	Cell row 2 column 3	Cell row 2 column 4
Cell row 3 column 1	Cell row 3 column 2	Cell row 3 column 3	Cell row 3 column 4
Cell row 4 column 1	Cell row 4 column 2	Cell row 4 column 3	Cell row 4 column 4
Cell row 5 column 1	Cell row 5 column 2	Cell row 5 column 3	Cell row 5 column 4

Formula

Runs server-side · free · no signup

Step by step

1Set up three input cells: Loan Amount (A2), Annual Interest Rate as decimal (B2 — e.g. 0.05 for 5%), Loan Term in Years (C2).
2Enter =PMT(B2/12, C2*12, -A2) in the result cell. B2/12 converts annual rate to monthly; C2*12 converts years to months; negative A2 makes the payment come back positive.
3Format the result as Currency.
4To enter the rate as a percentage like "5%" instead of "0.05", you can either format the input cell as Percent or write =PMT(B2/12/100, C2*12, -A2) if it's a plain number.
5Example: $200,000 loan at 5% over 30 years = $1,073.64/month.

Tips

Always divide the annual rate by 12 for monthly payments — passing the annual rate directly gives a wildly wrong answer.
Use a negative sign on the principal: =PMT(rate,nper,-A2). Otherwise PMT returns a negative number (since it views the payment as money out).
For biweekly payments: =PMT(B2/26, C2*26, -A2). For weekly: divide by 52 and multiply nper by 52.
Add a balloon (residual): =PMT(B2/12, C2*12, -A2, -BalloonAmount). The fourth argument is the future value remaining after the last payment.
Split each payment into principal and interest with PPMT and IPMT: =IPMT(B2/12, period, C2*12, -A2) gives the interest portion for that period; PPMT gives the principal portion.
Wrap in ABS if the sign convention bothers you: =ABS(PMT(B2/12, C2*12, A2)).

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

Why does PMT return a negative number?

PMT signs cash flows from the lender's perspective: money paid out is negative. To get a positive monthly payment, negate the principal: =PMT(rate, nper, -A2). Or wrap with ABS.

What if the loan has a balloon payment at the end?

Add the balloon as the fourth argument (FV): =PMT(B2/12, C2*12, -A2, -BalloonAmount). For a $20,000 balloon: =PMT(B2/12, C2*12, -A2, -20000).

How do I see how much of each payment is interest vs principal?

Use IPMT for interest and PPMT for principal. Both take the same arguments as PMT plus a period number: =IPMT(B2/12, 1, C2*12, -A2) returns the interest portion of payment 1; =PPMT(B2/12, 1, C2*12, -A2) returns the principal portion. They sum to PMT.

Why does my mortgage calculator show a different number?

Mortgage calculators often add property tax, insurance (PITI), and PMI on top of principal and interest. PMT only computes the P&I portion. To match: =PMT(B2/12, C2*12, -A2) + monthly_tax + monthly_insurance + monthly_pmi.

How do I calculate the loan amount I can afford for a target payment?

Use PV (present value): =PV(rate/12, years*12, -monthly_payment). Plug in your max monthly payment, rate, and term to back-solve the principal.

What's the total amount I'll pay over the life of the loan?

Total = monthly payment × total payments: =ABS(PMT(B2/12, C2*12, -A2)) * C2 * 12. Total interest = total minus principal: same expression minus A2.

What rate should I use — APR or interest rate?

Use the nominal interest rate, not APR. APR includes fees that aren't amortized into the monthly payment. The interest rate is what PMT expects for compounding the balance.

Formulas used

PMT IPMT PPMT NPER CUMPRINC FV

Related tasks

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026