Loan Payment Calculator

This is a fixed-rate amortizing loan: the payment stays the same, and it covers principal and interest only. Taxes, insurance and fees are not included. Numbers stay in your browser.

Quick answer: for a monthly payment, with the principal in B2, the annual rate in C2 (as 6.5% or 0.065) and the term in years in D2, Excel and Google Sheets use:

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

The third argument is the present value. Putting a negative principal in front of the loan amount makes the payment come out positive, which is usually what you want in a budget. Leave the format as currency, with two decimal places.

A $250,000 loan at 6.5% for 30 years

The default numbers in the calculator are a 250,000 principal, 6.5% a year, 30 years, monthly payments at the end of each month. In Google Sheets on 12 November 2026:

=PMT(0.065/12, 30*12, -250000)

returned 1,580.1700587324133, which is a payment of $1,580.17 once you round to the cent. Over 360 payments, the total paid is $568,861.22 and the total interest is $318,861.22 (the payment times 360, minus the 250,000 principal). Those totals use the unrounded payment, which is how PMT itself works; a bank that rounds each installment to the nearest cent can be a few cents off over the life of the loan.

I also checked a zero-rate case: 12,000 repaid over two years in 24 monthly payments is exactly $500, with no interest. If the rate in C2 might be zero, guard the formula:

=IF(C2=0, B2/(D2*12), PMT(C2/12, D2*12, -B2))

Payment at the start of the period

PMT has two optional arguments: future value (usually 0) and type. type is 0 when the payment is due at the end of each period, which is the usual loan, and 1 when it is due at the beginning. The same 80,000 loan at 5.25% for 15 years was $643.10 at the end of the month and $640.30 at the beginning, in the Sheets check:

=PMT(0.0525/12, 15*12, -80000, 0, 0)
=PMT(0.0525/12, 15*12, -80000, 0, 1)

Those returned 643.1021723560746 and 640.3008561105906, or $643.10 與 $640.30 rounded to the cent.

Weekly, fortnightly and other schedules

Divide the annual rate by the number of payments per year, and multiply the years by the same figure. Weekly for three years is 52 × 3 = 156 periods. A 10,000 loan at 9% a year, paid weekly for three years, returned $73.20048299868967 in Sheets, about $73.20 a week. Using 52 weeks is a convention; a calendar year is not exactly 52 weeks, and lenders do not all split the nominal rate the same way, so treat this as the standard spreadsheet model, not a particular bank’s disclosure.

Mistakes that throw the payment off

Typing 6.5 in C2 when the cell is a plain number. PMT then treats the rate as 650% a year. Enter 6.5% 或 0.065, or divide a whole number by 100 in the formula: =PMT((C2/100)/12, D2*12, -B2).
Forgetting the minus sign on the principal, then wondering why the payment is negative.
Using the annual rate without dividing by 12, which prices the loan as if you paid 6.5% each month.
Reading the result as a “payment including escrow.” This model is principal and interest only.

Questions that come up

What is the Excel or Google Sheets formula for a monthly loan payment?

With the principal in B2, the annual rate in C2 and the term in years in D2, use =PMT(C2/12,D2*12,-B2). Format the result as currency. The rate in C2 should be 6.5% or 0.065, not 6.5.

Does PMT include taxes and insurance?

No. PMT is principal and interest only, on a fixed-rate loan with a constant payment. Property tax, insurance or fees have to be added separately.

What if the interest rate is zero?

The payment is just the principal divided by the number of payments. The calculator handles that case; in a sheet you can wrap PMT with IF(C2=0,B2/(D2*12),PMT(...)).