Simplify Filling Schedule FA using this Google Sheets Template

If you are invested in US shares, either through direct investment or your company’s RSUs, you need to fill out Schedule FA while filing your income tax return. Submitting Schedule FA is a compliance requirement and failure to do so could lead to a fine of Rs 10 lac and/or imprisonment up to 7 years under the Black Money Act, 2015. It does not matter whether or not you sold the shares you held overseas.

If you’re new to Schedule FA, I’ve covered the process in detail in this article, and the process for capital gains tax for US shares here.

Filling Schedule FA comes with its own set of challenges, which only multiply if you’ve had multiple transactions over the years. In this article, I explain the process that I follow and share a template I’ve created to make this process simpler for myself.

Note: I am not a tax expert. These articles are my notes from my learnings and experiments. I may be wrong in my understanding and am ready to bear the consequences for myself. However, I cannot take responsibility for any errors you make in filing your ITR as a result of following this article. Please check with a professional for your specific use case.

Understanding the Complexities of Schedule FA

Let me explain the complexity with a scenario.

Let’s assume you are filling out Schedule FA for the calendar year 2023 (the reporting period for Schedule FA depends on the accounting period of the company where you hold the assets; in the case of the US, it is the calendar year. Check my article on Schedule FA to understand these nuances).

Let’s further assume that you hold shares of Uber, and here is the list of transactions over the years. Ignore the “Lot” column for now:

For filling out Schedule FA for CY 2023, you need to provide details of any assets you held during 2023. So, if we look at the cumulative data, you held 20 shares on 1st Jan 2023, 25 shares on 14 Feb 2023, 31 shares on 23 May 2023, and 18 shares on 28 Dec 2023.

But it is not so straightforward. See Table A3 below.

The Date column adds complexity to the process. As per my understanding, due to the presence of this Date column, you cannot provide a cumulative value of your shares. You need to report each lot separately and for each lot, you need to report the initial investment value as well as the peak value of that lot.

So, this is how you will have to structure your data to fill Table A3 of Schedule FA for CY 2023 (this is an oversimplification of the actual table A3):

Reporting the peak value of investment for each lot presents its own challenge. You need to track the share price each day and the shares you held on that day for that lot, to see when the peak value for that lot was hit. Then, you need to report these values in INR using the SBI TTBR value of that day.

These nuances make filling out Schedule FA a huge pain. I have spent hours each year in the month of July filing my ITR due to these. Given these nuances, I do not trust anyone else to fill out my ITR because it is quite possible that they will take a shortcut instead of going through so much of the pain.

This year, I created a solution for myself using Google Sheets, which has made this process much easier compared to previous years. Here is how you can build the same for yourself.

Creating a Google Sheet Template for Schedule FA

If you are a power user of Google Sheets, then with the instructions mentioned below you will have a working template ready. However, if you want to avoid all this work, you can purchase the Google Sheet template at a nominal cost using the payment link.

The key is to find out which sale transaction corresponds to which purchase transaction to figure out the corresponding values for each lot. Here is a gif to show the template in action, followed by explanation for each tab.

Note: Columns that are in gray color are formula driven and need no manual intervention.

ReadMe Tab: This tab contains the instructions for using the sheet.

Input Tab: In this tab I select the FY for which I am filing the ITR from the dropdown, and the name of the US company whose shares I own. It auto-populates the share price on the last day of the corresponding calendar year.

Purchase Tab: I have added all the purchase transactions in one sheet and use a combination of the stock name and purchase date to create a unique ID for each purchase transaction (lot). You can decide to use any other method to create a unique ID.

Sale Tab: I have created another sheet for sale transactions. Using the report shared by the broker, I know which lot was sold by me. I use the unique ID created in the previous step to identify the lot in this sheet. Thus, I can map the sale transaction to its corresponding purchase transaction.

Shares Purchased Until EoCY: In this tab, using a combination of SUMIFS and FILTER formulas, I auto-populate all such unique IDs from the Purchase Tab where the purchase date is less than the last day of the calendar year. I also get how many shares were sold before the start of the year and how many were sold during the year. This helps me find the initial value of investment for each lot in that CY and the value at the close of the year accurately. This sheet does not require any manual intervention from me.

Peak Value for each tranche: In the next tab, I automatically get the number of shares I held at any point in the year for each unique ID and thus the corresponding value of investment for that lot on any date. This gives me the peak value of investment for each unique ID (aka lot) and the date it was hit. This is achieved using a combination of these formulas: TRANSPOSE, FILTER, GOOGLEFINANCE, SUMIFS, MAX, INDEX, MATCH. As with previous tab, this sheet does not require any manual intervention from me.

Missing SBI TTBR Dates: Since we need to use SBI TTBR dates, in the next tab I automatically get a list of all such dates for which I need to find the SBI TTBR. I add the SBI TTBR for these dates in another sheet. This is achieved using a combination of FILTER, SORT, IFERROR, and converting them into an array formula.

Schedule FA: The last tab automatically gives me all the data that I need to fill Table A3 of Schedule FA, in the exact format that it is needed. The columns that I need for ITR are highlighted in light orange. This is achieved using a combination of QUERY and VLOOKUP. The columns in this sheet are the ones that I add in Schedule FA.

Except for the first four tabs (Input, Purchase, Sale, SBI TTBR data) this setup does not require any manual intervention from me. Even for these four tabs the information needed is bare minimum – I only need to add my sale and purchase transactions and the SBI TTBR rate for certain dates. While the initial setup could take some time if you do not have transaction data handy, if you continue to add subsequent transactions in the template every few months, next year will be a breeze! And if you think about it, you will anyway require downloading transaction data, so might as well have it in a template that makes the process easier.

There is one limitation of this sheet – it can only handle one company at a time. So, if you hold shares of multiple companies you will have to make a copy of this template for each company. I plan to remove this limitation in the subsequent versions.

If you want to avoid all this work, you can purchase the Google Sheet template at a nominal cost using the payment link.

Hope this helps you with your tax filing.

Photo Credit:

• Featured Photo by rawpixel.com on Freepik