Sole Trader Income Excel - Free Template

1. Open the Instructions sheet first and check the notes about the intended GST basis, sample information and data-entry approach.
2. Go to Income and replace the sample rows with your own sales. Enter the date, invoice number, client, description and amount excluding GST; check the calculated GST and total.
3. Choose the correct payment status for each invoice, such as Paid, Unpaid or Overdue. Update the status when money reaches your bank account.
4. Open Expenses and enter each business purchase from a receipt, supplier invoice or bank transaction. Keep private spending out of the business expense list.
5. Check the Summary sheet after each weekly or monthly update. Compare the totals with your bank account, sales invoices and expense receipts.
6. Before a GST return or year-end, filter or review the entries for missing dates, duplicated invoice numbers, incorrect GST treatment and unpaid items.
7. Save a dated copy and retain the source invoices, receipts and bank records. Send the completed file and supporting documents to your bookkeeper when your records are ready.

A sole trader usually opens this file when the bank account is busy but the bookkeeping is not yet under control. A self-employed electrician might add several completed jobs each Friday, while a freelance designer may enter three or four invoices after sending them to clients. The point is to capture the transaction while the invoice or receipt is still in front of you.

Useful for weekly bookkeeping

Take the sample income row of $2,200.00 excluding GST. At 15%, GST is $330.00 and the invoice total is $2,530.00. If that invoice is paid, the Payment Status column helps distinguish money received from the $850.00 invoice that is still waiting on the client.

For a Hamilton plumber with 25 invoices a month, entering each job separately is more useful than writing one monthly bank deposit. The invoice number, customer and job description make it possible to trace a $1,150.00 payment back to the right work, even when several customers pay on the same day.

Handy at month-end and 31 March

A sole trader can use the Expenses sheet after reconciling the business account each month. A Christchurch photographer might have $4,800.00 of sales, $1,350.00 of deductible costs and $3,450.00 before other adjustments; the Summary sheet gives a quick sense-check, not a replacement for a complete tax calculation.

Image 1 shows the Income layout clearly: the identifying information sits above the table, and the eight columns run from Date through Payment Status. Image 2 shows the separate Expenses area, so sales and costs do not get mixed. Image 3 brings the totals together, while image 4 provides the instructions to refer to when entering a new row.

Your records need to support the figures in your IR3 individual income tax return and any GST return. Inland Revenue requires business records to be kept for 7 years, so the spreadsheet should be stored with the original invoices, receipts, bank statements and other evidence rather than treated as the only record.

GST registration and the payments basis

GST registration is compulsory when taxable turnover exceeds $60,000 in any 12-month period, although a business can register earlier. The standard GST rate is 15%. The sample Income sheet states Payments Basis, so the timing of a payment matters: an invoice for $2,200.00 plus $330.00 GST is not automatically the same as cash received on the invoice date.

GST returns may be monthly, 2-monthly or 6-monthly. For most growing sole traders, 2-monthly filing is a better discipline than 6-monthly filing because a $12,000 GST bill is less likely to build up unnoticed. Taxable supply information must support the supply, including the supplier details, date, description and amount; keep the actual documents behind the spreadsheet.

Income tax and business identity

A sole trader does not have a separate legal entity from you. You use your personal IRD number for income tax, and an NZBN can identify the business when dealing with customers and suppliers. Your standard balance date is 31 March, and provisional tax may apply after the first assessment creates a sufficient residual income tax liability.

The file's IRD/GST number and NZBN line is a practical identity check, not proof that the details are correct. Confirm the GST treatment of each cost: a $115.00 GST-inclusive purchase may contain $15.00 GST, but private costs, exempt supplies and some mixed-use expenses cannot simply be claimed in full.

The next step is to separate those GST-inclusive figures from items that belong in the company return, which is where an IR4 tax workings sheet becomes useful for reconciling private, exempt and mixed-use expenses.

The most expensive errors are usually small entries repeated for months. One sole trader enters GST-inclusive sales into the Amount (Excl GST) column. A $1,150.00 sale then produces $172.50 GST instead of the correct $150.00, overstating GST by $22.50 on one transaction and $675.00 across 30 similar sales.

When payment status is ignored

Another common failure is leaving every invoice as Paid. A business may show $18,000.00 of invoiced work while only $11,000.00 has arrived in the bank. That makes cash planning unreliable and can hide a customer who is 60 days overdue. Conversely, changing dates to match deposits can damage the audit trail because the invoice date, payment date and GST timing are different facts.

Duplicate invoice numbers cause trouble when a customer queries a bill or a bookkeeper imports the data into accounting software. If INV-2026-014 is used twice, two $900.00 invoices can look like one corrected invoice or a duplicate sale. Keep the numbering sequence intact and use a credit note or clear adjustment rather than deleting history.

Costs that are not business costs

Private petrol, household purchases and personal drawings are often entered as expenses because they appear on the same bank account. A sole trader who records $6,000.00 of private spending as deductible costs could reduce the apparent profit by $6,000.00 and create a poor tax position if the error is carried into the IR3.

Missing receipts are just as painful. At year-end, finding 40 unexplained transactions at an average of $85.00 means chasing $3,400.00 of evidence. The fix is not more complicated formulas; it is entering the transaction while the receipt is available and reconciling the Summary totals to the bank before filing.

A spreadsheet only works when updating it is tied to something you already do. Set aside 20 minutes every Friday after checking the business bank account. Enter new sales in Income, add receipts to Expenses and update payment statuses while the week is still easy to remember.

Use a fixed checking sequence

• Start with the bank statement and tick off paid invoices rather than guessing from memory.
• Check that every sales amount is GST-exclusive before accepting the 15% calculation.
• Look for blank dates, duplicate invoice numbers and expenses with no supporting receipt.
• Review the Summary sheet at month-end and save a copy such as SoleTrader_2026-01.xlsx.

Keep the sample row until you understand the columns, then replace it carefully rather than inserting random fields. The pale yellow input formatting is a useful visual cue: type into the intended entry cells and avoid overwriting headings or formulas. If you add rows, copy the existing row's formatting and formulas so the Summary remains complete.

Know when Excel has reached its limit

This template is sensible for a straightforward sole trader with perhaps 20 to 100 transactions a month. If you have 500 sales, several bank accounts, stock, payroll or regular GST adjustments, manual checking becomes a risk. At 300 transactions a month, even 2 minutes per entry is 10 hours of admin each month.

Move to Xero or MYOB when bank feeds, recurring invoices, receipt capture and GST filing will save more time than the software costs. Keep this workbook as a useful backup or planning tool, but do not run two competing ledgers indefinitely: choose one source of truth and reconcile it regularly.

That same single-source approach also makes it easier to keep a separate tax instalments tracker aligned with your year-end numbers.