Calculate IRR for Individual Shares

Provides a good value investing guide!

To calculate IRR (internal rate of return) for individual shares, an Excel spreadsheet may be used. This exercise is very useful from a value investing perspective as it provides an important measure of performance.

Three Individual Share Worksheet examples that demonstrate how this can be done maybe obtained by contacting me directly to request a virus-protected FREE copy.

This will enable you to follow more clearly the discussion below.

An Excel worksheet request from you will not result in your email address being stored in a distribution list nor be used by me for unsolicited communication with you.

The worksheet examples 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.


Explaining the Individual Share Worksheets

The Share A worksheet illustrates how three buying transactions and one selling transaction are recorded. Also several Interim Dividend receipts (Int Div) and Final Dividend receipts (Final Div) are also recorded.

The Share B worksheet shows, in addition, how stock obtained from re-investing dividends using a Dividend Reinvestment Plan (DRP) are recorded in the worksheet.

The Share C worksheet indicates in Cell K25 that the current loss (-) on this investment represents 1.1% of the current overall portfolio value. My selling rule below explains how I use this value.

Totals are included on row 25 for all three Share worksheets using SUM formulas; for example, =SUM(C1:C25) for column C and equivalent formulas for columns 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 current return in cell M21 of each Share worksheet is obtained by linking internally to cell J25 of the same worksheet.

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 current portfolio value:

=(J25-F26)/'Portfolio Summary'!D15*100

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

Selling Rule

A selling rule I use in order to preserve capital is to sell if the capital loss recorded in Cell K25 for a each share 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 - like in the recent general downturn. You only lose money when you sell!

Note that in the Share C worksheet, Share C has a (negative) current capital loss of 1.1%.


Calculating IRR

To calculate IRR for each share, the formula:

=XIRR(M6:M22,L6:L22,0.05)

is used. The IRR result 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 annualized result and the result obtained when the purchase is less than one year old may not be highly meaningful.


To Conclude

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 strategy.

Your task now is to replace the fictitious share data in the individual share worksheets with information on your share holdings so that you can calculate IRR for each of your shares.

Use one of these worksheets as a template to create additional individual share worksheets for other shares that you hold.

The related articles below provide more detail on the other ways I use IRR calculations to inform investment decisions.

---

Related Articles

My selling strategy - relies on being able to calculate IRR for each share in my portfolio. Check out my selling strategy to see how.

The Overall Portfolio IRR Worksheet - tracks the performance of the whole portfolio. Check it out here.

The Shares Portfolio Summary Worksheet - provides a listing of all the stock in the portfolio together with information on each holding.

Return from Calculate IRR for Individual Shares to Internal Rate of Return

Return to Value Investing Home Page