To get a loan statement into Excel, give each monthly statement one row and copy the payment breakdown into separate columns for principal, interest, escrow and fees, plus the principal balance after the payment. Twelve rows make the loan year. Total the interest column and compare it with the lender's Form 1098 for the same year.
What fields a loan statement carries into Excel
A loan statement carries a small set of fields into Excel: the payment date, the amount received, how that payment was split between principal, interest and escrow, any fees, the principal balance and the interest rate. A mortgage statement prints most of these on its first page, so one statement becomes one row with about eight columns.
For mortgages, federal rules fix much of the layout. The Regulation Z mortgage periodic statement rules in § 1026.41 apply to closed-end consumer loans secured by a dwelling. Section 1026.41(d) requires the statement to show:
- The amount due and its due date, plus any late fee and when it applies.
- An explanation of the amount due, breaking the monthly payment into principal, interest and escrow.
- A past payment breakdown, giving payments received since the last statement and for the year to date, each split into principal, interest, escrow, fees and charges, and any amount held in suspense.
- Transaction activity since the last statement, each with a date, a description and an amount.
- Account information, including the outstanding principal balance and the current interest rate.
Not every field belongs on the sheet. The amount due is next month's bill, not this month's activity. Contact details and counseling notices are not figures. The columns worth keeping are the ones that describe a payment that was actually made and the balance it left.
Columns for a loan year
| Column | Where it comes from on the statement | Example value |
|---|---|---|
| Payment date | Transaction activity | 01/01/2026 |
| Amount received | Past payment breakdown, since last statement | 2,050.00 |
| Principal | Past payment breakdown | 360.00 |
| Interest | Past payment breakdown | 1,240.00 |
| Escrow | Past payment breakdown | 450.00 |
| Fees | Past payment breakdown or transaction activity | 0.00 |
| Principal balance after | Account information | 247,640.00 |
| Interest rate | Account information | 6.000% |
Car loans, personal loans and business loans are not covered by § 1026.41, so their statements vary more. Most still show a payment split and a balance. Use the same columns and leave Escrow empty when the loan has none.
Converting the statement PDF into rows is a separate step. How to convert a bank statement PDF to Excel walks through it, and the same method works for a loan statement's activity table.
Splitting each loan payment into principal, interest and escrow
Splitting each loan payment into principal, interest and escrow means copying the servicer's own breakdown, not calculating one. The past payment breakdown block on a mortgage statement shows how the money received since the last statement was applied. Those printed figures are what the sheet records, because they are what the servicer actually did.
Regulation Z § 1026.41(d)(3) requires that block to give "the total of all payments received since the last statement," split into principal, interest, escrow, fees and charges, and any suspense or unapplied funds, plus a year-to-date version with the same split. Read the "since last statement" column for the row. Keep the year-to-date column for checking.
The split usually has a pattern. On a fixed-rate loan with a level payment, such as the example in the next section, interest tends to fall a little each month as the balance falls, and principal rises by about the same amount. Escrow stays level until the servicer reviews the account. A row that breaks this pattern, such as interest that jumps or principal that drops, is worth a second look at the statement before anything else.
Three checks catch most copying errors:
- The parts add to the payment. Principal plus interest plus escrow plus fees equals the amount received.
- The balance falls by the principal. Last row's balance minus this row's principal equals this row's balance.
- Year to date agrees. The running total of each column equals the statement's year-to-date figure for that part.
Dates on these rows cause their own problems, especially when statements print short dates or a payment posts days after it was sent. Bank statement date formats in Excel covers turning text dates into real ones.
How do you build a year of loan statements into one Excel sheet?
A year of loan statements builds into one Excel sheet as twelve rows, one per monthly statement, in date order, with an opening balance row above them and a total row below. The total row sums principal, interest, escrow and fees for the year. The last balance should equal the opening balance minus the principal total.
A 12-row sheet for one loan year
This example uses a fictional client, the Haverford household, with a fixed-rate mortgage at Example Mortgage Servicing, loan XXXX-4821. The rate is 6.000%, the principal and interest payment is 1,600.00 and escrow is 450.00, so each payment is 2,050.00. All figures are examples.
| Payment date | Principal | Interest | Escrow | Fees | Balance after |
|---|---|---|---|---|---|
| Balance before the January payment | 248,000.00 | ||||
| 01/01/2026 | 360.00 | 1,240.00 | 450.00 | 0.00 | 247,640.00 |
| 02/01/2026 | 361.80 | 1,238.20 | 450.00 | 0.00 | 247,278.20 |
| 03/01/2026 | 363.61 | 1,236.39 | 450.00 | 0.00 | 246,914.59 |
| 04/01/2026 | 365.43 | 1,234.57 | 450.00 | 0.00 | 246,549.16 |
| 05/01/2026 | 367.25 | 1,232.75 | 450.00 | 0.00 | 246,181.91 |
| 06/01/2026 | 369.09 | 1,230.91 | 450.00 | 0.00 | 245,812.82 |
| 07/01/2026 | 370.94 | 1,229.06 | 450.00 | 0.00 | 245,441.88 |
| 08/01/2026 | 372.79 | 1,227.21 | 450.00 | 0.00 | 245,069.09 |
| 09/01/2026 | 374.65 | 1,225.35 | 450.00 | 0.00 | 244,694.44 |
| 10/01/2026 | 376.53 | 1,223.47 | 450.00 | 0.00 | 244,317.91 |
| 11/01/2026 | 378.41 | 1,221.59 | 450.00 | 0.00 | 243,939.50 |
| 12/01/2026 | 380.30 | 1,219.70 | 450.00 | 0.00 | 243,559.20 |
| Total | 4,440.80 | 14,759.20 | 5,400.00 | 0.00 | |
| Form 1098, box 1 (example) | 14,759.20 |
Two proofs sit in the totals. The balance check: 248,000.00 minus 4,440.80 equals 243,559.20, the December balance. The payment check: twelve payments of 1,600.00 principal and interest come to 19,200.00, and 4,440.80 plus 14,759.20 equals 19,200.00.
Keep the sheet to one loan. A client with a mortgage and a home equity line has two loans, two servicers or two account numbers, and two 1098 forms to check, so give each its own sheet. A refinance midyear ends one loan and starts another: close the first sheet at the payoff and open a second one for the new loan.
Add a row only for a statement that exists. If a month is missing, leave a visible gap row marked "statement not received" rather than estimating the split. The year-to-date figures on the next statement show what the missing month held.
Does the loan statement interest match Form 1098?
The loan statement interest matches Form 1098 when the sheet's interest total for the calendar year equals the amount in box 1 of the 1098 the lender issues for that year. A gap usually comes from a missing statement, a payment received in a different year from its due date, or charges the 1098 counts as interest.
The IRS Instructions for Form 1098 say box 1 reports mortgage interest received during the calendar year, not points, and that interest includes prepayment penalties and late charges unless the late charges are for a specific mortgage service. So a sheet that records a late charge in the Fees column can show a lower interest total than box 1 for a legitimate reason. Note the difference beside the total rather than moving the fee.
Box 2 gives a second check. The instructions say it shows the outstanding principal as of January 1 of the year, or the principal at origination or acquisition for a loan that began or was bought during the year. On the sheet above, that is the opening balance of 248,000.00. A mismatch there points to the wrong opening statement or a loan that changed hands.
Other boxes do not belong in the interest total. Box 5 holds mortgage insurance premiums, box 6 holds certain points, and box 10 can show other information such as real estate taxes paid from escrow. How to read each box on the form itself is covered in the post on the 1098 mortgage interest statement.
This comparison checks the documents against each other. It does not decide how anything is treated on a return. If the totals still differ after the checks above, ask the servicer for a payment history for the year.
Loan statement edge cases: escrow shortages and extra principal payments
Loan statement edge cases are payments that do not follow the schedule: an escrow payment that changes after the servicer's annual review, a principal payment above the scheduled amount, or a payment held as unapplied funds. Each breaks the sheet's steady pattern, and each shows on the statement if you read the breakdown rather than assume it.
Escrow shortages and surpluses
An escrow shortage is, in the words of the Regulation X escrow account rules in § 1024.17, "an amount by which a current escrow account balance falls short of the target balance." A surplus is the opposite. The rule requires the servicer to analyze the escrow account and send an annual escrow account statement.
The rule also limits how a shortage is collected. For a shortage of one month's escrow payment or more, the servicer may leave it alone or require repayment in equal monthly payments over at least 12 months. On the sheet, that shows as a higher escrow figure from the month the new payment starts. Add a Note column, record "escrow change after annual analysis," and keep the annual escrow statement with the loan file.
Extra principal and unapplied funds
A borrower who sends more than the scheduled payment creates a row where the principal column is larger than the pattern predicts. Record the split exactly as the statement shows it. If the statement shows the extra amount as principal, the balance falls by more that month, and every later interest figure is a little lower.
Sometimes the extra money is not applied at once. Regulation Z § 1026.41(d)(3) requires the payment breakdown to show any amount held in suspense or as unapplied funds. Give those amounts their own column so they are not mistaken for principal, and watch for the later statement that applies them.
Loans with no monthly statement
Some mortgages have no monthly statement to convert. Regulation Z § 1026.41(e) exempts small servicers, and it lets servicers of some fixed-rate loans use a coupon book instead. For those loans, build the year from the coupon book, the annual escrow statement and the 1098, and ask the servicer for a payment history if the split is needed.
Schema CPA reads loan statements the same way it reads bank, savings and credit card statements, and exports the rows to Excel or CSV for a sheet like the one above; the document categories it reads are listed on the homepage.