To bring a mortgage statement to Excel, take one row per monthly statement and record the payment, the part applied to principal, the part applied to interest, the escrow deposit, any fees, and the outstanding principal afterwards. Line up twelve statements and each month's ending balance should equal the previous balance minus that month's principal.
Fields worth keeping from a mortgage statement
A mortgage statement is short, but only a handful of its numbers matter for a spreadsheet: the statement date, the payment due and its split, what was actually received and how it was applied, the outstanding principal, the interest rate, and the escrow balance. Everything else on the page is contact details, notices and legal text.
What the rules require servicers to print
In the US, most residential mortgage servicers have to send a periodic statement with a fixed set of contents. The CFPB's text of Regulation Z section 1026.41 lists them: the amount due and its due date, an explanation that splits the payment into principal, interest and escrow, a breakdown of payments received since the last statement and year to date, a list of transactions since the last statement, and account information such as outstanding principal and the current interest rate. That consistency is useful. Whoever services your loan, the same building blocks should be somewhere on the page.
A column layout that works
Use one row per statement, with these columns:
- Statement date and payment due date.
- Payment received, the total that arrived.
- Principal applied, interest applied, escrow applied and fees applied.
- Unapplied or suspense funds, if any.
- Outstanding principal after the payment.
- Interest rate.
- Escrow balance.
Keep dates as real dates and amounts as plain numbers with two decimals. A dollar sign typed into the cell turns the value into text that will not sum.
How is each mortgage payment split into principal, interest and escrow?
Each mortgage payment is split by the servicer: interest is charged on the outstanding principal for the period, escrow takes the fixed monthly amount set for taxes and insurance, and whatever is left of the principal-and-interest portion reduces the loan balance. The statement prints that split in two places, the amount due box and the past payments breakdown.
Two boxes that look alike
The explanation of amount due describes the payment you owe next. The past payment breakdown describes the payment that already arrived. They often hold the same figures, which tempts people to copy whichever is easier to read. Take the split from the past payment breakdown. That is what actually happened to your money, and it is the figure that changes the balance.
Interest falls a little each month
On a fixed-rate loan, the principal-and-interest total stays the same each month. Because interest is charged on a slightly smaller balance each time, the interest share shrinks and the principal share grows by a few dollars. If your sheet shows the interest flat for several months in a row, a value was probably copied from the wrong statement.
Year-to-date columns
The past payment breakdown usually also shows year-to-date totals for principal, interest, escrow and fees. Do not enter those as monthly values. Use them as a check instead: by the December statement, the sum of your monthly interest column should equal the year-to-date interest printed on it.
A twelve-month mortgage balance tracker
A mortgage balance tracker is one sheet with a row for every statement in the year, in date order, showing principal applied and the ending balance. Its job is to show that each ending balance equals the previous one minus that month's principal. Any row where that fails points to a missing statement, an extra payment, or a transcription slip.
Worked example: the first six months
The loan below is an example, not a real account: a balance of 240,000.00 at a fixed 6% rate, a principal-and-interest payment of 1,500.00, and an escrow deposit of 450.00, so 1,950.00 total each month.
| Month | Payment | Interest | Principal | Escrow | Ending balance |
|---|---|---|---|---|---|
| Jan | 1,950.00 | 1,200.00 | 300.00 | 450.00 | 239,700.00 |
| Feb | 1,950.00 | 1,198.50 | 301.50 | 450.00 | 239,398.50 |
| Mar | 1,950.00 | 1,196.99 | 303.01 | 450.00 | 239,095.49 |
| Apr | 1,950.00 | 1,195.48 | 304.52 | 450.00 | 238,790.97 |
| May | 1,950.00 | 1,193.95 | 306.05 | 450.00 | 238,484.92 |
| Jun | 1,950.00 | 1,192.42 | 307.58 | 450.00 | 238,177.34 |
Each interest figure is one month at 0.5% of the previous ending balance, rounded to the cent. Real servicers may compute interest on a slightly different basis, so expect your own numbers to differ from a hand calculation by small amounts.
The two formulas that check it
Add a column called Expected balance. In the second row, enter the previous ending balance minus this row's principal. Add another column that subtracts the printed ending balance from the expected one. Every cell in that column should be zero. Then add a column that checks payment equals interest plus principal plus escrow plus fees. A non-zero result means one of the four was misread.
Comparing with the original schedule
If you have the amortization schedule from closing, put it beside the tracker and compare the balances month by month. The loan amortization schedule PDF to Excel guide covers getting that schedule into the same column layout, so a lookup on the month can pull both side by side.
Escrow account lines: taxes, insurance and shortages
An escrow account is money the servicer holds to pay your property tax and homeowners insurance. Each payment adds to it, and the statement shows a withdrawal when a tax bill or insurance premium is paid. Track the escrow balance in its own column, and record each disbursement as a separate row on a second sheet.
Disbursements on a second sheet
Escrow payments out happen a few times a year, not monthly. Put them on their own sheet with date, payee type (tax or insurance), and amount. When the property tax bill arrives, you can match it against the disbursement. The property tax bill PDF to Excel guide shows how to pull the bill's installments and parcel details into rows that line up with these escrow payments.
Annual escrow analysis
Once a year the servicer reviews the escrow account and may change the monthly escrow deposit. A shortage raises your payment; a surplus can produce a refund. The new amount starts on a specific statement, so expect the escrow column to change at that row. Note the change in a comment rather than averaging it away.
A misread to watch for
Escrow balance and escrow applied sit near each other and are both labelled "escrow". The balance is the running total held. The applied amount is this month's deposit. If your escrow applied column suddenly shows a four-figure number, you have probably copied the balance.
Why doesn't the mortgage principal drop by the amount you expected?
Mortgage principal drops by a different amount than expected for a few common reasons: an extra principal payment, a payment held in a suspense account instead of being applied, a rate change on an adjustable loan, or a fee taken before principal. The transaction list on the statement shows which one happened.
Extra principal payments
A payment above the amount due is usually applied to principal, unless you or the servicer specified otherwise. It shows as a larger principal figure for that month, or as a separate transaction line. Record it in a separate Extra principal column so the regular split stays comparable from month to month.
Partial payments held in suspense
Servicers can hold a payment smaller than the full amount due in a suspense or unapplied funds account until enough arrives to make a full payment. While it sits there, the principal does not move. The statement shows the amount held. Add it to its own column so your payment received total still matches.
Adjustable rates
On an adjustable-rate loan, the interest rate printed on the statement can change at set dates. When it does, the interest share changes and the payment may change too. Keep the rate column filled on every row; it explains a jump that would otherwise look like an error.
Mortgage year-end figures versus summed monthly statements
For tax purposes, the year-end figures your servicer reports are the ones to use, not your sum of monthly interest. Summed statements can differ because of a payment that posted in January for December, a correction after a statement was issued, or points and other items reported separately. Use your sheet to check the annual form, not to replace it.
Where the two disagree
If your December year-to-date interest differs from the annual form, look first at payments near year end. A payment sent on December 31 might post on January 2 and count in the next year. Then look for adjustments listed in the transaction activity of the January statement.
Paper statements and old loans
Older statements may only exist on paper, or as scans with no text layer. A scan needs a text-recognition step before any figure can be copied out. The guide to scanning a printed spreadsheet into Excel covers that step and the checks that catch misread digits, which matter a great deal when every digit of a balance is significant.
The checking account side
Mortgage payments usually leave a checking account, and matching them to the bank statement confirms each one was paid on time. FastBankConvert is being built for that bank-side half of the job, turning checking and savings statement PDFs into Excel or CSV and flagging balances that do not add up. It is not open yet; the get-started page is where to sign up for when it is.