Tracking results with the IRR Formula
Keeps You From Guessing!
Using the Internal Rate of Return or IRR formula is the best way to be able to accurately track your stock market share and portfolio performance. The reason why you should track your performance is fairly obvious.
Successful 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 – assuming you have one. 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 financial measure that can do both, using an Excel spreadsheet, is the Excel IRR formula.
The formula for solving the IRR may not only be used to track individual share performance over time, but also the of the overall portfolio performance over time.
For your information, if you have had previous spreadsheet experience, 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
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 process used to produce the IRR result
The range of cells used may vary from that in the example above, depending on the number of purchase and sales involved.
Another important requirement when using the XIRR function for the irr calculation is that the Analysis ToolPak must be installed with Excel.
To check that it is, 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.
If you are thoroughly confused at this point because yoy have not had any experience in using spreadsheets and the IRR formula, help is at hand.
Excel IRR Calculations Worksheets containing example IRR calculations are available and can be obtained by contacting me directly to request a free copy.
A Portfolio Summary Worksheet is part of the package of worksheets.
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 much easier.
Return from Tracking Results with the IRR Formula to Home Page
|
