Excel guide · 2 min read

How to calculate a loan payment in Excel

Q: What is the PMT formula in Excel?

=PMT(rate, nper, pv, [fv], [type]). For a monthly loan payment use =PMT(annual_rate/12, years*12, -loan_amount).

Q: How do I calculate a payment with a balloon at the end?

Use the fv argument for the amount left owing: =PMT(rate/12,n,-amount,balloon). A positive balloon lowers the regular payment.

Quick answerUse =PMT(rate/12, years*12, -amount). For a 25,000 loan at 6% over 5 years: =PMT(6%/12,60,-25000) returns about 483.32 per month. Divide the annual rate by 12 and multiply the years by 12 for monthly payments.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

	A	B
1	Input	Value
2	Loan amount	25000
3	Annual rate	0.06
4	Years	5
5	Mortgage	300000
6	Mortgage rate	0.065
7	Mortgage years	30

The PMT formula

PMT(rate, nper, pv) needs the interest rate per period, the number of periods, and the loan amount (present value). For monthly payments, that means the annual rate divided by 12 and the term in years times 12.

Keep the inputs in cells like this so you can try different rates and terms. Format the result as Currency.

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

Result: 483.32

Returns 483.32, the monthly payment on 25,000 at 6% for 5 years.

Why PMT returns a negative number

Excel's finance functions use cash-flow signs: money you receive is positive, money you pay out is negative. If you enter the loan as a positive 25,000 (you receive it), the payment comes back negative (you pay it). Putting a minus sign in front of the amount, as above, or in front of PMT, flips it to a positive payment.

=PMT(B3/12,B4*12,B2)

Result: -483.32003824

Returns about -483.32: the same payment shown as money going out.

Mortgage payment

The same formula works for a 30-year mortgage. Only the inputs change: 360 monthly payments at 6.5%/12 per month.

=ROUND(PMT(B6/12,B7*12,-B5),2)

Result: 1896.2 shows as 1,896.20 with Number format

Returns 1,896.20 per month for a 300,000 mortgage at 6.5%.

Total paid and total interest

Multiply the payment by the number of payments to get everything you pay over the loan. Subtract the amount borrowed to see the interest. This is the number that shows how much a longer term really costs.

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

Result: 3999.2

Returns 3,999.20 of interest over the 5-year loan.

How much can I borrow?

PV works backwards from a payment you can afford. =PV(rate/12, years*12, -payment) gives the loan amount that payment supports. For 400 a month at 6% over 5 years that is about 20,690.

=ROUND(PV(B3/12,B4*12,-400),2)

Result: 20690.22

Returns about 20,690.22, the loan a 400 monthly payment covers.

Common mistakes

Using the annual rate with monthly periods

=PMT(6%,60,-25000) charges 6% every month and gives a huge payment. Divide the rate by 12 when paying monthly.

Entering the term in years

nper is the number of payments, not years. A 5-year monthly loan has 60 periods: years*12.

Typing 6 instead of 6%

A rate of 6 means 600%. Type 6% or 0.06.

FAQ

What is the PMT formula in Excel?

=PMT(rate, nper, pv, [fv], [type]). For a monthly loan payment use =PMT(annual_rate/12, years*12, -loan_amount).

How do I calculate interest and principal for each payment?

Use IPMT for the interest part and PPMT for the principal part of a given payment number, such as =IPMT(6%/12,1,60,-25000) for the first month's interest.

Does PMT include taxes and insurance?

No. PMT covers principal and interest only. Add property tax, insurance and fees separately for a full mortgage payment.

How do I calculate a payment with a balloon at the end?

Use the fv argument for the amount left owing: =PMT(rate/12,n,-amount,balloon). A positive balloon lowers the regular payment.

Functions used

Related guides

Updated October 31, 2026