Why I Use XIRR and How I Calculate It?

Firstly, I would like to start by saying that this is how I calculate XIRR for my portfolio and this may not be the right way to do it. If I remembered correctly, using XIRR to track your portfolio is controversial and there are people who think XIRR should not be used and there are different ways of doing it. 

When I just started investing, I do keep track of my cumulative return and CAGR but I feel that the values calculated do not really reflect the performance of my portfolio. Cumulative return do not take time into consideration, so it could be a case of 20% return in 1 year versus 20 years. Without the time frame, there is no basis for comparison. 

If we want to take the length of investment into consideration, one option is to use the CAGR. But CAGR only takes the starting and ending values of your investment. So if you are doing irregular transactions or capital injection into your portfolio, the time weightage for the investment will be skewed.

The other option is to use the XIRR (Extended Internal Rate of Return). This is by far the best option for me which takes irregular time interval into consideration and the easiest to apply. XIRR can be easily calculated in Excel using the following formula.

XIRR = XIRR(value,dates)

A simple way to view XIRR is that it is tracking cashflow in and out of your portfolio. In Excel, you will just need 2 columns. I have a 3rd column just for 'Remarks' to indicate the transaction type and details. The 'Date' column is where the date of transaction is tracked and under the 'Value' column, the value of the transaction. For the 'Value' column, do note that there is a difference between money 'flowed in' and money 'flowed out' the portfolio. Money 'flowed in' are tracked as 'Negative' and money 'flowed out' are tracked as 'Positive'.

For Example

1. Buying an Investment (Money 'flowed into' portfolio) - Negative Value
2. Selling an Investment (Money 'flowed out' of portfolio) - Positive Value
3. Dividend (Money 'flowed out' of portfolio) - Positive Value

Lastly, the last row of the table will be the date of 'Today' and the value will be your total portfolio value in 'Positive'. You can imagine it as that you are liquidating all your investment 'Today' and money 'flowed out' of portfolio, therefore it is 'Positive'. You can then simply get the XIRR value using the Excel formula on the table.

I do not include Cash and Singapore Saving Bonds in the calculation of XIRR.

The above are the reasons which resulted me to chose XIRR and how I calculated it. I would like to reiterate that I may be wrong to apply the formula in this way. This is just a metric for me to know whether my portfolio has improved or got worst from previous months. If you are comparing XIRR with others, make sure it is an apple to apple comparison as different people may use the formula differently. It as also perfectly fine if you are using other metrics to track your performance.