How to Calculate Loan Payment in Excel Using the PMT Function

shape
shape
shape
shape
shape
shape
shape
shape
How to Calculate Loan Payment in Excel Using the PMT Function - featured image

The PMT function is the quickest way to find the regular payment on a loan in Excel. You give it the interest rate per period, the number of payments and the amount borrowed, and it returns the payment. It works for car loans, mortgages, personal loans and any loan with equal payments. This guide explains the syntax, shows worked examples for monthly and yearly payments, builds a simple amortization schedule, compares different loan terms, shows how to find the total interest, and explains the sign and rate mistakes that give wrong answers. The examples use round numbers so you can check each result by hand.

Updated November 2026

In this guide

  1. The PMT function syntax
  2. Worked example: a monthly car loan
  3. Worked example: a home loan
  4. Compare loan terms with a table
  5. Yearly, quarterly and biweekly payments
  6. Find the total interest with CUMIPMT or simple math
  7. Build a simple amortization schedule
  8. Use IPMT and PPMT for each payment
  9. What if the payment is fixed and you want the term or rate
  10. Extra payments and early payoff
  11. How much can I borrow for a given payment
  12. Formatting the results for a client
  13. Common mistakes
  14. Practice problems

The PMT function syntax

The syntax is =PMT(rate, nper, pv, [fv], [type]). Rate is the interest rate for each period, nper is the total number of payments, and pv is the present value, which is the amount you borrow. The optional fv is the balance you want after the last payment, which is zero for a normal loan. The optional type is 0 for payments at the end of each period and 1 for the start. Microsoft describes the PMT function in its support documentation. The most important rule is that rate and nper must use the same time unit.

Worked example: a monthly car loan

You borrow 20,000 for 5 years at an annual rate of 6 percent, paid monthly. The monthly rate is 6 percent divided by 12, which is 0.5 percent. The number of payments is 5 times 12, which is 60. Enter =PMT(6%/12,5*12,20000). Excel returns about −386.66. The negative sign shows money going out. To show a positive payment, put a minus sign before the pv: =PMT(6%/12,60,-20000) returns 386.66. Over 60 payments you pay about 23,199.60 in total, so the interest is about 3,199.60.

Worked example: a home loan

A 200,000 mortgage at 7 percent for 30 years has a monthly rate of 7 percent divided by 12, or about 0.5833 percent, and 360 payments. Enter =PMT(7%/12,360,-200000). The result is about 1,330.60. Over 360 payments you pay about 479,016, so the interest is nearly 279,000, which is more than the amount borrowed. This shows why even a small change in the rate or term has a large effect, and it is the reason borrowers compare several loan terms before signing.

Compare loan terms with a table

Build a small table with the term in years down column A, and use PMT in column B. For a 20,000 loan at 6 percent, the monthly payments are shown below, rounded to cents.

TermMonthly paymentTotal paidTotal interest
3 years608.4421,903.841,903.84
4 years469.7022,545.602,545.60
5 years386.6623,199.603,199.60
7 years292.1724,542.284,542.28

A longer term lowers the monthly payment but increases the total interest. For the table, the formula for total paid is the payment times the number of payments, and total interest is the total paid minus the amount borrowed.

Yearly, quarterly and biweekly payments

Match the rate and nper to the payment frequency. For quarterly payments use the annual rate divided by 4 and the years times 4. For yearly payments use the annual rate and the number of years. For example, 10,000 at 8 percent over 4 years with yearly payments is =PMT(8%,4,-10000), which returns about 3,019.21. For biweekly payments, divide the annual rate by 26 and multiply the years by 26. Always check the loan contract, because some lenders calculate interest daily or use a slightly different method, so your result may differ from the bank by a few cents.

Find the total interest with CUMIPMT or simple math

The simplest method is to multiply the payment by the number of payments and subtract the amount borrowed. In a worksheet, with the payment in B2, use =B2*60-20000. Excel also has the CUMIPMT function, which adds up the interest paid between two periods, and CUMPRINC, which does the same for the principal. Use the simple method when you want the whole loan and CUMIPMT when you want a specific year, such as the interest in year 1 of a mortgage, for a tax report.

Build a simple amortization schedule

An amortization schedule shows how each payment splits between interest and principal. Create columns for Period, Payment, Interest, Principal and Balance. In the first row, the balance is the loan amount. For each period, interest is the previous balance times the monthly rate, principal is the payment minus the interest, and the new balance is the previous balance minus the principal. For the 20,000 loan, the first month interest is 20,000 times 0.5 percent, which is 100. The payment is 386.66, so 286.66 goes to principal, and the new balance is 19,713.34. The next month interest falls to about 98.57. Over time the interest share shrinks and the principal share grows, until the balance reaches zero after 60 payments. Use absolute references such as $B$1 for the rate, so you can fill the formulas down.

