Calculating Internal Rate of Return using the IRR Formula

Stops you from guessing your performance!

Using the Internal Rate of Return or IRR formula is the best way to be able to accurately track your individual stock and stock portfolio performance.

The reason why you should track your performance is fairly obvious.

Successful value investing in stocks requires that you have as much information at your fingertips as possible.

If over time you are losing money, then it is useful to know. You then have the option to change your investment plan.

Of course, it is even nicer to know that you are making money!

Because share investing occurs over time and usually involves purchases and sales at irregular intervals, the measure of share performance needs to handle both the time value of money and the irregular buying and selling events.

The 'time value of money' refers to the fact that the sooner you receive money, the more you can potentially earn from it. So, the longer the delay in receiving money, the less it will be worth.

In the context of share investing, if your share portfolio does not increase in value for a period of time, you are effectively losing money.

Using the IRR

The financial measure that can do both, using an Excel spreadsheet, is the internal rate of return calculation using the IRR formula.

The IRR calculation may be used to track individual share performance over time. This includes taking into account purchases and sales of the particular share, as well as dividend receipts.

The IRR formula can also be use to track the overall portfolio performance over time by consolidating all the buying and selling decisions across the total portfolio.

A Portfolio Summary Worksheet is part of the package of worksheets.

The worksheet allows me to undertake portfolio performance monitoring by updating share prices weekly so that the total share portfolio dollar value can be calculated. It is also used to update the current date.


Spreadsheet Requirements

Some of the important requirements when using a spreadsheet to undertake this calculation include ...

• The cost of purchasing a share must be entered as a negative value
• The series of following share purchases (if present) are also entered as negative values
• Any share sales are entered as positive values
• The current value for each share adjusted for cash dividends, calculated as: (the share price X the number of shares) + cash dividends received) - must be entered as a positive value
• Dates should not be entered as text.

The IRR Formula Explained

An example of an IRR formula is: =XIRR(N6:N20,M6:M20,0.1) where:

• XIRR is the code for the internal rate of return function
• N6:N20 represents the range of cells in the spreadsheet column containing the (negative) buying costs, any (positive) sales and the (positive) current value adjusted for cash dividends at the bottom
• M6:M20 represents the range of cells in the spreadsheet column containing the dates of share purchases, and sales (if any) - with the current date at the bottom
• 0.1 represents a guesstimate relating to the iterative (repetitive) process used to produce the IRR result

The range of cells used will vary from that in the example above, depending on the number of purchases and sales involved.


Activating the Analysis ToolPak

Another important requirement when using the XIRR function for calculating internal rate of return is that the Analysis ToolPak must be installed with Excel.

To check that it is installed, while in Excel, pull down the Tools menu and select Add-Ins. If Analysis ToolPak appears on the list of add-ins, make sure that it is checked.

If not, then check it – that is, ensure that it has a tick in the box beside it.

If the Analysis ToolPak can’t be checked, or if it does not appear on the list, then you will need to re-install the Excel or Microsoft Office software and choose a full install to ensure that the Analysis ToolPak is available.

If the ToolPak is not installed, you will see a Ref! (Reference) error appear in the cell containing the XIRR formula.


Free IRR Worksheets

If you are thoroughly confused at this point because you have not had any experience in using spreadsheets and the IRR formula, help is at hand.

Virus-protected Excel IRR Calculations Worksheets containing example IRR calculations can be obtained by contacting me to request a FREE copy.

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.

Don't forget to check whether you need the Excel 2007 or the Excel 2003 version of the spreadsheet. Look in the Properties section of your Excel software to see which version you have.

The examples provided include all the required formulas and can be adapted to include your share purchase and dividend information.

This will make tracking your share and portfolio performance using the internal rate of return calculator much easier.

Return from Internal Rate of Return to Value Investing Home Page