Spreadsheets

Freelancer income and expense tracker in Excel with monthly totals

Set up a freelancer income and expense tracker in Excel with client, invoice, gross, TDS and received columns, and monthly totals using SUMIFS.

Editorial illustration for Freelancer income and expense tracker in Excel with monthly totals

A freelancer tracker in Excel needs two tables and one summary: Income, with client, invoice number, gross, TDS deducted and the amount actually received; Expenses, with category and a business-or-personal flag; and a Month sheet that totals both with SUMIFS. Record what hit your bank on which date, and keep TDS visible.

What columns should the Income table have?

On a sheet named Income, use rows 2 to 300 for entries:

  • A Client, B Invoice number, C Invoice date.
  • D Gross amount invoiced.
  • E TDS deducted, taken from the client's payment note or invoice paperwork.
  • F Amount received, which is what actually reached your bank.
  • G Date received, left blank until payment arrives.
  • H Short-pay check: =IF(G2="","",D2-E2-F2). Anything other than 0 means the client deducted something else or paid less.

Do not enter GST, TDS rates or deadlines here unless your CA has told you what to track. Keep the numbers as shown on your invoices and payment advice, and confirm the treatment with your CA.

What goes in the Expenses table?

On a sheet named Expenses: A date, B description, C amount, D category (software, internet, coworking, travel, equipment), E flag with the words Business or Personal. The flag is the key column. If you pay a phone bill that is partly for work, split it into two rows rather than guessing a share in a formula. Which expenses count as business costs for tax is a question for your CA, so tag by use and let them decide.

For larger purchases such as a laptop or a course, keep the bill in a folder named for the month and write its file name in the description. Come tax time, you will find it in seconds.

How do I build the Month summary with SUMIFS?

On a sheet named Month, put the first day of each month in A2 downwards: 1-May-2026, 1-Jun-2026 and so on. Then:

  • B2, received: =SUMIFS(Income!$F$2:$F$300,Income!$G$2:$G$300,">="&A2,Income!$G$2:$G$300,"<="&EOMONTH(A2,0))
  • C2, TDS on those payments: =SUMIFS(Income!$E$2:$E$300,Income!$G$2:$G$300,">="&A2,Income!$G$2:$G$300,"<="&EOMONTH(A2,0))
  • D2, business expenses: =SUMIFS(Expenses!$C$2:$C$300,Expenses!$D$2:$D$300,"Business",Expenses!$A$2:$A$300,">="&A2,Expenses!$A$2:$A$300,"<="&EOMONTH(A2,0))
  • E2, left after business costs: =B2-D2

Argument order matters: the first range is what you add up, then pairs of range and condition. Every range must be the same size, which is why each uses rows 2 to 300.

Can you show an example with three clients?

Say a freelance designer has these in May, all hypothetical:

ClientInvoiceGross (₹)TDS (₹)Received (₹)Date received
Client AINV-01440,0004,00036,00012 May
Client BINV-01525,000025,0003 Jun
Client CINV-01630,0003,00027,00028 May

The May row of the Month sheet counts only payments dated in May: 36,000 plus 27,000, which is ₹63,000 received. TDS on those two is 4,000 plus 3,000, so ₹7,000. Client B's ₹25,000 lands in June, even though the invoice was raised earlier. Say May's business expenses are software 1,800, internet 1,000 and coworking 3,500, which add up to ₹6,300. Left after business costs: 63,000 minus 6,300 is ₹56,700.

How do I keep track of unpaid invoices?

Add a column I: =IF(G2="",D2-E2,0). This is what you expect to receive for invoices not yet paid. A total at the top, =SUM(I2:I300), shows money you are waiting for. Sort or filter column G for blanks and chase the oldest invoice dates first.

What should I do at year end?

  1. Total gross, TDS and received for April to March.
  2. Reconcile TDS against the statement your CA or the official portal shows, and chase differences with the client.
  3. Share a values-only copy with your CA, with the personal expenses removed.

When you find yourself retyping the same bank lines into the Expenses sheet, SpendixAI can import the statement and categorise rows, and report exports can go to your CA as CSV. The Income sheet with TDS stays valuable as your own record.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports