A mortgage lender usually asks for your recent bank statements, often the last three months, sometimes six, and more if you are self-employed. Send the lender the PDFs your bank gives you, exactly as they are. A lender or broker checks that statements are genuine, and a spreadsheet or an edited PDF is not evidence.
A spreadsheet is for you. It lets you read your statements the way an underwriter does, before they do, while there is still time to explain something or to wait a month.
What a lender looks for
Lenders differ, but they read the same things in a statement:
- Income. Your salary arriving, from the employer on your payslips, on the day you would expect. For the self-employed, the money coming in.
- Committed outgoings. Loans, credit cards, car finance, childcare, maintenance and other regular payments, which come off what you can afford.
- Spending. Everyday spending as a share of income.
- The overdraft. How often the account is overdrawn, and how deep.
- Warning signs. Returned direct debits, payday loans, gambling, and payments to buy-now-pay-later firms or debts the application does not mention.
Every one of these is easier to see in a spreadsheet than in thirty pages of PDF.
Turn the statements into one sheet
- Download the statements from your online banking as PDFs.
- Upload each one to BankPDFtoXLS. Page one converts free, with no email and no card, so you can check the columns first.
- Download the Xero / QuickBooks file from your list of statements: one row per transaction, the date in full, and one signed amount, money in positive.
- Paste the files into one sheet in Excel, under one header row.
| Date | Description | Amount |
|---|---|---|
| 01/08/2024 | BGC EMPLOYER LTD SALARY | 2650.00 |
| 01/08/2024 | DD CAR FINANCE CO | -289.00 |
| 02/08/2024 | DD CREDIT CARD CO | -150.00 |
| 05/08/2024 | CARD PAYMENT SUPERMARKET | -64.20 |
The rows above are an example, not a real statement.
Read it the way an underwriter does
Add a Category column and mark each row: Salary, Loan, Card, Finance, Bills, Spending. Then:
- Total each month. Select the data, then Insert > PivotTable, with Category in Rows, the month in Columns and Amount in Values. Income less committed outgoings each month is the figure affordability starts from.
- Find every regular payment. Sort by Description. The same payee every month is a commitment, and it should be on your application.
- Find the words a lender searches for. Filter the Description for gambling sites, short-term lenders, "unpaid" or "returned". If something is there, you can explain it to your broker before the lender asks.
- Watch the balance. The statement's Excel download keeps your bank's Balance column. The lowest balance each month shows how close the account runs to its overdraft.
What DD, BGC and FPI mean helps with a description that is only a code.
Check the conversion
The Excel download has a Ledger column that checks each balance against
the row before it. Filter it for break and compare those rows with the PDF,
so that a misread figure does not send you worrying about a payment that did
not happen.
What this does not do
It does not tell you what a lender will offer. Lenders set their own affordability rules, and a broker knows which lender suits your case. What the spreadsheet gives you is a clear view of your own statements, so the questions come from you, not from the underwriter.