Loan Payments With PMT

When you borrow money, you usually pay it back in equal installments. The PMT function tells you exactly how big each of those payments must be.

You give PMT three core pieces of information: the interest rate per period, the number of payments, and the amount you borrowed (the present value). PMT does the math and returns one steady payment.

This is the engine behind every loan, mortgage, and car-finance calculator you have ever seen.

The full signature is =PMT(rate, nper, pv, [fv], [type]).

• rate — the interest rate for one period.
• nper — the total number of payments.
• pv — the present value, i.e. the loan amount.
• fv — optional ending balance, defaults to 0.
• type — optional, 0 for end-of-period payments, 1 for start.

Only the first three arguments are required for a normal loan.

The single most common PMT mistake is mixing a yearly rate with monthly payments. The rate and nper must use the same time period.

If a loan has a 6% annual rate paid monthly, divide the rate by 12 and multiply the years by 12. So a 5-year loan becomes rate/12 and 5*12 periods.

Keep both in months, or both in years. Never mix.

You borrow 20,000 at 6% annual interest over 5 years, paid monthly.

The rate per month is 0.06/12 = 0.005. The number of payments is 5 × 12 = 60. The present value is 20000.

The formula returns about -386.66. The result is negative because it is money leaving your pocket each month.

Financial functions follow a cash-flow sign convention. Money you receive is positive, money you pay out is negative.

Because you borrowed a positive amount (cash came in), the payments flow out and come back negative. This is correct, not a bug.

If you prefer a positive number for display, wrap the loan amount in a minus sign or wrap the whole formula in ABS.

In real workbooks you rarely hard-code numbers. Put your inputs in cells and reference them.

Suppose B1 holds the annual rate, B2 the years, and B3 the loan amount. Your formula becomes clean and reusable.

Now you can change any input and the payment updates instantly — the whole point of a spreadsheet.

If you make a down payment, you only finance the remainder. Subtract the down payment from the price before passing it to PMT.

Say a car costs 25,000 and you put 5,000 down. You finance 20,000, so the present value is the difference.

You can compute that inline so the formula stays in one place.

One payment is useful, but borrowers care about the total they will repay. Multiply the payment by the number of periods.

For the 20,000 loan at 6% over 5 years, 60 payments of about 386.66 sum to roughly 23,200.

The difference between that total and the 20,000 borrowed is the interest you paid — about 3,200.

The optional type argument controls when payments occur. Use 0 (the default) for payments at the end of each period, and 1 for the beginning.

Paying at the start of each period reduces interest slightly because the balance drops sooner. Leases often use type 1.

You must still supply the fv argument (often 0) before type.

The fv argument lets you aim for a leftover balance. A balloon loan, for example, finishes with a large remaining amount.

If you want 5,000 still owed at the end, set fv to -5000 (it is an outflow you have not yet paid).

PMT then sizes the regular payments so that only the non-balloon portion is amortized.

Good news: PMT behaves identically in Google Sheets and Excel. The arguments, order, and sign convention are all the same.

This makes loan models portable. A spreadsheet you build in Excel will calculate the same payment when opened in Sheets.

The sibling functions IPMT and PPMT (interest and principal portions of a single payment) also exist in both apps if you need a full amortization schedule.

Test your understanding of how PMT periods work.

You learned to size loan payments with =PMT(rate, nper, pv).

• Match the rate and nper to the same period — divide by 12 and multiply years by 12 for monthly loans.
• Results are negative due to the cash-flow sign convention; wrap in ABS for display.
• Optional fv targets a leftover balance and type sets start vs end of period.
• Multiply the payment by nper to see the total repaid.

PMT works the same in Excel and Google Sheets.