Some Cool Calc Tricks for Indian Taxpayers

If you do not consider yourself a Calc or Excel expert but must do your taxes in a spreadsheet, then these cool tricks can help you a great deal.

- V.Subhash

The financial year ended in March and everyone with taxable income must file IT returns by July this year. I opened a new bank account last year and it came with a new demat (share-trading) account. Rather than just shovel numerous lists of transactions from my bank and demat accounts to my tax consultant, I decided to put all that data in one spreadsheet before sending it to him.

My experience with spreadsheets ended with Lotus 1-2-3 in the 90s so working with Calc threw several challenges. Here is how I addressed them.

IF function — for conditional formulae

Some fixed deposits make interest payments at a regular interval (monthly, quarterly, half-yearly or yearly) while others pay cumulative interest only at maturity. How do I make a column for monthly interest payments for a list that contains both types of deposits? If the amount paid back by a fixed deposit at maturity is the same as the amount invested at the beginning, then it would have paid interest at regular intervals. If the amount paid back at maturity is much greater than the amount invested at the beginning, then obviously, it must be a cumulative fixed deposit.

In Figure 1, the F column contains formulae with the IF function. The monthly payout is calculated only if the pay-in and payout are the same.

In Figure 2, the E column shows the source of the gold ornament and the B column gives its weight. Using the SUMIF function, I can add the weights in the B column if the corresponding values in the E column equal a particular type of source. This can be done using the search criterion as either “=Own” or “=Parents”.

VLOOKUP function — for values from a lookup table