For a standard fixed-rate, fully amortizing mortgage with monthly payments, enter this formula in Excel:
=-PMT(AnnualRate/12,TermYears*12,LoanAmount)For example, a $300,000 loan at 6.5% for 30 years produces an estimated $1,896.20 monthly principal-and-interest payment. That figure does not automatically include property taxes, homeowners insurance, mortgage insurance, HOA dues, or other housing costs.
As an Amazon Associate I earn from qualifying purchases.
What you need before starting
Gather these inputs:
- Home price: the purchase price.
- Down payment: the amount paid upfront.
- Loan amount: usually home price minus down payment.
- Interest rate: use the rate used to calculate scheduled payments, generally the note rate—not automatically the APR.
- Loan term: the repayment period in years.
- Payment frequency: usually monthly for a U.S. mortgage.
- Taxes, insurance, mortgage insurance, and HOA dues: separate monthly-cost inputs.
For monthly payments, convert the annual rate and term to matching monthly periods:
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Monthly rate = Annual rate / 12
Number of payments = Term in years * 12Microsoft notes that the rate and nper arguments in PMT must use consistent periods. See Microsoft’s PMT documentation.
#1 Best Overall
- Profitability calculations; cash flow function Calculates NPV and IRR for uneven cash flows
- Time-value-of-money and Amortization keys solve problems including: pension calculations, loans, mortgages, etc.
- Ideal calculator for students, managers and statisticians
- Built-in functionality : List-based one- and two-variable statistics with four regression options: linear, logarithmic, exponential and power
- The BA II Plus calculator is approved for use on the following professional exams: Chartered Financial Analyst exam. GARP Financial Risk Manager (FRM) exam. Certified Management Accountants exam
Build the basic mortgage calculator
Enter the following example in a blank workbook:
| Cell | Label | Value or formula |
|---|---|---|
| B2 | Loan amount | 300000 |
| B3 | Annual interest rate | 6.5% |
| B4 | Term in years | 30 |
| B5 | Monthly rate | =B3/12 |
| B6 | Number of payments | =B4*12 |
| B7 | Monthly principal and interest | =-PMT(B5,B6,B2) |
| B8 | Total principal and interest | =B7*B6 |
| B9 | Total interest | =B8-B2 |
Format B7:B9 as currency. The approximate results are:
- Monthly principal and interest: $1,896.20
- Total principal and interest: $682,633.47
- Total interest: $382,633.47
These are estimates under the stated assumptions. Lender figures may differ because of timing conventions, rounding, fees, prepaid interest, escrow, mortgage insurance, or other loan terms.
How Excel’s PMT function works
The syntax is:
=PMT(rate, nper, pv, [fv], [type])| Argument | Mortgage meaning |
|---|---|
rate |
Interest rate per payment period |
nper |
Total number of payments |
pv |
Present value, normally the loan principal |
fv |
Balance remaining after the final payment; normally zero |
type |
0 for payment at period end; 1 for payment at period beginning |
For an ordinary mortgage, use the default final arguments of zero:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
=-PMT(AnnualRate/12,TermYears*12,LoanAmount)The negative sign is intentional. Excel uses cash-flow signs: money received and money paid out have opposite signs. You can also write the formula this way:
=PMT(B3/12,B4*12,-B2)Both versions display a positive payment. Do not change type to 1 unless payments genuinely occur at the beginning of each period.
Calculate the loan amount from price and down payment
If you start with the home price, calculate the amount borrowed separately:
Rank #2
- PROFESSIONAL FINANCIAL CALCULATOR : Built-in TVM, IRR, NPV. Engineered for business analysts, real estate investors, accountants, and finance students.
- ADVANCED CASH FLOW & AMORTIZATION : Execute time value of money, break-even analysis, depreciation schedules, and bond pricing. Trusted for professional exam prep", MBA coursework, and banking certifications.
- CATIGA CF-300 : Flip-open hard case with a snap-close design for a secure fit. Compact and portable: designed for daily professional use in office, classroom, or on-site.
- ALL-IN-ONE FOR PROFESSIONALS : From NPV/IRR for real estate analysis to statistical calculations for business analysts. Handles probability, linear regression, and complex financial formulas.
- MORTGAGE, LOAN & INVESTMENT CALCULATOR : Covers bond pricing, loan amortization, investment analysis, and exam-level computations. Your go-to accounting calculator, business calculator, and real estate calculator in one device.
| Cell | Label | Value or formula |
|---|---|---|
| B2 | Home price | $375,000 |
| B3 | Down payment | $75,000 |
| B4 | Loan amount | =B2-B3 |
| B5 | Annual interest rate | 6.5% |
| B6 | Term in years | 30 |
| B7 | Monthly payment | =-PMT(B5/12,B6*12,B4) |
The compact version is:
=-PMT(B5/12,B6*12,B2-B3)This simple calculation assumes closing costs, discount points, prepaid items, financed mortgage insurance, and other charges are not added to the loan. If any costs are financed, use the actual principal amount instead.
Free tools Windows power users keep installed
One-click scans. No signup required.
Add taxes, insurance, mortgage insurance, and HOA dues
PMT calculates principal and interest only. The broader monthly housing payment may also include property taxes, homeowners insurance, mortgage insurance, and HOA dues. The Consumer Financial Protection Bureau explains this distinction.
Set up a separate section such as:
| Component | Formula or input |
|---|---|
| Principal and interest | =-PMT(rate/12,years*12,loan) |
| Annual property taxes | User input |
| Monthly property taxes | =AnnualTaxes/12 |
| Annual homeowners insurance | User input |
| Monthly homeowners insurance | =AnnualInsurance/12 |
| Monthly mortgage insurance | User input or separate model |
| Monthly HOA dues | User input |
| Estimated total payment | Sum of all monthly components |
For example:
=PI_Payment+AnnualPropertyTaxes/12+AnnualHomeownersInsurance/12+MonthlyMortgageInsurance+MonthlyHOATaxes, insurance premiums, mortgage insurance, and other escrowed charges can change. Treat a fixed total-payment projection as an assumption, not a guarantee.
Create an amortization schedule
An amortization schedule shows how each payment is divided between interest and principal.
Inputs
| Cell | Label |
|---|---|
| B2 | Loan amount |
| B3 | Annual interest rate |
| B4 | Term in years |
| B5 | =B3/12 |
| B6 | =B4*12 |
| B7 | =-PMT(B5,B6,B2) |
Schedule columns
Create columns for payment number, payment date, beginning balance, scheduled payment, extra principal, interest, scheduled principal, ending balance, and total payment.
Assume the first payment is on row 12 and the first payment date is stored in B10:
Rank #3
- HP 10BII+ FOR STUDENTS & PROFESSIONALS – This HP calculator is built for business, finance, accounting, and statistics courses. Perfect for learners and professionals who need to solve common financial problems quickly without memorizing formulas or relying on spreadsheets.
- 100+ FUNCTIONS FOR REAL WORLD MATH – Quickly solve time value of money, interest rates, loan payments, NPV, IRR, cash flows, and more. The 10bII+ also includes probability distributions for statistics courses—a feature not often found in financial calculators.
- ALGORITHMIC INPUT WITH DEDICATED KEYS – This high-school/college calculator uses algebraic and chain logic with minimal keystrokes. Layout appears the same as standard calculators for easy learning. Dedicated keys give quick access to commonly used financial and statistical functions
- APPROVED FOR MAJOR EXAMS – The HP 10bII+ algebra calculator is permitted for use on SAT, PSAT/NMSQT, and AP tests. An ideal statistics calculator and business calculator for school finance and accounting students preparing for class, coursework, or standardized exams.
- INCLUDES TRAVEL CASE, CLEANING CLOTH & BATTERIES– Slim, durable, and easy to keep on hand or store in a backpack or locker. Includes a protective case, cleaning cloth, and batteries so it’s ready out of the box. Large screen with clear contrast (non-backlit) is easy to read during exams or lectures.
A12: 1
B12: =$B$10
C12: =$B$2
D12: =$B$7
E12: =0
F12: =C12*$B$5
G12: =D12-F12
H12: =MAX(0,C12-G12-E12)
I12: =D12+E12For the next row, use:
A13: =A12+1
B13: =EDATE(B12,1)
C13: =H12
D13: =MIN($B$7,C13+F13)
E13: =0
F13: =C13*$B$5
G13: =MIN(D13-F13,C13)
H13: =MAX(0,C13-G13-E13)
I13: =D13+E13Copy row 13 downward for the scheduled number of payments. MAX(0,...) prevents a negative final balance, while MIN(...) keeps the final payment from exceeding what remains.
Format amounts as currency but avoid rounding the balance in each formula. Excel may retain fractional cents internally while displaying two decimal places. A lender may round differently, so use the lender’s amortization schedule for reconciliation.
Use IPMT and PPMT instead
For a fixed-rate loan, Excel can calculate the interest and principal for a specific payment:
Interest for payment 1:
=-IPMT($B$5,A12,$B$6,$B$2)
Principal for payment 1:
=-PPMT($B$5,A12,$B$6,$B$2)IPMT returns the interest portion and PPMT returns the principal portion. See Microsoft’s IPMT documentation and PPMT documentation.
Calculate total and cumulative interest
For the full loan term:
=MonthlyPayment*NumberOfPayments-LoanAmountFor interest during a selected range of payments, use CUMIPMT:
=-CUMIPMT(MonthlyRate,TotalPayments,LoanAmount,StartPeriod,EndPeriod,0)For the first 12 months:
=-CUMIPMT(B5,B6,B2,1,12,0)Use positive rate, number-of-payment, and loan-amount inputs. Period numbers begin at 1. Excel also provides CUMPRINC for cumulative principal; Microsoft lists it in its financial-functions reference.
Rank #4
- Solves time-value-of-money calculations such as annuities, mortgages, leases, savings, and more
- Performs cash-flow analysis for up to 32 uneven cash flows with up to 4-digit frequencies
- Calculates various financial functions: Net Future Value Net present Value Modified Internal Rate of Return Internal Rate of Return Modified Duration Payback Discounted Payback
- The Texas Instruments BAII Plus Professional features an Automatic Power Down (APD) function for extended battery life
- Prompted display guides you through financial calculations showing current variable and label. Ten-digit display
Compare rates, terms, and loan amounts
Make a scenario table with columns for scenario, loan amount, rate, term, monthly principal and interest, and total interest:
| Scenario | Loan amount | Rate | Term | Monthly P&I | Total interest |
|---|---|---|---|---|---|
| 30-year | $300,000 | 6.50% | 30 | =-PMT(C2/12,D2*12,B2) |
=E2*(D2*12)-B2 |
| 20-year | $300,000 | 6.50% | 20 | Copy formula | Copy formula |
| 15-year | $300,000 | 6.50% | 15 | Copy formula | Copy formula |
This makes the trade-off visible: shorter terms generally require higher monthly payments but fewer payment periods and less total interest, assuming the same principal and rate.
Use What-If Analysis
On supported desktop Excel editions, including Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016:
- Put the payment formula in the upper-left cell of a comparison grid.
- Place possible interest rates across the top row.
- Place possible terms down the first column.
- Select the entire grid.
- Choose Data > What-If Analysis > Data Table.
- Set the row input cell to the interest-rate input and the column input cell to the term input.
Feature availability can differ across desktop, Mac, web, and mobile editions. See Microsoft’s Data Table documentation.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Model extra principal payments
Add an input cell, such as B8, for monthly extra principal. In the schedule, cap the extra amount so it cannot exceed the remaining balance after scheduled principal:
Recommended Free Tools
E12: =MIN($B$8,MAX(0,C12-G12))
H12: =MAX(0,C12-G12-E12)Copy the same logic through the schedule. Extra principal can shorten the payoff period and reduce interest when it is applied to principal, but the spreadsheet cannot determine how your servicer will apply an overpayment. Check the loan documents and servicer instructions for principal-only payments, recasting, and any prepayment restrictions.
Best Value
- Brand New in box; The product ships with all relevant accessories
- Dedicated keys allow easy access to common financial and statistics functions
- Easy-to-use design provides business, finance and statistical calculations fast
- Specially designed to meet the mathematical needs
Special cases the basic formula does not handle
- Adjustable-rate mortgages:
PMTassumes a constant rate. Model each rate period separately, including adjustment dates, caps, floors, and any interest-only period. - APR: APR reflects certain finance charges and is not automatically the rate used for scheduled principal and interest. Use the applicable note rate unless the loan documents specify otherwise.
- Biweekly payments: A mathematical biweekly model can use
=-PMT(AnnualRate/26,TermYears*26,LoanAmount), but lender programs may use different timing, application rules, or fees. - Zero-interest loans: A manual formula needs a separate branch:
=IF(rate=0,loan/number_of_payments,-PMT(rate,number_of_payments,loan)). - Balloon balances: If a balance remains at the end, model it with the
fvargument, for example=-PMT(rate,nper,loan,balloon_balance). - Interest-only periods: Use one section for interest-only payments, then calculate a new payment over the remaining principal and amortization period.
Common Excel mortgage mistakes
| Problem | Fix |
|---|---|
Entering 6.5 instead of 6.5% |
Enter 6.5% or 0.065. |
| Using 30 as the number of payments | Use 30*12 for a monthly 30-year loan. |
| Using the annual rate unchanged | Divide it by 12 for monthly payments. |
| Thinking a negative result is an error | Use -PMT(...) or enter the principal as a negative cash flow. |
| Using purchase price as principal | Subtract the down payment and add only genuinely financed costs. |
Adding taxes inside PMT |
Calculate them separately and add monthly estimates. |
#NUM! from CUMIPMT |
Check positive inputs and ensure the requested periods are between 1 and the total number of payments. |
Reconcile the workbook with the lender
Compare your assumptions with the lender’s Loan Estimate and amortization information:
- Loan amount and whether any costs are financed.
- Note interest rate, not just APR.
- Term, payment frequency, and payment timing.
- Scheduled principal-and-interest payment.
- Property-tax and homeowners-insurance estimates.
- Mortgage insurance and the period for which it applies.
- Escrow amount and whether it may change.
- Loan fees, points, prepaid interest, and other charges.
- Any adjustable-rate, balloon, interest-only, or prepayment terms.
A spreadsheet is a planning model, not the legal payment record. If the numbers differ, identify which assumption differs before changing the formula.
Further Excel resources
Microsoft provides official mortgage and loan-amortization templates. Templates can save setup time, but inspect their rate, payment-frequency, escrow, and extra-payment assumptions before relying on the output. The calculation itself does not require a paid add-on or specialized mortgage software.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesFrequently Asked Questions
Does Excel’s PMT include taxes and insurance?
No. PMT normally calculates principal and interest only. Enter property taxes, homeowners insurance, mortgage insurance, and HOA dues separately, convert annual costs to monthly amounts, and add them to the PMT result.
Why is my mortgage payment negative?
Excel’s financial functions use cash-flow signs. A negative result represents money paid out. Use -PMT(…) to display the borrower’s payment as a positive amount.
Should I divide APR by 12?
Do not use APR automatically. Use the interest rate specified for calculating scheduled principal and interest, usually the note rate. APR includes certain finance charges and may not reproduce the lender’s payment.
Can Excel calculate an adjustable-rate mortgage?
PMT can calculate a payment for one stated rate, but it assumes a constant rate. An adjustable-rate mortgage requires separate calculations for each rate period and its adjustment rules.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Why does my Excel result differ from the lender’s payment?
Check the loan amount, note rate, payment frequency, timing, rounding, financed costs, escrow estimates, mortgage insurance, fees, and whether the loan has adjustable or other special terms.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
