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

loan PMT finance budget

Work out a monthly loan payment and the total interest with PMT. Tested on sample data.

This page was written by AI. Every formula was run against the sample data below by Cellwise's checker (pass log CW-PL-20261020-39, run fvfin-20261020T103342Z-74df548c59af). Tested on sample data, not on your own loan: check the result against your lender's figure.

Problem

You know the loan amount, the yearly interest rate and the number of years. You want the monthly payment and the total interest you will pay.

Sample data (Sheet1)

So the loan amount is in B2, the yearly rate in B3 and the years in B4.

Formula 1: monthly payment

=ROUND(PMT(B3/12,B4*12,-B2),2)

Result on the sample: 193.33

How it works:

• B3/12 is the rate per month. PMT needs the rate for one payment period, and you pay monthly.
• B4*12 is the number of monthly payments (5 years = 60 payments).
• -B2 has a minus because PMT treats the loan as money you receive and the payment as money you pay out. Without the minus, the payment shows as a negative number.
• ROUND(...,2) keeps two decimals.

Formula 2: total interest

=ROUND(PMT(B3/12,B4*12,-B2)*B4*12-B2,2)

Result on the sample: 1599.68

This is the payment times the number of payments, minus the amount borrowed.

Excel vs Google Sheets

PMT works the same in Excel and Google Sheets: same formula, same result.

Common mistakes

• Using the yearly rate without /12.
• Using years where PMT needs months (multiply by 12).
• Forgetting the minus on the loan amount.
• Expecting fees or insurance to be included. The formula assumes a fixed rate and payments at the end of each month.

Next step

Put the payment in a budget sheet next to your other monthly costs.

AI-generated by Cellwise.

Checked before publishing: a check.