Spreadsheets

Advance tax set-aside tracker in Google Sheets for self-employed

Build an advance tax set-aside tracker in Google Sheets that reserves a percentage of every payment and shows the reserve against each due date you enter.

Editorial illustration for Advance tax set-aside tracker in Google Sheets for self-employed

An advance tax set-aside tracker in Google Sheets takes every payment you receive, puts away a percentage you choose in one cell, and shows a growing reserve. A small table of due dates that you type in yourself shows whether the reserve covers each one. No rate or date is hard-coded.

What should the sheet do, and what should it leave to your CA?

The sheet is a savings habit in spreadsheet form. It does not decide how much tax you owe, which quarters apply to you or what the due dates are. Your CA or the official source gives you those. The sheet only makes sure money is waiting when the payment has to be made.

How do I set up the tabs?

  • Settings: the set-aside percentage in B1, and a due dates table from A5 with a label (Q1 to Q4) and the date you type in
  • Log: one row per payment received - date in A, client in B, amount in C, set-aside percentage in D, set-aside amount in E
  • Paid: one row per payment you have made to the tax department - date in A, amount in B, reference in C

Ask your CA whether to apply the percentage to the gross amount or the net amount after any TDS the client held. A client who deducts TDS has already sent part of the tax, so the amount reaching your bank is already reduced.

Which formulas are needed?

  • Log!D2: =Settings!$B$1, so every row picks up the percentage
  • Log!E2: =C2*D2
  • Log!F2, running reserve: =SUM($E$2:E2)
  • Reserve left today: =SUM(Log!$E$2:$E$500)-SUM(Paid!$B$2:$B$100)
  • Next due date: =IFERROR(1/(1/MINIFS(Settings!$B$5:$B$8, Settings!$B$5:$B$8, ">="&TODAY())), "none")

The last formula returns the nearest future date, and the 1/(1/x) wrapper turns the zero that MINIFS returns for no match into an error that IFERROR catches. Format the result as a date.

How do I see the reserve at each quarter?

In the Settings table, add three more columns beside each due date:

  • C: Set aside up to this date: =SUMIFS(Log!$E$2:$E$500, Log!$A$2:$A$500, "<="&B5)
  • D: Paid up to this date: =SUMIFS(Paid!$B$2:$B$100, Paid!$A$2:$A$100, "<="&B5)
  • E: Reserve left at this date: =C5-D5

A positive number in E means the reserve has more than what you have paid out so far; whether it covers the next payment is for your CA to confirm. Add conditional formatting to column E so a zero or negative value turns red.

A worked example with made-up payments

Say the set-aside cell holds 25 percent, and three clients pay you in one quarter.

PaymentAmount receivedSet aside at 25%
Client X₹40,000₹10,000
Client Y₹65,000₹16,250
Client Z₹30,000₹7,500
Total₹1,35,000₹33,750

Now say you pay ₹20,000 and record it on the Paid tab, using the amount and date on your payment receipt. The reserve is 33,750 - 20,000 = ₹13,750. Nothing was hard-coded: change the percentage in Settings and every row follows.

If your income arrives in uneven lumps, glance at the Next due date cell at the start of each week. Seeing the number of days left is a gentler way to plan than discovering a due date at the last minute, and it nudges you to top up the reserve while there is still time.

What should I do each time money comes in?

  1. Add the payment to the Log with its date, client and amount.
  2. Move the set-aside amount to a separate savings account on the same day.
  3. Look at the reserve left; if it is falling behind your plan, raise the percentage for new payments.
  4. After a tax payment, add it to the Paid tab at once.

The separate account matters as much as the sheet. A reserve that stays in your everyday account tends to get spent. If your income arrives in irregular lumps, SpendixAI can import your bank statements to show a month's credits in one place, and the set-aside Log can be filled from those figures.

SPENDIXAI JOURNAL

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

Explore your reports