Loan Amortization Schedule PDF to Excel, Checked

To convert a loan amortization schedule PDF to Excel, extract the payment table into one sheet, delete the headings repeated on each page, and keep payment number, date, payment, interest, principal and balance as number columns. Then recompute each row from the previous balance and the rate. Any row the formula disagrees with was misread.

What a loan amortization schedule PDF holds

A loan amortization schedule is a table with one row per scheduled payment. Each row shows the payment number or date, the payment amount, how much of it is interest, how much reduces the principal, and the balance left afterwards. A 30-year monthly loan has 360 rows. Above the table sit the loan terms that the whole schedule depends on.

The header numbers to copy first

Before touching the table, copy these into their own cells at the top of the sheet:

• Loan amount, the starting principal.
• Annual interest rate, as printed, such as 6.000%.
• Term, in months or years.
• Payment frequency, monthly in most consumer loans, sometimes biweekly.
• Scheduled payment, the fixed principal-and-interest amount.
• First payment date.

These six values let you rebuild any row of the schedule. They are also what you will check the extracted table against, so read them carefully from the PDF rather than from a summary email.

Columns you may also see

Some lenders add columns: cumulative interest paid, cumulative principal paid, an escrow amount, or an extra principal column. Cumulative columns are handy but redundant, since they can be rebuilt with a running SUM. Escrow is money for taxes and insurance held by the lender, and it is not part of the amortization at all. Keep it in its own column so it never gets added into principal.

Page breaks in a long amortization schedule

A long amortization schedule runs across many pages, and each page usually repeats the column headings and adds a page number. Some lenders also print a yearly subtotal after every twelfth row. Extract all pages into one sheet, then remove every row that is not a payment, so the table has exactly one row per scheduled payment.

Removing the repeated rows

Filter the payment number column for anything that is not a whole number. Heading text such as "Pmt #", page footers such as "Page 4 of 12", and yearly totals labelled "Year 3 total" all show up in that filter. Delete them. Then check that the payment numbers run from 1 to the term without a gap. A quick test is =MAX(A:A)=COUNT(A:A) on the payment number column. It returns FALSE when a single payment number is missing or repeated, assuming the numbers start at 1. A gap and a repeat together cancel out, so also sort the column and scan for repeats.

Yearly subtotals are worth keeping elsewhere

The yearly interest subtotals the lender prints are useful for checking, especially if you need yearly interest figures. Copy them to a separate small table before deleting them from the payment rows. Later, a SUMIFS on your clean rows by year should reproduce each printed subtotal.

Rows that split at a page boundary

Occasionally one payment row is cut by a page break, with the date at the foot of one page and the amounts at the top of the next. It looks like two half-empty rows. Join them before you delete anything, or the check at the end of the next section will show that payment as missing.

Which formula checks every amortization row?

Every amortization row follows from the row before it. Interest is the previous balance times the periodic rate, rounded to cents. Principal is the payment minus that interest. The new balance is the previous balance minus that principal. Put those three formulas next to the extracted columns and compare. A non-zero difference marks a row that was misread.

Worked example: the first three rows

The loan below is an example: 200,000.00 at 6.000% a year, 360 monthly payments. Excel's =PMT(6%/12,360,-200000) gives a payment of 1,199.10. The periodic rate is 0.5% a month. As printed, the first three rows read:

Each figure is reproducible. Row 2 interest is 199,800.90 times 0.005, which is 999.0045, rounded to 999.00. Principal is 1,199.10 minus 999.00, which is 200.10. Balance is 199,800.90 minus 200.10, which is 199,600.80.

The check columns

With the extracted data in columns A to E starting in row 2, the rate in cell K1 as 0.06, and the loan amount in K2, add these in row 2 and fill down:

• Interest check, column F: =ROUND(IF(A2=1,$K$2,E1)*$K$1/12,2)-C2
• Principal check, column G: =(B2-C2)-D2
• Balance check, column H from row 3 onward: =(E2-D3)-E3

Each should be zero on every row. Filter each check column for non-zero values. On a clean extraction there are none.

Reading a failed row

A failure in one row only, with the next row fine, is a misread digit in that row. A failure that starts at one row and continues to the end means the balance itself was misread at that point, so every later interest figure computed from your extracted balance disagrees. Fix the balance on the first failing row and the rest usually clears. Also check the whole-schedule tie: the sum of the principal column should equal the loan amount, and the last balance should be zero.

Why doesn't the amortization schedule match my loan payment history?

An amortization schedule shows the loan as planned at the start. A payment history shows what actually happened. They part ways as soon as anything changes: an extra principal payment, a late payment, a rate reset on an adjustable loan, or a payment that includes escrow. The schedule is not wrong; it is a forecast.

Extra payments

An extra principal payment lowers the balance sooner than planned, so every later row of interest is smaller. The printed schedule does not know this. If you want a schedule that reflects extra payments, use the rebuild method in the last section and add an extra principal column that feeds the balance formula.

Adjustable rates

On an adjustable-rate loan, the original schedule only holds until the first reset. After that, the lender recalculates the payment. A schedule PDF from the closing documents will drift from reality from that date. Ask the lender for a current schedule, or rebuild from the reset date with the new rate and the balance on that date.

Escrow in the payment

The monthly amount you pay often includes escrow for property tax and insurance on top of principal and interest. The amortization schedule usually shows only principal and interest. If you compare it with your bank statement, the bank shows the full payment. The difference is escrow. The property tax itself is billed separately, and turning property tax bill PDFs into parcel and payment rows shows how to track that side. Housing costs such as power and water follow a similar pattern, covered in converting utility bill PDFs into monthly usage and charges.

Rounding and the final payment on an amortization schedule

The final payment on an amortization schedule is often a few cents or a few dollars different from the others. That is expected. Because each interest figure is rounded to cents, the balance drifts slightly from the exact value, and the lender adjusts the last payment so the balance lands on zero.

How to check the last row

On the last row, the principal should equal the previous balance exactly, so the new balance is zero. The payment is that principal plus the final month's interest. Your check formulas still pass on the last row, because they use the payment printed on that row. What differs is the payment itself, which will not equal the scheduled payment at the top of the sheet. That difference is fine. Confirm that the last balance is 0.00.

Lenders that round differently

Not every lender rounds interest to the cent each month. Some carry more decimal places and round only what they print. Then your ROUND-based interest check will show differences of one cent here and there. If the differences are all one cent or less and the balance still lands on zero, the schedule is consistent; it just uses another rounding method. Change the check to tolerate one cent: =ABS(...)<=0.01.

Daily interest loans

Some loans charge interest by the day rather than by the month, so the interest varies with the number of days between payments. A schedule for such a loan will not pass a monthly-rate check at all. Recognise it by interest figures that rise and fall with month length. For those, check only principal plus interest equals payment, and the balance chain.

Rebuilding the amortization schedule in Excel instead of converting it

For a standard fixed-rate loan, rebuilding the schedule in Excel from the six header numbers is often faster than extracting hundreds of rows. Excel has the functions for it, and a computed schedule has no reading errors. Convert the PDF when you need the lender's exact figures, or when the loan has features you cannot model.

A rebuild in four columns

With the rate, term and loan amount in cells at the top:

1. Payment: =PMT(rate/12, term, -amount) in one cell, referenced by every row.
2. Interest: previous balance times rate divided by 12, rounded to cents.
3. Principal: payment minus interest.
4. Balance: previous balance minus principal.

Fill down for the full term. Excel's IPMT and PPMT functions give the interest and principal for any single period directly, without the chain, which is handy for spot checks.

When to convert anyway

Extract the PDF when the schedule is part of a legal record, when the lender's rounding differs from yours, or when the loan includes balloon payments, interest-only periods or fees that the header does not spell out. Then compare the extracted table with your rebuild. Where they agree, you understand the loan. Where they differ, you have a question for the lender.

Schedules on paper

Old schedules are sometimes only on paper. A phone photo works for a page or two; copying text from a photo on Android into a spreadsheet covers that route, and the check formulas above catch the digit errors photo recognition makes.

The bank side of the loan

Your bank statement shows each loan payment leaving the account, which is the other half of a payment history. FastBankConvert is being built to turn those statement PDFs into Excel or CSV, and to flag a statement whose balances do not add up. It is getting ready to open; sign up on the get-started page for an invite.