A useful freelancer income tracker shows what clients owe, what they have actually paid, and what arrived in your bank account. Those are different numbers. Combining them in one “income” column can make an unpaid invoice look available to spend or make processing fees disappear.
This guide sets out a spreadsheet structure you can build in Google Sheets or Excel. It focuses on recording and checking transactions. For future payment timing, use a cash calendar or forecast alongside it. A tracker is a recordkeeping aid, not a substitute for tax records or an accounting system suited to your business.
Example note: All clients, invoice identifiers, and amounts below are fictional US-dollar illustrations. They demonstrate real-world recording problems without claiming actual client results.
Keep three totals separate
Invoiced amount is the amount requested from a client. Payments applied is the money credited against that invoice. Net cash deposited is what reaches the receiving account after any withheld processing fees. The totals may match for a straightforward bank payment, but a tracker needs to handle cases where they do not.
An invoice for $1,000 does not become $2,000 of income because you list both the invoice and its payment. The invoice table tracks the amount owed. The receipt table records settlement. Monthly cash summaries should use the receipt records rather than adding invoices and receipts together.
For the underlying distinction, read tracking invoices versus payments received. This article turns that distinction into a practical spreadsheet layout.
Sheet 1: invoices
| Column | Purpose |
|---|---|
| Invoice ID | A unique, stable identifier |
| Client and project | Identify the payer and work |
| Issue and due dates | Preserve contractual timing |
| Invoice amount | Total requested, before payments |
| Payments applied | Total linked receipts credited to the invoice |
| Credits or adjustments | Approved reductions with explanatory notes |
| Outstanding amount | Invoice amount − applied payments − credits |
Keep identifiers consistent. “INV-104” and “Inv 104” can look similar while behaving as different text values in a formula. Choose one format and retain it. Add a separate project identifier when one project has several milestone invoices.
Sheet 2: actual receipts
Use one row per payment event. Record the invoice ID, date received, gross client payment, withheld fee, net deposit, account, and a bank or processor reference. If one payment covers multiple invoices, split its allocation across invoice rows and retain a shared payment reference. Check that allocated amounts add to the actual payment.
For a straightforward payment with no withholding or currency conversion, net deposit equals gross payment minus processing fee. Where a processor batches several payments, add a payout reference and reconcile the whole batch. Do not force every bank deposit to represent exactly one invoice.
Worked example: partial payments and fees
A fictional independent editor sends three invoices totaling $4,500. The first two receive payments this month; the third has not been paid. A fee is withheld from the second client's card payment.
| Invoice | Invoiced | Payments applied | Outstanding |
|---|---|---|---|
| INV-101 | $1,800 | $1,800 | $0 |
| INV-102 | $1,500 | $900 | $600 |
| INV-103 | $1,200 | $0 | $1,200 |
| Total | $4,500 | $2,700 | $1,800 |
| Receipt | Gross payment | Fee withheld | Net deposit |
|---|---|---|---|
| INV-101, first installment | $900 | $0 | $900 |
| INV-101, second installment | $900 | $0 | $900 |
| INV-102, partial payment | $900 | $27 | $873 |
| Total | $2,700 | $27 | $2,673 |
The client payments total $2,700, while the bank receives $2,673. The $27 difference is a recorded fee, not an unpaid client balance. Outstanding invoices total $1,800: $600 on INV-102 plus $1,200 on INV-103. These checks explain why a single total labeled “earned” is too ambiguous for practical cash planning.
Add formulas only after defining the columns
Suppose the Receipts sheet has invoice IDs in column A and gross payments in column D. If the invoice ID is in cell A2 of the Invoices sheet, =SUMIF(Receipts!A:A,A2,Receipts!D:D) totals payments applied to that invoice. This assumes one allocation row per invoice and no duplicated receipts.
If invoice amount is E2, applied payments F2, and credits G2, the outstanding formula is =E2-F2-G2. Investigate negative balances rather than hiding them with a formula that automatically changes them to zero. A negative figure could represent an overpayment, a credit, or a duplicate entry.
With gross receipt in D2 and withheld fee in E2, net cash is =D2-E2. A separate control cell can compare total net receipts against matched bank deposits. Use dates stored as actual date values, numeric amount cells, and consistent currency formatting. Spreadsheet locale settings can affect date interpretation and formula separators.
Build a small monthly summary
Summarize gross payments received, withheld fees, net deposits, and outstanding invoice balances separately. Also show cash received by client. If one client supplies most receipts, that concentration is useful planning information, even if the monthly total looks healthy.
Do not describe net deposits as net profit. Profit also depends on relevant expenses and the accounting approach being used. Likewise, an owner transfer is not a new client payment. A spreadsheet intended for tax preparation needs professional review where your situation requires it. The IRS Self-Employed Individuals Tax Center is an official starting point for US tax information.
Reconcile the tracker every week
- Match each new receipt to a bank or processor reference.
- Check partial payments against the correct invoice.
- Record fees and refunds without deleting the original transaction history.
- Review outstanding balances and disputed items.
- Move only verified receipt information into actual cash totals.
Schedule this inside your weekly freelancer money review. When a receipt date changes, update the cash flow calendar separately. A correct ledger can coexist with an outdated forecast, so both need attention.
Handle deposits, refunds, and currencies carefully
A deposit received before work is completed should remain identifiable as a project deposit. Its cash arrival does not tell you the amount safely available for personal spending: future delivery costs, refund terms, and other obligations may still matter. Retain the agreement and the payment trail.
For refunds, add a dated refund record connected to the original payment rather than rewriting the original amount. For different currencies, retain the original currency and amount, conversion details, fees, and actual USD proceeds. Adding euros and dollars in one amount column without conversion produces a meaningless total.
Key takeaways
- Use separate invoice and receipt tables connected by identifiers.
- Keep gross payment, processing fee, and net deposit visible.
- Record partial payments as separate events.
- Reconcile before using tracker totals to make spending decisions.
Frequently asked questions
Can I use Google Sheets instead of accounting software?
A spreadsheet can support a simple tracking workflow. It may become insufficient when you need complex taxes, payroll, inventory, multiple entities, or stronger controls. Choose a system appropriate to those requirements.
Should I enter the invoice date or payment date?
Keep both in their respective tables. Invoice dates help manage billing; actual receipt dates show when payment occurred. They answer different questions.
How do I record a client paying half an invoice?
Add the payment to the receipt table and link it to the invoice ID. The invoice remains partly outstanding until further payments or approved credits settle the remainder.
Source and scope
The CFPB toolkit provides income-tracking resources. The spreadsheet structure and worked examples above are original educational illustrations; they do not determine tax treatment.