Use IPMT and PPMT for each payment

Instead of writing the split by hand, use IPMT and PPMT. =IPMT(rate,per,nper,pv) returns the interest part of payment number per, and =PPMT(rate,per,nper,pv) returns the principal part. For the car loan, =IPMT(6%/12,1,60,-20000) returns 100 and =PPMT(6%/12,1,60,-20000) returns about 286.66. Together they equal the payment. These functions make the schedule faster to build and easier to audit.

What if the payment is fixed and you want the term or rate

You can also work backward. NPER finds how many payments are needed for a given payment, and RATE finds the interest rate when you know the payment, term and amount. For example, =NPER(6%/12,-500,20000) returns about 44.7, which means 45 payments of 500 would repay a 20,000 loan at 6 percent. This is helpful when a client has a budget and wants to know how long the loan will last. Use the ROUNDUP function from our guide to rounding numbers in Excel to turn 44.7 into whole payments.

Extra payments and early payoff

Many borrowers want to know how much an extra payment saves. Using the 20,000 car loan, imagine you pay 50 more each month, so the payment becomes 436.66. Use NPER to see the new term: =NPER(6%/12,-436.66,20000) returns about 52.2 payments, so the loan ends roughly 8 months early and saves several hundred in interest. Check the lender’s rules first, because some loans charge a penalty for early payoff, and some apply extra money to the next payment instead of the principal. In your worksheet, add a cell for the extra amount and build the amortization schedule on the new payment, stopping when the balance reaches zero. A clear comparison of the two schedules is a useful deliverable for a client who is deciding whether to refinance or pay down a loan.

How much can I borrow for a given payment

Sometimes the question is reversed: how much can I borrow if I can pay 400 a month? Use the PV function, which is the mirror of PMT: =PV(6%/12,60,-400) returns about 20,690, so a 400 payment supports a loan of about 20,690 over five years at 6 percent. Build a small table of rates down one side and terms across the top, and use mixed references so that one formula fills the whole grid. This gives a client a quick view of how rate and term change the affordable loan, and it is a common request for car, equipment and small business financing projects.

Formatting the results for a client

Format payment cells as Currency with two decimals, and show the rate as a percentage with two decimals. Label every input cell, such as Loan amount, Annual rate and Years, and use a different fill color for inputs than for formulas. Protect the formula cells so a client does not overwrite them by accident. Add a notes line that says the payment excludes taxes, insurance and fees. Put the amortization schedule on a second sheet and link the inputs from the first. These small steps make a loan calculator look professional and reduce questions after delivery.

Common mistakes

The most common mistake is using the annual rate with monthly periods, which makes the payment far too small. Divide the annual rate by 12 and multiply the years by 12. Another is the sign: if you enter a positive pv, PMT returns a negative payment, which is correct but confusing. A third is mixing the rate as a whole number, such as typing 6 instead of 6 percent or 0.06. A fourth is forgetting that PMT does not include taxes, insurance or fees, so a mortgage payment from a bank will be higher. Finally, round the displayed payment with ROUND when you build an invoice or a schedule, so the final balance ends at exactly zero.

Practice problems

Try these on your own. Problem 1: a 10,000 loan at 12 percent for 1 year, monthly. The rate is 1 percent, nper is 12, and the payment is about 888.49. Problem 2: a 5,000 loan at 0 percent for 10 months. PMT with a zero rate needs a different setup, but the answer is simply 5,000 divided by 10, which is 500. Problem 3: a 150,000 loan at 5 percent for 15 years, monthly. The payment is about 1,186.19. Check each result by multiplying by the number of payments and confirming that the total is larger than the amount borrowed. Use our IF statements guide to handle the zero-rate case in one formula.

Frequently asked questions

How do I calculate a monthly loan payment in Excel?

Use =PMT(annual rate/12, years*12, -loan amount).

Why is my PMT result negative?

PMT shows money paid out as negative. Put a minus sign before the loan amount to get a positive payment.

Does PMT include taxes and insurance?

No. It covers only principal and interest.

How do I find the total interest on a loan?

Multiply the payment by the number of payments and subtract the amount borrowed.

Can PMT handle yearly payments?

Yes. Use the annual rate and the number of years.

Related guides and free tools

Sources: PMT function, Microsoft Support

Leave a Reply

Your email address will not be published. Required fields are marked *