Extracting Structured Fields From a Mortgage Amortization Schedule

An amortization schedule is the rare extraction target that is fully determined by three inputs. If you have the original principal, the rate and the term, you can generate the whole table yourself and use it as ground truth — which turns a 360-row table into a problem with an answer key.

What a row means

Each row of a level-payment schedule is one period, and the five columns are always the same thing even when they are labelled differently: payment number, payment amount, the interest portion, the principal portion, and the balance remaining afterwards. The mechanism is one sentence: interest is charged on the balance you owed at the start of the period, and whatever the payment does not spend on interest reduces the balance.

That gives two per-row identities before you have looked at any document:

interest[n]  = balance[n-1] x periodic_rate
principal[n] = payment - interest[n]
balance[n]   = balance[n-1] - principal[n]

The first ambiguity to resolve on any real schedule is whether the balance column is beginning or ending balance. Both conventions are printed, the header rarely disambiguates (“Balance” is not a specification), and getting it backwards shifts your whole check by one row. Resolve it from row one rather than from the header: if the first row’s balance equals the original loan amount, it is a beginning balance; if it equals the original amount minus the first principal portion, it is an ending balance. That is a two-line determination and it is more reliable than any amount of prompt engineering about column names.

Recomputing the schedule from three numbers

Take a synthetic loan: $300,000 principal, 6.00% nominal annual rate, 30-year term, monthly payments. The periodic rate is the nominal annual rate divided by twelve, and the number of periods is 360:

i = 0.06 / 12 = 0.005
n = 30 x 12   = 360

M = P x i / (1 - (1 + i)^-n)

(1.005)^360 = 6.0225752
(1.005)^-360 = 1 / 6.0225752 = 0.1660419
1 - 0.1660419 = 0.8339581

M = 300,000 x 0.005 / 0.8339581
  = 1,500 / 0.8339581
  = 1,798.6516...
  = 1,798.65 per month

If the payment printed on the extracted schedule is 1,798.65, the three header values you extracted are mutually consistent and you may now check every row against a schedule you generate yourself. If it is not, stop — one of principal, rate or term was misread, and validating rows against a wrong generator is worse than not validating them, because it produces 360 confident failures pointing at the wrong thing.

Note what the formula assumes, because schedules exist that violate each assumption: level payments, monthly compounding at exactly one twelfth of the nominal rate, no escrow inside the payment, and no additional principal. Canadian mortgages, for instance, are conventionally compounded semi-annually, which makes the periodic rate the sixth root of one plus half the nominal rate rather than a twelfth of it, and the payment comes out different. Extract the compounding convention if the document states it and treat a payment mismatch as a signal to check that assumption before assuming a misread.

Three rows, checked

Here are the first three periods derived from the numbers above, with each figure rounded to cents at the point it is printed:

row 1
  interest   = 300,000.00 x 0.005 = 1,500.00
  principal  = 1,798.65 - 1,500.00 =   298.65
  balance    = 300,000.00 - 298.65 = 299,701.35

row 2
  interest   = 299,701.35 x 0.005 = 1,498.50675 -> 1,498.51
  principal  = 1,798.65 - 1,498.51 =   300.14
  balance    = 299,701.35 - 300.14 = 299,401.21

row 3
  interest   = 299,401.21 x 0.005 = 1,497.00605 -> 1,497.01
  principal  = 1,798.65 - 1,497.01 =   301.64
  balance    = 299,401.21 - 301.64 = 299,099.57

Any extracted row can be checked this way against the row before it, which is the row-by-row test worth wiring into validation. The tolerance should be one cent, not zero: whether a generator rounds the interest before subtracting it or carries full precision and rounds only the printed value changes the last cent on many rows and compounds slowly down the table. A tolerance of a cent per row absorbs the convention difference; a drift of dollars by row 200 means the two schedules are genuinely different loans.

Checks that catch a dropped row

The characteristic failure on this document has nothing to do with arithmetic. A 360-row schedule spans six or seven pages, every page repeats the column header, and every page boundary is a place to lose a row or gain a phantom one from the header. Three checks catch it and none needs the loan parameters:

• Sequence continuity. Payment numbers must run 1 to n with no gaps and no repeats. This is the cheapest check in the file and it catches the majority of page-break losses outright.
• Balance chaining. Row n’s beginning balance must equal row n−1’s ending balance. A dropped row leaves a discontinuity exactly the size of the missing principal payment, and a duplicated row leaves a zero-progress step.
• Column totals. The principal column must sum to the original principal, and the payment column must equal the principal column plus the interest column. On the synthetic loan, 360 payments of 1,798.65 total 647,514.00, of which 300,000.00 is principal and 347,514.00 is interest — approximately, because the final payment is trued up by a few dollars to land the balance exactly on zero. Expect the printed total to differ from 360 times the payment by less than one payment; a difference larger than that is a missing row, not rounding.

The final balance is the other free check: the last row must end at 0.00, and a schedule that ends at 4,127.19 has lost rows somewhere regardless of how clean each individual row looks. Layout problems of this kind are their own subject — columns that do not align between pages and merged header cells are both common on printed schedules — and the point of the arithmetic checks is that they catch the damage whatever caused it.

Rows that legitimately disagree

Before treating a mismatch as an extraction error, rule out the four reasons a correctly-extracted row disagrees with a generated one:

• Escrow is inside the payment column. Many servicer schedules print the total monthly obligation — principal, interest, taxes and insurance — under a header that just says “Payment”. Then principal plus interest is less than the payment, by design, every row. If a schedule has a separate escrow column, the check is principal + interest + escrow = payment; if it does not and the identity fails by a constant amount on every row, that constant is the escrow.
• Extra principal payments. A schedule generated with a $200 monthly overpayment has a shorter term and rows that no standard generator reproduces. The tell is that the payment column is constant but the principal column jumps at the row where the overpayment starts.
• The final payment. Almost always a different amount from every other row, because it absorbs accumulated rounding. A validator asserting a constant payment fails on exactly one row out of 360, which is annoying enough that people disable the check entirely rather than exempting the last row. Exempt the last row.
• An adjustable rate. After the first adjustment the payment changes and everything downstream is recomputed against the remaining term. The schedule is still internally consistent; it is just not one annuity. Detect it as a change in the payment column and re-derive from the row where it changes, rather than declaring the table corrupt.