Calculation In Detail

This page provides details of calculation method used by Portfolio Template. For any further queries on the same please feel free to contact me.

First in First out (FIFO)

The template uses FIFO - First in First out method for computation of capital gains. One needs to arrange the transaction (including corporate actions) in ascending order to get the correct output from the spreadsheet. Optionally you can dumb your transaction in the transaction sheet and press the load button in the FIFO sheet, the template will do the sorting for you.

To compute the capital gain it's mandatory to us FIFO method as per Income tax Act.

Speculative / Non Speculative

If the shares purchased are sold within T + 2 days the income derived from such a trade is considered as speculative. FIFO method is used to compute date of purchase.

For example:

Let's say shares of ABC ltd are bought on 1-7-2012 (5 shared) and 15-7-2012 (10 shares). 7 shares are sold on 16-7-2012. In this case first 5 shares will be treated as short term capital gain and the other 2 will be treated as speculative income.

Short Term / Long Term

The spreadsheet classifies gain on shares as long term if the shares are hold for a period of more than 1 Year. All the transactions that are not Long Term or Speculative will be treated as short term.

Corporate Action

Portfolio Template is also able to cater with various corporate actions, Below are the details corporate actions and how the spreadsheet handles them.

• Bonus Shares - A BUY transaction with Zero cost (User mark's this transaction as BUY)

• Sell of Rights - Sell of rights to buy shares is simply treated as other income

• Split of Shares - Existing shares split into the entered ratio. (refer below para)

• Dividend - Other income

• Dividend - Dividend stripping [S. 94(7)] (refer below para)

Split of Shares

Split of shares calculation is is one of the most complex thing that needs to be handled with lots of care. However Portfolio template makes it as easy as a child's play. User simple needs to enter the correct transaction code and the correct split ratio.

For Example:

If you have received 5 shares for every 2 shares held. The split ratio comes to 2.5 (5/2). This ratio needs to be entered into the quantity column of the spreadsheet against the corporate transaction code 'Split'. The templates will take care of rest by multiplying your holding with the respective ratio and computing the gain correctly.

Multiple splits

This is one of the unique spreadsheets than can handle multiple splits for a simple script. Not just that it can properly tackle various transaction like buy, sell, dividend & bonus between the various splits for the script.

For cases where the split results in non integer value of the shares held the balance is treated as cash received (Other income). For more details refer the instruction sheet inside the Spreadsheet.

Dividend Stripping

If shares are purchased in less that 3 months period before date of declaration of dividend and sold within 3 months from the dividend date the loss arising from such a transaction is reduced by the amount of dividend received for such shares.

Opening Balance

In order to get the correct output from the template a user should enter all the historic transaction of at least the scripts that are in the hand. However in the absence of data, dummy transaction can be entered. This transaction should belong to correct financial year in order to compute the correct indexation and hence correct long term capital gain.

Note: if you are only interested in the tax computation and applicable long term capital gain tax rate is zero % you can enter any dates (more than 1 year back) for your opening balance. This would off course lead to incorrect gain computation but as tax rate is zero it won't matter.

This version of Portfolio Template is for shares and hence Bonus Stripping is not implemented in this version. A separate version for mutual funds will be released later

You can download the trial version from the below or download trial version page.