Free and instant — plug in your own numbers.
To calculate a fixed monthly loan payment, you need three numbers: the amount you borrow, the monthly interest rate, and the number of payments. Plug them into the standard amortization formula and you get one payment that stays the same every month and brings the balance to exactly zero at the end.
For example, $15,000 borrowed at an illustrative 9% a year over 4 years works out to $373.28 a month. Below you will see how to get that number with a phone calculator, how to get it in one Excel formula, and how to check that the answer is right.
The loan payment formula
Almost every personal loan, car loan and fixed-rate mortgage uses the same formula:
Here is what each letter means:
- M is the monthly payment you are solving for.
- P is the principal, the amount you borrow (after any down payment or trade-in).
- r is the monthly interest rate as a decimal. Take the annual rate, divide by 100, then divide by 12. So 9% becomes 0.09 ÷ 12 = 0.0075.
- n is the total number of monthly payments. A 4-year loan has 48.
The formula works for any loan that is amortizing, which means each payment covers that month's interest first and the rest reduces the balance. If you want the full story of why it works, our guide to how loan interest is calculated walks through it month by month.
Worked example: $15,000 at 9% over 4 years
You only need a calculator with a power key (usually labeled x^y or ^). Most phone calculators show it when you turn the phone sideways.
Step 1: Find the monthly rate
Step 2: Raise (1 + r) to the power of n
Keep at least six decimal places here. Rounding this number early is the most common reason a hand calculation ends up a few dollars off.
Step 3: Build the top and bottom of the fraction
Step 4: Divide
That is your monthly payment. Multiply by 48 and you repay about $17,917 in total, of which $2,917 is interest. Lenders often adjust the final payment by a few cents to clean up rounding, so a lender's schedule may differ from your total by a cent or two.
Key idea The formula gives you the payment, but the payment is not the cost. Always multiply by the number of payments and subtract the amount borrowed to see the total interest.
Check your answer with the first few payments
A quick way to confirm the payment is right is to split it into interest and principal. Interest each month is the current balance times the monthly rate. The rest of the payment lowers the balance.
| Month | Payment | Interest | Principal | Balance after |
|---|---|---|---|---|
| 1 | $373.28 | $112.50 | $260.78 | $14,739.22 |
| 2 | $373.28 | $110.54 | $262.74 | $14,476.48 |
| 3 | $373.28 | $108.57 | $264.71 | $14,211.77 |
| 12 | $373.28 | $90.16 | $283.12 | $11,738.27 |
| 24 | $373.28 | $63.60 | $309.68 | $8,170.58 |
| 47 | $373.28 | $5.54 | $367.74 | $370.26 |
| 48 | $373.04 | $2.78 | $370.26 | $0.00 |
In month 1, interest is $15,000 × 0.0075 = $112.50, so $260.78 goes to principal. If your hand-calculated payment were too low, the balance would not reach zero in month 48. If it were too high, you would hit zero early. Landing on $0.00 at the last payment is your proof.
How to calculate a loan payment in Excel
Excel and Google Sheets both have a built-in function, so you can skip the algebra:
- rate is the rate per period, so divide the annual rate by 12 for monthly payments.
- nper is the number of payments.
- pv is the present value, the amount borrowed.
Excel treats money you receive and money you pay as opposite signs. If you enter the loan as a positive 15000, PMT returns −373.28. Putting a minus sign in front of the loan amount, as above, gives you a positive payment, which is easier to read.
Other functions worth knowing
Once you have PMT, a few related functions answer the follow-up questions:
| Question | Formula | Result |
|---|---|---|
| Interest in month 1? | =IPMT(9%/12, 1, 48, -15000) | $112.50 |
| Principal in month 1? | =PPMT(9%/12, 1, 48, -15000) | $260.78 |
| How much can I borrow at $400 a month? | =PV(9%/12, 48, -400) | $16,073.91 |
| How many months at $450 a month? | =NPER(9%/12, -450, 15000) | 38.5 |
| What rate am I paying? | =RATE(48, -373.28, 15000)*12 | 9.00% |
The NPER result of 38.5 means 38 full payments of $450 plus a smaller 39th payment. The RATE function is handy when a dealer gives you only a payment and a term: it tells you the interest rate you are actually being charged.
Common Excel mistakes
- Forgetting to divide by 12. =PMT(9%, 48, -15000) returns $1,371.92, because Excel thinks you are paying 9% every month.
- Typing 9 instead of 9%. =PMT(9/12, 48, -15000) treats the rate as 75% a month and returns $11,250.00.
- Using years instead of months. If the rate is monthly, nper must be in months too.
- Mixing up fees. If a fee is added to the loan, include it in pv. If it is taken out of the money you receive, the payment stays the same but your true cost rises. Our guide to APR vs interest rate explains how to measure that.
How the rate and term change your payment
Once you can calculate the payment, it is easy to see what each lever does. Both tables use the same $15,000 loan.
Same 9% rate, different terms:
| Term | Monthly payment | Total interest | Total repaid |
|---|---|---|---|
| 3 years (36) | $477.00 | $2,172 | $17,172 |
| 4 years (48) | $373.28 | $2,917 | $17,917 |
| 5 years (60) | $311.38 | $3,683 | $18,683 |
| 6 years (72) | $270.38 | $4,468 | $19,468 |
Stretching from 4 years to 6 years cuts the payment by about $103 a month but adds about $1,550 in interest.
Same 4-year term, different rates:
| Annual rate | Monthly payment | Total interest |
|---|---|---|
| 6% | $352.28 | $1,909 |
| 9% | $373.28 | $2,917 |
| 12% | $395.01 | $3,960 |
| 15% | $417.46 | $5,038 |
Each 3-point jump in the rate adds roughly $21 to $22 a month, but more than $1,000 in interest over the life of the loan. That is why a small difference in the quoted rate matters more than it looks on the monthly figure.
When the standard formula does not apply
The formula assumes a fixed rate, equal monthly payments and interest charged on the falling balance. A few loans break those assumptions:
- 0% loans. Divide the amount by the number of payments: $15,000 ÷ 48 = $312.50. The formula itself fails at r = 0 because you would divide by zero.
- Interest-only loans. During the interest-only period, the payment is just P × r. On our example that is $112.50 a month, and the balance does not fall at all.
- Flat-rate loans. Some dealers charge interest on the original amount for the whole term. At a 9% flat rate, that would be $15,000 × 9% × 4 = $5,400 of interest and $425.00 a month, far more than the $373.28 above. See flat rate vs reducing balance for how to convert one to the other.
- Credit cards. There is no fixed term, and interest is usually charged daily on a changing balance. A card's minimum payment is set by its own formula, not this one.
- Variable-rate loans. The formula is correct for the current rate, but the payment is recalculated when the rate changes.
Some loans also charge interest daily rather than monthly. If your payment arrives a few days late or early, the interest portion shifts slightly, but the scheduled payment is still calculated with the formula above.
Frequently asked questions
Is the loan payment formula the same for car loans and mortgages?
Yes. Car loans, personal loans and fixed-rate mortgages all use the same amortization formula. A mortgage payment you see quoted may also include property tax and insurance held in escrow, which sit on top of the formula result.
Why is my lender's payment slightly different from mine?
Usually it is rounding, a fee added to the loan amount, or a first payment date that is more or less than one month after the loan starts. Ask the lender for the exact amount financed and the first payment date, then recalculate.
How do I calculate a payment for biweekly or weekly payments?
Change r and n to match the payment period. For biweekly payments, divide the annual rate by 26 and use 26 payments per year. In Excel, that is =PMT(9%/26, 104, -15000) for a 4-year loan.
Does a bigger down payment change the formula?
No, it changes P. Subtract the down payment from the price first, then run the formula on what you actually borrow.
Next steps
- Write down the amount financed, the annual rate and the number of payments from your loan offer.
- Calculate the payment yourself, by hand or with =PMT, and compare it with the lender's figure.
- Multiply by the number of payments to see the total interest, not just the monthly cost.
- Try a shorter term and a lower rate in the loan calculator to see how much each one saves.
- Shopping for a vehicle? The car loan calculator adds the down payment and trade-in for you.
If you expect to have spare cash later, it is also worth reading whether it pays to pay off a loan early. You can model extra payments in the loan calculator before you commit.
This article is for general education and is not financial advice. Rules, rates and products vary by lender and country. See our disclaimer.
