DEV Community

Ashish Srivastava
Ashish Srivastava

Posted on AI-assisted

A freelance income tracker spreadsheet: invoices, unpaid clients, and money set aside for tax

#excel #freelancing #spreadsheets #productivity

This article was written with AI assistance and reviewed before publishing.

When you freelance, "how much did I make this month?" is surprisingly hard to answer. Some invoices are paid, some are late, and some clients pay in a different currency. Here's a plain spreadsheet that answers it, plus two more questions: who still owes me? and how much should I put aside?

It works in Excel or Google Sheets.

1. The Income tab: one row per invoice

Column Example
Invoice # INV-031
Client Acme Ltd
Project Landing page
Invoice date 2026-10-05
Due date =D2+30
Amount 1,200.00
Currency USD
Paid date
Status
Days overdue

Status:

=IF(H2<>"","Paid",IF(TODAY()>E2,"Overdue","Open"))
Enter fullscreen mode Exit fullscreen mode

Days overdue:

=IF(I2="Overdue",TODAY()-E2,"")
Enter fullscreen mode Exit fullscreen mode

Sort by Days overdue, largest first. Those are the reminder emails to send today.

2. Handling more than one currency

Add a Rate column (how much of your home currency one unit is worth on the day you're paid) and a Home amount column:

=IF(H2="","",F2*K2)
Enter fullscreen mode Exit fullscreen mode

Fill in the rate from your bank statement once the money arrives. Then your totals reflect what actually landed in your account, not an estimate.

3. Monthly summary

On a Summary tab, put the first day of each month in A2:A13, then:

Earned (paid):  =SUMIFS(Income!L:L,Income!H:H,">="&A2,Income!H:H,"<="&EOMONTH(A2,0))
Invoiced:       =SUMIFS(Income!F:F,Income!D:D,">="&A2,Income!D:D,"<="&EOMONTH(A2,0))
Still owed:     =SUMIFS(Income!F:F,Income!I:I,"<>Paid")
Enter fullscreen mode Exit fullscreen mode

A simple column chart of "Earned" by month shows your real income trend, including the slow months.

4. A "set aside" line (not tax advice)

Lots of freelancers move a fixed percentage of every payment into a separate account. Put your percentage in one cell (for example Summary!B1 = 25%) and add:

Set aside this month: =B2*$B$1
Enter fullscreen mode Exit fullscreen mode

The right percentage depends on where you live and how you're set up, so ask a local accountant. The point of the sheet is just to make the habit automatic.

5. Expenses (optional)

Add an Expenses tab with Date, Description, Category, and Amount. Then you get:

Profit this month = Earned − SUMIFS(Expenses!D:D, Expenses!A:A, ">="&A2, Expenses!A:A, "<="&EOMONTH(A2,0))
Enter fullscreen mode Exit fullscreen mode

Weekly 5-minute routine

  1. Add any new invoices.
  2. Mark payments received, with the date and rate.
  3. Send reminders to everything marked Overdue.
  4. Move the set-aside amount.

That's all there is to it. If you build one, I'd like to hear what columns you added.

Top comments (1)

Collapse
 
keelsheet profile image
Keelsheet •

Nice that the summary keys off the paid date. For most US freelancers on the cash method, that's the date that counts for tax too, so the sheet already lines up with how the IRS sees your income.

If you're in the US, one column worth adding is the estimated tax period. The IRS quarters aren't calendar quarters: Jan to Mar is due Apr 15, Apr to May is due Jun 15, Jun to Aug is due Sep 15, and Sep to Dec is due Jan 15. Something like =IF(H2="","",IF(MONTH(H2)<=3,"P1",IF(MONTH(H2)<=5,"P2",IF(MONTH(H2)<=8,"P3","P4")))) and a SUMIFS on it tells you what to send each deadline.

I'd also run the set-aside percentage on profit (earned minus expenses) rather than on gross payments, since that's what self-employment and income tax are figured on. And if your income is lumpy, paying each period based on what actually landed in it is allowed. It's called the annualized installment method (Schedule AI on Form 2210), and it keeps a slow first half from turning into an underpayment penalty.