# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/build-a-mortgage-calculator-in-excel
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 12 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Compare against the online mortgage calculator](https://dothecalculation.com/calculators/mortgage-calculator) — Check 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.

**Input block**
| 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 |

> **Format B2 as a percentage** — Type 6.5% rather than 6.5. If the cell holds 6.5 as a plain number, the periodic rate becomes 0.5417 instead of 0.005417 and the payment comes out roughly a hundred times too large.

## Step 2: the monthly payment with PMT

**PMT in Excel and Google Sheets**

```
=PMT(rate, nper, pv, [fv], [type])
```
- rate is the rate per period, not per year
- nper is the total number of payments
- pv is the present value, the loan amount
- fv defaults to 0, meaning fully repaid
- type is 0 for payments at period end (the default), 1 for the beginning

In cell B8, the payment is:

**The payment formula**

```
=PMT(B5, B6, -B1)
```
- On the values above this returns 2,212.24.
- The minus sign on B1 is the important part.

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.

**Interest and principal in period 1**

```
=IPMT(B5, 1, B6, -B1)    ·    =PPMT(B5, 1, B6, -B1)
```
- Period 1 interest: 1,895.83
- Period 1 principal: 316.41
- Together: 2,212.24, the full 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.

**Schedule formulas, row 12 onwards**
| 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.

> **If the last balance is not zero** — A residue of a few cents is rounding and is expected. A residue of hundreds means a reference did not lock: check that the rate and payment use absolute references with dollar signs, so filling down does not shift them.

## Step 5: totals and the interest-to-principal ratio

**Summary figures**

```
Total paid: =B8*B6    ·    Total interest: =SUM(C12:C371)    ·    Ratio: =SUM(C12:C371)/B1
```
- Total paid: 796,406
- Total interest: 446,406
- You pay 1.28 dollars of interest for every dollar borrowed.

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.

**Balance with extra payments**

```
=MAX(0, E11 - D12 - F12)
```
- MAX keeps the balance from going negative in the final period.
- Guard the interest column too: =IF(E11<=0, 0, E11*$B$5)

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.

> **Works the same in Google Sheets** — PMT, IPMT, PPMT, CUMIPMT, and the fill-down behaviour are identical. The only difference is that Sheets recalculates automatically with no manual option to disable.

Tool: [Check your schedule against ours](https://dothecalculation.com/calculators/amortization-calculator) — Generate the same amortization schedule online and compare row by row to validate your build.

**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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/build-a-mortgage-calculator-in-excel). Quote freely with attribution and a link to this page._