Mortgage Calculator
Build a working mortgage calculator in Excel or Google Sheets using PMT, IPMT, and PPMT. Estimate monthly payments, interest, and principal for any fixed-rate loan.
Introduction
A mortgage calculator answers the question every homebuyer asks: how much will I actually pay each month? With Excel's financial functions you can build one in minutes and then break the payment down into interest, principal, and total cost. This guide covers the PMT-based calculator plus the IPMT, PPMT, CUMIPMT, and RATE functions that power it.
Prerequisites
- Basic formula entry
- Understanding of annual vs periodic interest rates
1How a Mortgage Payment Is Calculated
A fixed-rate mortgage payment is an annuity: equal payments over time that pay off the principal plus interest. PMT returns that periodic payment given the rate per period, the number of periods, and the loan amount (present value). The single most common mistake is mixing periods — if you pay monthly, both the rate and the number of periods must be monthly.
Example
=PMT(6%/12, 360, 200000)6%/12 converts the 6% annual rate to a monthly rate (0.5%). 360 is 30 years x 12 monthly payments. 200000 is the loan principal. PMT returns a negative number by convention, so display it with ABS if you prefer a positive value.
Always divide the annual rate by 12 for monthly payments.
PMT does not include taxes, insurance, or fees — it is principal plus interest only.
2Use the Interactive Mortgage Calculator
Rather than retyping the formula, use the calculator below. Enter your loan amount, annual interest rate, and term in years; it applies the same PMT logic and shows your monthly principal-and-interest payment. Change any input and the result updates instantly.
Try a 15-year vs 30-year term to see how much faster the loan is paid off.
The calculator assumes end-of-period payments (type = 0) and a fully amortized loan (future value = 0).
3Break Down Interest vs Principal with IPMT and PPMT
The PMT result is the total, but each payment is split between interest (IPMT) and principal (PPMT). Early payments are mostly interest; later payments are mostly principal. For any single period, IPMT + PPMT = PMT.
Example
=IPMT(0.05/12, 1, 360, 300000)On a $300,000 loan at 5% over 30 years, the first month's interest is $1,250. PPMT for the same period returns about -$348.55, and the two add up to the first month's total payment.
4Total Interest Over a Range with CUMIPMT
To see how much interest you pay in a block of periods — say the first year, or years 6 through 10 — use CUMIPMT. It sums the interest across a start and end period, which is handy for estimating tax-deductible mortgage interest.
Example
=CUMIPMT(0.05/12, 360, 300000, 1, 12, 0)For the same $300,000 loan, the first 12 monthly payments contain about $14,935 of interest. The type argument (0) is required for CUMIPMT.
5Reverse It: Find the Rate with RATE
If you already know the payment and want to know the effective rate, RATE works backwards from the number of periods, payment, and loan amount. Multiply the periodic result by 12 to estimate the nominal annual rate.
Example
=RATE(360, -1199.10, 200000) * 12Given a $200,000 loan paid at $1,199.10 per month for 360 months, RATE recovers the 6% annual rate. RATE uses an iterative solver, so add a guess argument if it fails to converge.
Interactive Calculator
Estimate your monthly mortgage payment. Enter the loan amount, annual interest rate, and loan term in years.
Functions Used
PMT
PMT calculates the periodic payment for a loan or investment based on constant payments and a constant interest rate. It's the function you need for mortgage, car loan, and any fixed-rate payment calculations.
IPMT
IPMT calculates the interest portion of a payment for a specific period of a loan or investment.
PPMT
PPMT calculates the principal portion of a payment for a specific period of a loan or investment.
CUMIPMT
CUMIPMT returns the total interest paid over a specified range of periods for a loan with constant payments and a constant interest rate.
RATE
RATE returns the periodic interest rate for an annuity based on the number of periods, payment amount, present value, and future value.
Related Guides
Loan Payment Calculator (PMT)
Calculate your monthly loan payment instantly and understand the Excel PMT function behind it. Works for mortgages, car loans, and personal loans.
PMT Function Loan Payment
Master the PMT function loan payment formula in Excel and Google Sheets: calculate monthly mortgage or car payments and split interest from principal.
Calculate Loan Interest
Calculate loan interest in Excel using IPMT, PPMT, and PMT. Learn how to split every payment into interest and principal, and find the true total cost of a loan.
Summary
A mortgage calculator is just PMT applied with the right period math: divide the annual rate by 12 and multiply years by 12. Use IPMT and PPMT to split each payment into interest and principal, CUMIPMT to total interest over any period range, and RATE to work backwards from a known payment. All five functions behave identically in Excel and Google Sheets.
Next Steps
- Build a full amortization schedule by dragging IPMT and PPMT down one row per month
- Compare a 15-year vs 30-year term using the CUMIPMT total-interest figure
- Read the PMT function deep-dive for car loans, leases, and savings goals