Spreadsheets

TDS tracker for freelancers: matching client deductions in Excel

Set up a TDS tracker in Excel for freelancers that logs each client deduction, flags short deductions and totals TDS per client for the year.

Editorial illustration for TDS tracker for freelancers: matching client deductions in Excel

A TDS tracker for freelancers is a table with one row per invoice: client, gross amount, TDS rate, TDS actually deducted, net received and whether the certificate has arrived. Running totals per client for the financial year let you check your records against the tax statement on the income tax portal. The rate sits in an input cell, never inside a formula.

What columns should the TDS sheet have?

  • A: Client
  • B: Invoice number
  • C: Invoice date
  • D: Gross amount the client deducted TDS on
  • E: TDS rate, linked to a settings cell
  • F: Expected TDS, calculated
  • G: TDS actually deducted, typed from the payment advice
  • H: Net received in your bank
  • I: Difference between expected and actual, calculated
  • J: Certificate received - Yes or No
  • K: Cross-check of gross, TDS and net, calculated

On a Settings tab, put the sample rate in B1 and the financial year start and end dates in B2 and B3. Keeping them there means a change of rate, or the start of a new April to March year, is one edit and not a hunt through formulas.

Which formulas do the work?

  • E2: =Settings!$B$1 (overwrite for a client who deducts at a different rate)
  • F2: =D2*E2
  • I2: =G2-F2
  • K2: =IF(ABS(D2-G2-H2)<1, "OK", "Check")

The cross-check works when the client pays you exactly the gross minus TDS. If your invoice carries GST that the client also pays, the bank credit will be higher. Decide with your CA which amount the client calculates TDS on, and enter the net for that same amount in column H, or the check will always say Check.

How do I total TDS per client for the year?

On a Summary tab list each client once in column A, then use:

  • Gross billed: =SUMIFS(Invoices!$D$2:$D$500, Invoices!$A$2:$A$500, A2, Invoices!$C$2:$C$500, ">="&Settings!$B$2, Invoices!$C$2:$C$500, "<="&Settings!$B$3)
  • TDS deducted: the same formula with column G as the sum range
  • Certificates pending: =COUNTIFS(Invoices!$A$2:$A$500, A2, Invoices!$J$2:$J$500, "No")

These give the figures you can place beside the tax statement for the same financial year. Deductions appear there only after the client reports them, so a recent invoice may take a while to show up; that is normal, not a reason to panic.

A worked example with sample clients

Take 10 percent as a sample rate, which your CA will confirm for the kind of work you do. Three invoices:

ClientGrossExpected TDSDeductedNet receivedDifference
Client A, INV-01₹50,000₹5,000₹5,000₹45,000₹0
Client B, INV-02₹80,000₹8,000₹6,000₹74,000-₹2,000
Client A, INV-03₹60,000₹6,000₹6,000₹54,000₹0

Client A has TDS of 5,000 + 6,000 = ₹11,000 for the year so far, and Client B has ₹6,000. The -₹2,000 for Client B is a flag, not a verdict. It may be a different rate for that work, or a mistake. Ask the client for the payment advice and the reason, and write the answer in a Notes column.

What do I do when the certificate has not arrived?

  1. Filter column J for No and sort by client.
  2. Send a short, polite reminder with the invoice number, amount and the date you were paid.
  3. Note the date you asked, so you can see which clients keep needing a nudge.
  4. Tick Yes only when the certificate is in your inbox or folder.

Keeping the sheet honest

  • Enter the TDS from the payment advice or bank narration, not from memory.
  • Do not average clients; every invoice stays on its own row.
  • Keep the certificate files in one folder with the client and quarter in the filename.
  • Compare your totals with the tax statement every quarter and note any gap.

If you also want your ordinary spending in the same picture, SpendixAI can import bank statements, where the net amount from each client will appear as a credit; the TDS layer still belongs in this sheet.

SPENDIXAI JOURNAL

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

Explore your reports