RatedrillMortgage payment formula
The monthly mortgage payment is M = P x r / (1 - (1 + r)^-n), where P is the loan amount, r the annual rate divided by 12 and n the number of monthly payments. Equivalently M = P x r(1 + r)^n / ((1 + r)^n - 1). In Excel: =PMT(rate/12, years*12, -principal).
Syntax
M = P x r / (1 - (1 + r)^-n) with r = annual rate / 12, n = years x 12| Part | What it is |
|---|---|
P | Loan principal |
r | Monthly interest rate (annual rate / 12) |
n | Total number of monthly payments (years x 12) |
Examples
P = 200,000, 6% for 30 years
r = 0.005, n = 360
M = 200,000 x 0.005 / (1 - 1.005^-360)Result: M = 1,199.10 per month
Excel: =PMT(6%/12, 360, -200000)
P = 300,000, 5% for 30 years
M = 300,000 x (0.05/12) / (1 - (1 + 0.05/12)^-360)Result: M = 1,610.46 per month
P = 200,000, 6% for 15 years
M = 200,000 x 0.005 / (1 - 1.005^-180)Result: M = 1,687.71 per month
Total interest 103,788 versus 231,676 over 30 years
Common mistakes
- Using the annual rate (0.06) instead of the monthly rate (0.005) produces a payment many times too high.
- Using n = 30 instead of 360; n must be in months when r is monthly.
- Forgetting that the formula gives principal and interest only; taxes, insurance and HOA fees are added on top.
Practice Mortgage payment formula
These drills check your answer on live data, free, no account needed.
- Monthly mortgage paymentYou buy a home for $155,000 and put 5% down. The rest is a 30-year fixed mortgage at 8% APR with monthly payments. What is the monthly payment of principal and interest?
- APR implied by a paymentA lender offers $10,500 to be repaid in 36 monthly payments of $361.62. What APR (the monthly rate × 12) does this payment imply?
Questions
How is the mortgage payment calculated in Excel?
=PMT(annual_rate/12, years*12, -loan_amount). For 200,000 at 6% over 30 years: =PMT(6%/12, 360, -200000) returns 1,199.10.
Why is most of the early payment interest?
Interest each month is the balance times r. At the start the balance is largest, so interest is 1,000 of the first 1,199.10 payment; as the balance falls, more of each fixed payment goes to principal.
Related
- Amortization scheduleAn amortization schedule splits each payment into interest and principal and tracks the balance. Worked schedule for a 10,000 loan, Excel IPMT and PPMT.
- Total interest paid on a loanTotal interest on an amortizing loan is the payment times the number of payments minus the principal. Examples for 30-year and 15-year loans, Excel CUMIPMT.
- How much can I borrowThe loan you can afford for a given payment is the present value of an annuity: P = PMT x (1 - (1 + r)^-n) / r. Mortgage and car loan examples and Excel PV.
- Remaining loan balanceThe balance after k payments is the present value of the payments still to come: B_k = PMT x (1 - (1 + r)^-(n-k)) / r. Mortgage and car loan examples, Excel.