Profit and loss spreadsheet template for a small business
A working Excel profit and loss account for a small business or sole trader. Type each sale and each cost as a dated row and choose a category; the statement sheet totals every category for each of twelve months and the year, and works out gross profit, net profit and gross margin. Free, no sign-up and no email address.
What is in it
| Sheet | One row per | What it does for you |
|---|---|---|
| Transactions | Sale or cost | Date, details, invoice or reference number, a category from a dropdown, and the amount. Every amount is entered as a positive number; the category says whether it is income or a cost. |
| Profit and loss | Category | Income, cost of sales and overheads by category across twelve monthly columns and a year column, with total income, gross profit, total overheads, net profit and gross margin worked out. Amounts you have not given a category are shown on their own line. |
How a profit and loss account is laid out
It answers one question: over this period, did the business make or lose money? It starts with income. From that it takes the cost of sales, meaning the costs that exist only because you made those sales, such as materials, stock and subcontractors. What is left is gross profit. From gross profit it takes the overheads, the costs you would pay whether or not you sold anything that month, such as rent, insurance and the phone. What is left is net profit.
Gross margin is gross profit as a share of income. It tells you how much of each pound sold is left to pay the overheads, and it is the figure to watch when supplier prices rise.
How to use it
Set the start date at the top of the Profit and loss sheet to the first day of your year. Then add a row to the Transactions sheet for each sale and each cost. The month a row falls in is decided by its date, so use the date that matches how you keep your books: the date money moved, or the date on the invoice. Pick one and use it for every row.
If you are VAT registered, enter amounts without VAT, because VAT you charge and reclaim is not your income or your cost. If you are not registered, enter what you actually paid and received.
What does not belong on it
Money you take out of the business for yourself as a sole trader, repayments of a loan, and money you put in are not income or costs, so they have no category here. Buying a van or a machine is not an ordinary cost of the month either. Net profit on this sheet is before tax, and the template does not decide what is allowable for tax; that is a question for HMRC's guidance or an accountant. For a tax return, the self-employed income and expenses template uses the return's own headings.
Filling it in without typing
The cost rows come from supplier invoices and receipts: PDFs, scans and phone photos. Invoices to Spreadsheets reads a folder of those into one checked table with the date, supplier, invoice number, net, VAT and total, which you can export to Excel and paste into the Transactions sheet. The category stays yours to choose. Try one invoice free, without an account.
Is the profit and loss template free?
Yes. Download it, use it, change it. No account, no email address, no card.
What is the difference between gross profit and net profit?
Gross profit is income less the cost of sales, the costs that come directly from making those sales. Net profit is gross profit less the overheads, the running costs of the business. The template shows both for every month and for the year.
Do I enter costs as negative numbers?
No. Enter every amount as a positive number. The category you choose places the row under income, cost of sales or overheads, and the statement does the subtracting. A refund you give or receive can be entered as a negative amount in the same category.
Can I change the categories?
Yes. Rename a category on the Profit and loss sheet and in the dropdown on the Transactions sheet, using exactly the same words in both, so the totals still match.
Can I change the year or use calendar months?
Yes. The start date at the top of the Profit and loss sheet is set to 6 April 2026. Change it to the first day of your own year, for example 1 January, and the twelve month columns follow.
Does it work in Google Sheets or Numbers?
Yes. It is a standard .xlsx file using SUM, SUMIFS and EDATE, with an ordinary dropdown. Set your locale to United Kingdom before importing so dates are read day first.
7 days · 50 documents · No card needed
All free tools and templates · Bookkeeping cash book template · Self-employed income and expenses template