To build a profit and loss from bank statements, put every business statement row for the period into one spreadsheet, label each deposit and payment with an income or expense line, remove transfers, loans and owner draws, then total each line by month. Income minus expenses is the profit for that month, measured by when the money actually moved.
What does a profit and loss from bank statements show?
A profit and loss from bank statements shows money earned and money spent by the business over a period, grouped into lines such as sales, rent and software, with the difference as profit or loss. Because it is built from bank rows, it counts income when it arrived and costs when they were paid.
That timing is what most small owners want. It matches how a sole proprietor on the cash method reports income, and it answers the everyday question: did the business make money this month? Lenders, landlords and some agencies also ask for a profit and loss, often called a P&L, when you are self-employed and cannot show a payslip.
A statement-based P&L has limits worth knowing before you start. It sees only what passed through the accounts you include. Cash takings never deposited are missing. An invoice sent but not yet paid is missing. A yearly insurance premium shows up in one month as a large cost, even though it covers twelve. None of this makes the report wrong, but you should read it knowing how it was made.
Gathering the statements for a profit and loss period
Gathering statements for a P&L means collecting every statement, for every account and card the business uses, covering the whole period with no gaps. A year is the usual period for tax; a quarter or three months is common for a lender. Missing one card statement removes a month of its expenses from the report.
Make a short inventory before you open a spreadsheet:
- List each business account: checking, savings, every credit card, and any payment processor balance such as a PayPal or Stripe account that holds money before paying out.
- For each, note the statement dates you need. Statement cycles rarely match calendar months, so pick the cycles that cover the period start to end.
- Download every PDF or CSV. Save them in one folder named by account and month.
- Tick each one off as its rows reach the sheet.
If your bank offers a CSV export for the full period, use it. If you only have PDFs, CheapBankConvert, which is preparing to launch, turns each statement into CSV or Excel rows and shows the first two pages free before you pay; CheapBankConvert's homepage has the early prices for single statements and bundles.
Sorting deposits and payments into P&L lines
Sorting deposits and payments into P&L lines means giving each row one label from a short list. Income lines might be sales, refunds received and interest. Expense lines might be rent, software, supplies, travel, advertising, bank fees and professional fees. A dozen lines is plenty for a small business.
Put all rows in one table with these columns: date, account, description, amount, and line. Use positive amounts for money in and negative amounts for money out. Credit card statements usually show purchases as positive numbers, so flip their sign as you paste, or card spending will count as income.
Then sort the table by description. The same payee appears month after month, so you can label a block of rows in one go. A year of statements with five hundred rows often has fewer than a hundred distinct payees. Label the big blocks first, then sort by amount and check the largest rows one by one, because they move the report most.
Some rows need judgment. A payment to a hardware store could be supplies or could be personal. If the business account is also used for personal costs, label those rows "Personal" and leave them out of the totals, then make a note to stop mixing the two.
If your sheet is a cash book with a running balance rather than a flat table, the same labels work. A cash book template for a small business shows that layout, and its balance column doubles as a check on missing rows.
Which bank statement rows stay out of the P&L?
Bank statement rows stay out of the P&L when money moved without the business earning or spending it. Transfers between your own accounts, loan money received, loan principal repaid, owner draws, owner top-ups and card payments from checking all belong in a separate "Excluded" line that the totals skip.
The credit card payment is the classic trap. The checking statement shows one payment of, say, 1,200.00 to the card. The card statement shows the forty purchases that made up that 1,200.00. Count both and expenses double. Count only the purchases, because they name the real suppliers.
Sales tax is a second trap if you collect it. Tax a customer paid you is not your income, and the payment you send to the state is not your expense. Record both on their own line so they cancel out.
A quick test confirms the exclusions are complete. Add every Excluded row across all accounts. Transfers between your own accounts should come close to netting to zero, apart from timing at the start and end of the period. What remains should be explained by draws, loans and top-ups. A large unexplained remainder means one side of a transfer is missing, usually because an account was left out.
Monthly P&L totals with a pivot table
A pivot table turns the labelled rows into a monthly P&L in a few clicks: lines down the side, months across the top, amounts summed in the middle. Excel and Google Sheets both have one. Filter out the Excluded and Personal lines and the grand total row becomes net profit per month.
The steps in Excel are short:
- Click inside the table and choose Insert, then PivotTable.
- Drag the line column to Rows.
- Drag the date column to Columns, then group it by month.
- Drag the amount column to Values, set to Sum.
- Use the Rows filter to untick Excluded and Personal.
Arrange the line list so income lines come first and expense lines after. Because money out is negative, the grand total of each column is income minus expenses, which is the profit.
A pivot table refreshes when you add rows, so next month's statements extend the report without new formulas. If you prefer fixed formulas, SUMIFS on the line and the month does the same job cell by cell.
A worked three-month P&L built from statement rows
The table below is a finished P&L for an example consulting business, built from one checking account and one card. All figures are invented for illustration. Excluded rows such as owner draws and the card payment do not appear, because the report filters them out.
| Line (example) | July | August | September | Three months |
|---|---|---|---|---|
| Sales | 6,200.00 | 4,850.00 | 7,400.00 | 18,450.00 |
| Interest received | 3.10 | 3.40 | 3.25 | 9.75 |
| Total income | 6,203.10 | 4,853.40 | 7,403.25 | 18,459.75 |
| Rent | −900.00 | −900.00 | −900.00 | −2,700.00 |
| Software | −148.00 | −148.00 | −172.00 | −468.00 |
| Travel | −310.40 | 0.00 | −622.15 | −932.55 |
| Advertising | −250.00 | −250.00 | −400.00 | −900.00 |
| Professional insurance | 0.00 | −1,140.00 | 0.00 | −1,140.00 |
| Bank fees | −15.00 | −15.00 | −15.00 | −45.00 |
| Total expenses | −1,623.40 | −2,453.00 | −2,109.15 | −6,185.55 |
| Net profit | 4,579.70 | 2,400.40 | 5,294.10 | 12,274.20 |
Read the August column and the insurance line together. Profit dropped by almost half, but nothing went wrong with the business: a yearly premium landed that month. The three-month column smooths that out.
If someone asked you for this report to support an application, also keep the statements it came from. How the Medicaid look-back period uses bank statements is one example of a review where the underlying statements, not just a summary, are requested.
Where a statement-based P&L misleads: unpaid invoices and yearly bills
A statement-based P&L misleads when the timing of cash differs sharply from the timing of the work. Large unpaid invoices make a busy month look empty. Yearly bills make one month look terrible. Prepayments from clients make a quiet month look strong. The rows are right; the story they tell needs a note.
For a short report, write the note in a line under the table: "September includes a client deposit of 3,000.00 for October work." For a longer view, use a three-month or twelve-month total, which absorbs most timing swings.
Owner pay deserves its own note. For a sole proprietor, the money you move to yourself is not on the P&L, so the profit line is larger than what you took home. Anyone reading the report, including you, should know the tax bill comes out of that profit, not out of the draws.
If timing swings matter to you every month, the business may have outgrown a cash-only view. Software that tracks invoices separately from bank rows can show what is owed as well as what was paid. A free bookkeeping app compared with a spreadsheet weighs when that switch is worth it.