Excel calculates a mortgage payment with the PMT function, which returns the fixed monthly principal-and-interest figure for any fixed-rate loan using the standard amortization formula. The exact result is M = P × r(1+r)^n / ((1+r)^n − 1), where P is the loan principal, r is the monthly interest rate (annual rate divided by 12), and n is the total number of monthly payments (years times 12). For a $300,000 loan at 6% over 30 years, that formula produces a monthly payment of $1,798.65. Excel wraps this math in =PMT(rate, nper, pv), so you type four inputs and the cell returns the answer immediately. Once you understand the PMT function, the same setup extends to IPMT and PPMT, which split each payment into interest and principal pieces and feed an amortization schedule. The catch is that PMT only models principal and interest. Real monthly housing costs usually include property tax, homeowners insurance, and HOA dues, the combination lenders call PITI. To get those numbers you either add columns by hand or switch to a purpose-built tool that handles PITI and a year-by-year schedule for you.
What the PMT Function Actually Calculates
PMT is one of Excel's built-in financial functions. It takes the interest rate per period, the total number of payments, and the loan principal, then returns the equal monthly amount that fully repays the loan over its term. The math behind it is the same annuity formula used by every standard mortgage calculator: each month, interest is charged on the remaining balance, and the rest of the payment chips away at the principal. Early in the loan most of the payment is interest; later in the loan most of it is principal. PMT hides that split, but IPMT and PPMT reveal it on demand. One detail worth noting: the rate argument in PMT must be the periodic rate, so for a mortgage you divide the annual percentage rate by 12. Passing 6% instead of 0.5% will produce a wildly wrong number that looks plausible on the screen.
Excel Setup: Entering the Four Inputs
Before you write a single formula, set up four labelled cells. Using dedicated cells means you can change one input and watch every formula update automatically. The inputs and their conventions are:
| Cell label | What you type | What Excel needs |
|---|---|---|
| Annual rate | 6 (for 6%) | Raw annual percentage as a number, or a percent-formatted value |
| Monthly rate | =B1/12 | Periodic rate — divide annual by 12 inside the formula |
| Years | 30 | Loan term in years |
| Payments | =B3*12 | Total number of monthly payments |
| Loan principal | =home_price - down_payment | Home price minus down payment, not the full purchase price |
Keeping rate and principal in their own cells also makes it easy to copy the formula across an amortization schedule without retyping anything, and makes the model readable if you share the file.
Writing the PMT Formula Step by Step
- Click an empty cell and type the equals sign to start a formula.
- Type PMT( to begin the function call.
- Enter the monthly rate, which is your annual rate cell divided by 12. If your annual rate lives in B1, type B1/12.
- Type a comma, then enter the total number of payments — your years cell times 12, for example B3*12.
- Type a comma, then enter the loan principal as a positive cell reference. PMT returns a negative result when the principal is positive, so wrap the reference in a minus sign: -B5.
- Close the parentheses and press Enter. The result is your monthly principal-and-interest payment.
Using the inputs above — 6% annual rate, 30-year term, $300,000 principal — Excel returns -$1,798.65. Flip the sign if you want a positive number. To sanity-check, multiply $1,798.65 by 360 monthly payments and subtract the $300,000 principal: $1,798.65 × 360 − $300,000 = $647,514 − $300,000 = $347,514. That is the total interest you will pay over the life of the loan if the rate never changes. The same annuity math is described in the amortization calculator reference and the mortgage calculator reference.
Building a Simple Amortization Schedule
PMT gives you one number, but lenders hand you a schedule that splits each payment into interest and principal. In Excel you build that with two companion functions: IPMT(rate, period, nper, pv) returns the interest portion of a specific payment, and PPMT(rate, period, nper, pv) returns the principal portion. To populate a 360-row schedule, create a column for the period number (1 to 360), then drag =IPMT($B$1/12, A2, $B$3*12, -$B$5) and =PPMT($B$1/12, A2, $B$3*12, -$B$5) down the sheet. A final column tracks the remaining balance after subtracting each principal portion. Building this from scratch in Excel takes twenty minutes the first time and is genuinely instructive. Once you have done it once, repeating it for a different rate or term is mechanical — and that is exactly where a browser tool starts to feel faster.
Why Use a Browser Calculator Instead
Excel is a great teaching tool, but for day-to-day decisions it is slower than it needs to be. Every new scenario means opening the file, changing four cells, dragging formulas, and reading the schedule by eye. A browser-based Mortgage Calculator takes the same four inputs and returns the same PMT answer plus the total interest, total amount paid, and a full year-by-year amortization schedule without a single formula. It also adds optional fields for property tax, annual insurance, and HOA dues, which Excel does not model at all unless you build extra columns by hand. Everything runs locally in your browser, so the home price and rate you type never leave your device. For quick what-if comparisons — "what if I put 20% down instead of 10%?" or "what if I take a 15-year term?" — switching tools takes seconds and avoids the risk of accidentally pointing PMT at the wrong cell.
P&I vs PITI: What Excel Does Not Show by Default
The number PMT returns is P&I — principal and interest only. That is the figure that repays the loan, but it is not the figure that decides whether you qualify for one. Lenders qualify borrowers on PITI, which adds property tax and homeowners insurance to the monthly payment. With HOA dues included, the total is sometimes written as PITIH or simply "total monthly housing cost." The simplest way to model PITI in Excel is to add three more cells: monthly tax as home_value × tax_rate / 12, monthly insurance as annual_premium / 12, and HOA as a flat amount. Sum all five numbers — principal, interest, tax, insurance, HOA — to see what you will actually write each month. The Mortgage Calculator exposes these as optional fields next to the loan inputs so the PITI total appears beside the P&I figure without extra columns.
Limits to Remember
Both PMT and the browser tool share the same simplifying assumptions, so use either one with the same caveats. The rate is treated as fixed for the entire term and interest compounds monthly — the standard convention for U.S. fixed-rate mortgages. Neither model includes private mortgage insurance (PMI), which many buyers pay when the down payment is under 20%, nor do they include points, closing costs, or escrow adjustments. Adjustable-rate mortgages, where the rate changes after an introductory period, cannot be modeled with a single PMT call. None of these outputs are a loan offer, and exact figures depend on the lender's underwriting, local tax rates, and the insurance carrier. Treat the numbers as planning estimates and confirm the final payment, total interest, and PITI breakdown with a licensed mortgage professional before signing anything.