Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate a monthly loan payment in Excel and Google Sheets
Topic:Finance basicsExcel & 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
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.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.