Calculate IRR for Individual Shares
- Provides a good stock investment guide!
To see how to calculate irr for individual shares, it would be best to print out the three Individual Share Worksheet examples that can be obtained by contacting me directly to request a copy.
Examples of worksheets that track the internal rate of return (IRR) of three different (fictitious) company shares are provided as the Share A, Share B and Share C worksheets in the spreadsheet.
The Share A worksheet illustrates how three buying and one selling transaction are recorded. Also several interim dividend receipts (Int Div) and Final Dividend receipts (Final Div) are also recorded.
Totals are included on row 25 for all three Share worksheets using SUM formulas, for example =SUM(C1:C25) etcetera for columns C,E, F and H.
The current price is included in column I25 for all three Share worksheets. The current price for Share A is obtained through linking to cell C5 of the Portfolio Summary worksheet by the formula: ='Portfolio Summary'!C5 - and in a similar manner for the current price for Share B: ='Portfolio Summary'!C7 - and for Share C: ='Portfolio Summary'!C9.
The current return for each share is calculated in cell J25 and is obtained by multiplying the number of shares by the current share price and adding the total dividend:
=C25*I25+H25.
The percentage capital profit or loss is calculated in cell K25 for each share by subtracting the total input from the current return and dividing by the portfolio value:
=(J25-F26)/'Portfolio Summary'!D15*100
A selling rule I use is to sell, in order to preserve capital, if the capital loss recorded in this cell reaches 2% of the portfolio value.
An exception I make to this rule is when there has been a general market downturn unrelated to the particular share. In this case, I grit my teeth and hang in there. You only lose money when you sell!
Note that in this example, Share C has a (negative) current capital loss of 1.1%.
To calculate irr for each share, the formula: =XIRR(M6:M22,L6:L22,0.05) is used. The IRR figure at M25 for each share is re-calculated for each share when either the share price, or the current date, or both are updated in the Portfolio Summary worksheet.
| Keep in mind that the calculated irr is an annualised result and the figure obtained when the purchase is less than one year old may not be highly meaningful.
|
The current date in L21 of each Share worksheet is linked from the current date entry in cell D17 of the Portfolio Summary worksheet using the formula:
='Portfolio Summary'!D17
The current return in cell M21 of each Share worksheet is obtained by linking internally to cell J25 of the same worksheet.
Open up the worksheet for each of Share A, B and C in turn and study the formulas in the various cells mentioned above so that you become familiar with how the calculations are determined.
Being able to calculate irr for individual stocks in my portfolio not only provides a good stock investing guide but also informs my selling policy.
Your task now is to replace the fictitious share data in the individual share worksheets with information on your share holdings.
Use one of these worksheets as a template to create additional individual share worksheets for other shares that you hold.
Return from Calculate IRR for Individual Shares to Tracking Results
|
