The Portfolio IRR Calculation
- Tracks my overall performance!
One irr calculation I perform, among others, is the Overall portfolio IRR. The reason for using an Overall internal rate of return Excel irr worksheet is to be able to track the performance of my portfolio over time.
By having an accurate indication of my investing performance, I am in a position to not only decide if I am making or losing money, but also to compare my performance to the relevant stock market index. In Australia, this is the All Ordinaries (All Ords).
If my performance over time is no better than the relevant market index, then I would be saving myself a lot of time by taking Benjamin Graham's advice and investing in an index fund that tracks that market.
You may obtain a free copy of the IRR Calculations Worksheets by contacting me directly.
It would be best to print out the sample Overall IRR Worksheet, or open up the worksheet in order to follow the following discussion.
To calculate the IRR for the total portfolio, the worksheet requires the dates of all share purchases and sales (see cells B5 to B13).
It is important to note here that the first date in the column (at B5) must be the oldest date. The order of the following dates is not important.
Also required is a cash-flow column (see C5 to C13) which includes total purchase costs (transaction cost plus brokerage) entered as negative values and any total sales amounts (sales transaction minus brokerage) entered as positive values.
The sample worksheet shows seven total purchase costs and one total sale amount (at C8).
All the total purchase costs and total sales amounts have been transferred from the three sample Share A, Share B and Share C worksheets that accompany the sample Overall IRR Worksheet.
If you have obtained these worksheets check to verify this by looking in column M of each of the three Share worksheets.
The current date in cell B15 is linked from the Portfolio Summary spreadsheet (which is part of the worksheet package) using the formula =’Portfolio Summary’!D17.
The total return for the portfolio is calculated in cell C15 by adding the total returns in each of the Share worksheets by means of the formula:='Share A'!J25+'Share B'!J25+'Share C'!J25.
The Portfolio IRR is then calculated in cell C17 by means of the formula: =XIRR(C5:C15,B5:B15,0.05).
Being able to calculate my overall porfolio performance is for me an important piece of information. I aim to achieve an average performance over time of 15 per cent or more.
This represents on average a three percent or more gain on the stock market accumulation index that has a historical return of about 12 per cent.
Your task, after you have adjusted the individual share worksheets and created more of them if required, is to delete the fictitious share data and include the relevant share data relating to your own portfolio.
Return from IRR Calculation to Tracking Results
|
