Build a Mortgage Calculator in Excel: PMT, IPMT, PPMT and a Full Schedule
A working mortgage calculator in a spreadsheet takes four formulas and about ten minutes. Here is the build, the sign convention that trips everyone up, and how to add extra payments.
Build a Mortgage Calculator in Excel
Excel and Google Sheets ship with the loan maths built in. Four functions do everything a mortgage calculator does: PMT for the payment, IPMT and PPMT for the split between interest and principal in any given month, and CUMIPMT for interest across a range of periods.
The advantage of building it yourself is that every assumption is visible in a cell you can change. The disadvantage is the sign convention, which produces a negative payment the first time nearly everyone tries it. Both are covered below.
Compare against the online mortgage calculatorCheck your spreadsheet against the same formulas with an amortization schedule already built.Step 1: lay out the inputs
Put every input in its own labelled cell and never type a number inside a formula. This is what lets you change one assumption and see everything update.
Swipe sideways to compare columns.
| Cell | Label | Example value |
|---|---|---|
| B1 | Loan amount | 350000 |
| B2 | Annual interest rate | 6.5% |
| B3 | Term in years | 30 |
| B4 | Payments per year | 12 |
| B5 | Periodic rate | =B2/B4 |
| B6 | Number of payments | =B3*B4 |
Step 2: the monthly payment with PMT
In cell B8, the payment is:
Excel treats money as directional: cash you receive is positive, cash you pay out is negative. The loan is money coming to you, so entering it as a positive present value makes the payment negative. Negating the loan amount instead of the result keeps the sign logic in one place and stops it reappearing when you use IPMT and PPMT.
Step 3: split any payment with IPMT and PPMT
PMT gives the total. IPMT gives the interest portion of a specific payment number, and PPMT gives the principal portion. They always sum to the payment.
Change the period argument to 360 and the split reverses almost entirely: 11.92 of interest against 2,200.32 of principal. That reversal is the whole story of an amortising loan, and seeing it in two cells is more convincing than any explanation.
Step 4: the full amortization schedule
Set up headers in row 11: Period, Payment, Interest, Principal, Balance. Then in row 12, enter the first period.
Swipe sideways to compare columns.
| Column | Formula in row 12 | Then fill down |
|---|---|---|
| A (Period) | 1 | =A12+1 |
| B (Payment) | =$B$8 | same absolute reference |
| C (Interest) | =E11*$B$5 | refers to previous balance |
| D (Principal) | =B12-C12 | payment less interest |
| E (Balance) | =E11-D12 | previous balance less principal |
Put the opening balance in E11, equal to =B1. Select row 12 and fill down to row 371 for a 30-year loan. The balance in the final row should be zero, or within a cent of it.
Step 5: totals and the interest-to-principal ratio
CUMIPMT gives the same total in one formula: =CUMIPMT(B5, B6, -B1, 1, B6, 0). It is also useful for a range, such as interest paid in the first five years: =CUMIPMT(B5, B6, -B1, 1, 60, 0).
Step 6: adding extra payments
This is where a spreadsheet beats most online calculators, because you can vary the extra payment month by month. Add a column F for the extra amount and change the balance formula.
On the loan above, $200 extra every month clears the mortgage in 24 years and 4 months instead of 30, and cuts total interest from $446,406 to $339,412. Count the rows where the balance is still above zero to find the new term: =COUNTIF(E12:E371, ">0").
Five things that go wrong
- Annual rate used as the periodic rate. PMT needs rate per period, so divide by payments per year.
- Percentage typed as a whole number. 6.5 in a general-format cell is 650%.
- Present value entered positive, producing a negative payment. Negate the loan amount instead.
- References not locked when filling down. Rate and payment need dollar signs.
- Term in years used where nper wants payments. A 30-year monthly loan is 360, not 30.
Why does my Excel PMT result come out negative?
Excel treats outgoing cash as negative. Entering the loan as a positive present value makes the payment negative. Enter the loan amount as negative instead, so PMT returns a positive payment and IPMT and PPMT stay consistent with it.
What is the difference between PMT, IPMT and PPMT?
PMT returns the total payment, which stays constant. IPMT returns the interest portion of one specified payment and PPMT the principal portion. IPMT and PPMT for the same period always sum to PMT.
How do I model extra payments in an amortization schedule?
Add an extra-payment column and subtract it in the balance formula, wrapping the result in MAX(0, ...) so the balance stops at zero. Guard the interest formula with IF so it returns zero once the loan is cleared.
Does this work in Google Sheets?
Yes. PMT, IPMT, PPMT, and CUMIPMT take the same arguments and return the same values in Google Sheets, and the schedule fills down identically.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team