The Portfolio IRR Calculation
Tracks my overall performance using Excel irr!
One irr calculation I perform, among others, is the Overall portfolio irr using an Overall IRR Worksheet which includes the irr calculator.
Why Calculate the IRR?
The reason for using an Overall 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 Index (the All Ords). Check out what your index is using the 'stock market index' link above.
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.
Obtaining the Worksheets
You may obtain a FREE COPY of the virus-protected IRR Calculations Worksheets that include the Overall IRR Worksheet by contacting me directly.
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.
It would be best to obtain the free copy, then print out the sample Overall IRR Worksheet, or open up the worksheet in order to follow this discussion.
Alternatively, you may prefer to use the discussion to construct your own worksheet.
Calculating the Total Portfolio IRR
In order to know how to calculate 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 irr formula: =XIRR(C5:C15,B5:B15,0.05).
To Conclude
Being able to calculate my overall portfolio performance is for me an important piece of information. I aim to achieve an average performance over time of 12-15 per cent or more.
This represents on average a performance that provides up to a three per cent or more gain on my 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.
Then include the relevant share data relating to your own portfolio so that you can perform your own irr calculation and be more informed when value investing!
Return from IRR Calculation to Internal Rate of Return
Return to Value Investing Home Page