Mortgage statement to Excel by hand: principal, interest and escrow in their own columns

To put mortgage statements into Excel, give each monthly payment one row and split it into principal, interest, escrow and fees, then add two check columns: one proving each row adds up to the amount paid, and one proving the principal balance rolls forward to what the lender printed. Ten minutes per year of statements, and the spreadsheet proves itself.

ZeraBooks Convert, the statement converter I'll mention at the end, doesn't read loan or mortgage statements yet, so this is the by-hand method. It works with any lender.

The columns

  1. Payment date
  2. Total paid
  3. Principal
  4. Interest
  5. Escrow
  6. Fees
  7. Principal balance after
  8. Escrow balance after
  9. Check: total minus parts (should be 0.00)
  10. Check: previous principal balance minus principal minus balance after (should be 0.00)

Put a first row above the payments with the balances before the first one. Every formula after that refers to the row above.

A worked quarter

This is a made-up rental property mortgage: 4.75% fixed, principal and interest of 1,450.62 a month, and 389.40 a month into escrow for property tax and insurance. The principal balance before January is 186,500.00 and the escrow balance is 1,204.55. In March the servicer pays the year's insurance premium, 1,655.00, out of escrow.

PaidTotalPrincipalInterestEscrowFeesPrincipal balanceEscrow balance
Before January186,500.001,204.55
01/01/20261,840.02712.39738.23389.400.00185,787.611,593.95
02/01/20261,840.02715.21735.41389.400.00185,072.401,983.35
03/01/20261,840.02718.04732.58389.400.00184,354.36717.75
Quarter5,520.062,145.642,206.221,168.200.00

The checks, worked

Rows add across. January: 712.39 + 738.23 + 389.40 = 1,840.02. If a row doesn't, you probably copied a figure from the year-to-date column instead of the current payment.

Principal rolls forward. 186,500.00 − 2,145.64 = 184,354.36, the balance on the March statement. A missing month shows up here as a gap the size of that month's principal.

Escrow rolls forward. 1,204.55 + 1,168.20 − 1,655.00 = 717.75. The escrow balance fell in March because the insurance was paid, not because anything went wrong.

Interest looks like the rate. 186,500.00 × 4.75% ÷ 12 = 738.23, January's interest to the cent. Each month the interest drops by a few dollars as the balance falls. A month where it jumps usually means a late payment or a figure from the wrong line.

Quarter adds up. 2,145.64 + 2,206.22 + 1,168.20 = 5,520.06, which is three payments of 1,840.02.

Getting the figures off the PDF

Most mortgage statements print a box near the top called something like "Explanation of amount due" and a "Transaction activity" section lower down. Take the figures from the activity for the payment received, not from the amount due for next month. If the PDF has a text layer, Excel's Get Data from PDF can sometimes pull the activity table into a sheet; check every figure against the page anyway, because the columns often come in shifted.

Scanned statements have to be typed. That is where the check columns earn their keep.

Why bother splitting it

Because the parts go to different places. Interest is an expense, and for a home mortgage it's what Form 1098 totals for the year; lenders issue a 1098 when interest is $600 or more. Principal reduces what you owe. Escrow is your money held by the servicer until it pays the tax and insurance, and those payments are the expenses, on the dates they're made. Late fees belong in their own column so they don't inflate interest.

For a rental, your accountant will want interest, tax and insurance separately. For a business loan, your books need the principal against the liability and only the interest on the profit and loss.

What software does here

Our loan statement to Excel page has a longer worked example and the questions people ask about escrow and the 1098.

Disclosure: I'm Damin and I run ZeraBooks. Our Convert tool doesn't read loan statements or split principal and interest yet. It reads the bank statements the mortgage payments came out of, proves each one against its printed opening and closing balances, and exports Excel or CSV, so every payment in your spreadsheet can be matched to a checked withdrawal. That's on our bank statement converter page.

Build the two check columns first. Then type. 

Comments

Post a Comment

Popular posts from this blog

How to clean up messy books without redoing the same month twice

Image
Clean up books in this order: collect every statement , set opening balances from the last period your accountant signed off, then reconcile each account one month at a time, oldest first . Only once a month ties do you pair transfers, remove duplicates, place uncategorized lines and move owner spending out. A different order means categorizing lines you later delete. A month that doesn't tie, with the gap shown. During a cleanup you stay on that month until the gap is zero. The worked figures below belong to a fictional design studio whose books stopped being kept in February. They are illustrative only. What has to happen before any categorizing? Step 1: Build a statement grid Accounts down the side, months across the top. Tick a cell when you hold that month's statement as a PDF from the bank. Our studio has checking, savings and one card across eight months, so the grid has 24 cells. Any blank cell is a month you can't prove yet, so request it today. Activity...
Read more

How to convert a credit card statement PDF to Excel, step by step

 You need last quarter's card charges in a spreadsheet by Friday, and all you have is three PDFs from the issuer's website. To convert a credit card statement to Excel, download the PDF statement (not the activity export), run it through a converter or copy the tables out by hand, then fix three things: the signs on payments, the foreign currency detail lines, and the summary blocks. Finish by checking that your rows rebuild the printed new balance. Here's each step. Step 1: Download the statement PDF Go to the card's statements page on the issuer's site and download the monthly statement. Many issuers also offer a "download activity" CSV. Skip it for this job. It usually has no previous or new balance printed anywhere, and those two numbers are how you'll prove the spreadsheet is complete at the end. Watch the dates. A card statement ends on a closing date, not the end of the month. If you need the full quarter, you may need the statement after it too...
Read more

How to scan a bank statement so it converts to Excel cleanly

Image
To scan a bank statement so it converts to Excel cleanly, use 300 dpi and grayscale , keep pages straight, save each account's pages into a single PDF , and include the last page even if it looks empty. Then convert it and check that the opening balance plus the rows equals the printed closing balance. Most conversion problems with scanned statements are decided before the conversion starts, at the scanner. Here is how to get it right, step by step. Where a scanned PDF goes in ZeraBooks Convert: the Upload button, or drop the file on the page. Do you need to scan the statement at all? Step 1: Check online banking first Log in and look at the statements section. If the month is there, download the PDF. A downloaded statement carries the bank's own text and will always beat a scan. What scanner settings work best for bank statements? Step 2: Set the scanner Setting Use Why Resolution 300 dpi Lower loses faint digits; higher only makes bigger files Colo...
Read more