Spreadsheets

TDS gross to net calculator in Excel for a freelance invoice

Set up a TDS calculator in Excel for a freelance invoice: net receivable from the gross, and the reverse formula for the gross needed to receive a target.

Editorial illustration for TDS gross to net calculator in Excel for a freelance invoice

With the TDS rate in a settings cell, TDS is =ROUND(Gross*Rate, 2) and the net you receive is =Gross-TDS. To work backwards and find the gross you must invoice to receive a target net, use =Target/(1-Rate). The rate is whatever your client tells you they will deduct; the sheet never assumes one.

Settings cell and the two forward formulas

  1. B1 TDS rate: type the rate your client will deduct, as a percentage.
  2. Row 4 headings: A Invoice, B Gross amount, C TDS deducted, D Net receivable.
  3. C5: =ROUND(B5*$B$1, 2). Round the way your client does; many round to whole rupees, so ask.
  4. D5: =B5-C5.
  5. Copy both formulas down. Total each column at the foot with =SUM.

Because the rate lives in one cell, a change of rate or a different client only needs one edit. If clients deduct at different rates, give each invoice row its own rate column and replace $B$1 with that column.

What do the numbers look like?

Say a gross invoice of ₹50,000. These rates are only to show the arithmetic; type the one that applies to you:

Rate in B1TDS deductedNet receivable
5%₹2,500.00₹47,500.00
10%₹5,000.00₹45,000.00
20%₹10,000.00₹40,000.00

How much should I invoice to receive a target amount?

Do not simply add the rate on top. Say you want ₹60,000 in hand and the rate is 10%. Invoicing ₹60,000 x 1.10 = ₹66,000 looks right, but TDS on that is ₹6,600, so you receive ₹59,400, short by ₹600. The deduction is taken from the larger figure, so you have to divide: =B2/(1-B1), where B2 is the target net. That gives ₹66,666.67. Check: TDS is ₹6,666.67 and ₹66,666.67 - ₹6,666.67 = ₹60,000.00.

Can I track what each client deducted?

Add three more columns to your invoice list: E TDS shown by the client, F Net actually received, and G Difference. Then:

  • E: type the TDS the client says they deducted, taken from their remittance advice or certificate.
  • G: =C5-E5, which should be zero if your rate matches theirs.
  • H: =D5-F5 to compare the net you expected with the credit in your bank account.
  • Colour any row where G or H is not zero so that you chase it while the invoice is fresh.

Why does the net received sometimes differ?

  • The client used a different rate or a different base amount.
  • Rounding to whole rupees or to paise differs between your sheet and theirs.
  • A bank charge or a deduction for something else was netted off.
  • Part of the invoice was paid on a different date, or the payment is still pending.

The statement of the account where the money lands is the final witness. Compare the credit with column D, not with your memory of what the client said.

Can one sheet handle several clients?

Yes. Make a small client table in H2:I10, with the client's name in H and the rate they deduct in I. In the invoice list, add a client column and use =VLOOKUP(A5, $H$2:$I$10, 2, FALSE) to fetch the rate into a rate column. Then TDS is =ROUND(B5*E5, 2), where E5 holds the looked-up rate. A change to one client's rate is then a single edit in the client table.

Before you send an invoice

  1. Ask the client whether they will deduct TDS and at what rate.
  2. Put the rate in B1 and look at the net.
  3. Decide whether your quote is the gross or the net, and say so in writing.
  4. Keep the certificate or statement they send, so you can reconcile later with the figures on your tax portal.

TDS rules, rates and thresholds change and differ by the nature of the payment, so confirm the current position with your CA or the official source. SpendixAI is useful for the other half of the job: importing the bank statement shows the credits that actually arrived, so you can tick them against the Net column here.

SPENDIXAI JOURNAL

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

Explore your reports