Loan payment formula: how to calculate a monthly loan payment
A fixed-rate loan's monthly payment depends on three numbers: the amount you borrow, the interest rate and the number of payments. This page shows the formula, works through a real example by hand and covers the related formulas people look for: how much you can borrow, how much you still owe and how to do it in Excel.
The monthly payment formula
Most car loans, personal loans and mortgages are repaid in equal monthly payments (an amortizing or annuity loan). The payment is:
M = P × r / (1 − (1 + r)^(−n))
M : monthly payment
P : principal, the amount borrowed
r : monthly interest rate = annual rate (APR) / 12
n : number of monthly payments (years × 12)
The most common mistake is using the annual rate directly. A 7% APR becomes r = 0.07 / 12 = 0.0058333 per month.
Worked example: $20,000 at 7% for 5 years
- Monthly rate: r = 0.07 / 12 = 0.0058333
- Number of payments: n = 5 × 12 = 60
- (1 + r)^(−n) = 1.0058333^(−60) = 0.705405
- 1 − 0.705405 = 0.294595
- P × r = 20,000 × 0.0058333 = 116.67
- M = 116.67 / 0.294595 = 396.02
monthly payment = 396.02
total repaid = 396.02 × 60 = 23,761.44
total interest = 23,761.44 − 20,000 = 3,761.44
A lender may show a cent or two of difference because of rounding.
Where each payment goes
Interest for a month is always charged on the balance still owed. In month one that is 20,000 × 0.0058333 = 116.67, so only 396.02 − 116.67 = 279.36 reduces the balance. Each month the balance is a little lower, so the interest part shrinks and the principal part grows, while the payment stays the same. The full month-by-month breakdown is called an amortization schedule.
Loan amount formula: how much can I borrow?
If you know the payment you can afford, turn the formula around to find the principal:
P = M × (1 − (1 + r)^(−n)) / r
Example: 300 a month, 6% APR, 48 months
r = 0.06 / 12 = 0.005
P = 300 × (1 − 1.005^(−48)) / 0.005 = 12,774.10
Remaining balance formula: how much do I still owe?
The balance after k payments can be found without building the whole schedule:
B = P × (1 + r)^k − M × ((1 + r)^k − 1) / r
Example: the $20,000 loan above after 24 payments
B = 20,000 × 1.0058333^24 − 396.02 × (1.0058333^24 − 1) / 0.0058333
B = 12,825.82
Loan payment formula in Excel and Google Sheets
Spreadsheets have the formula built in as PMT. The rate and the number of periods must be monthly, and the loan amount is entered as a negative number so the payment comes out positive.
=PMT(7%/12, 60, -20000) → 396.02 monthly payment
=PV(6%/12, 48, -300) → 12,774.10 loan amount
=PMT(7%/12, 60, -20000)*60-20000 → 3,761.44 total interest
Mortgage example
The same formula works for a fixed-rate mortgage. A 250,000 mortgage at 6.5% over 30 years (360 payments) has a monthly principal and interest payment of 1,580.17, and total interest of 318,861.22 over the full term. Property tax, insurance and fees are not part of this formula and are usually added on top.
APR is not the same as the effective annual rate
Dividing the APR by 12 gives the monthly rate lenders use, but because interest compounds every month, the true yearly cost is slightly higher: (1 + 0.07 / 12)^12 − 1 = 7.23% for a 7% APR. When comparing offers, compare the same kind of rate, and check the total amount repaid.
Why your lender's number can differ
- Fees and insurance may be added to the payment or to the amount borrowed.
- Some loans charge interest daily, so payment dates change the interest slightly.
- Variable-rate loans recalculate the payment when the rate changes.
- Interest-only loans use a different formula: payment = P × r until the principal is due.
Calculate it instantly
Enter the amount, monthly rate (for a 7% APR type 7 / 12 = 0.5833) and term into the loan payment calculator to see the monthly payment, total repayment, total interest and the month-by-month schedule. Your numbers never leave your browser.
This article is general information, not financial advice. Read the lender's contract and total cost details before taking a loan.