Imagine this.
Let’s assume you bought 1,000 shares of Public Bank Bhd at RM4.35 a share on March 5, 2021.
Then, you bought another 2,000 shares of Public Bank Bhd at RM3.78 a share on June 2, 2022.
Ever since, you hold onto 3,000 shares of Public Bank Bhd and collect dividends from them.
Now, Public Bank Bhd is trading at RM4.87 a share on 6 March 2026.
So, what exactly is your investment returns from Public Bank Bhd’s shares?
Introducing XIRR
From the above, can you see that you have:
1. Made two investments into Public Bank Bhd at different dates (different intervals)?
2. Invested different quantities of shares of Public Bank Bhd (different amounts)?
Also, you would have collected different amounts of dividends at different dates since your first investment into Public Bank Bhd on March 5, 2021.
So, you have different amounts of cash, flowing in and out, at different dates, between March 5,
2021 to March 6, 2026, from your investments in Public Bank Bhd’s shares. Here are the dates of your investments (cash outflows) and the dividend income you would have earned in between:
XIRR, known as Extended Internal Rate of Return, would calculate the annualised returns of your investment in Public Bank Bhd’s shares based on the actual amount you’ve invested and earned in dates throughout the period.
The annualised returns calculated are comparable to FD rates.
Basically, we can easily calculate XIRR in three simple steps as follow:
Step 1: Insert the “DATE” function
Allow me to demonstrate this with Google Spreadsheet.
So, to insert a date, type in “=date(year, month, day)”.
Hence, for March 5, 2021, type in “=date(2021, 3, 5)” as shown below:
Repeat the same for all dates listed at the table above.
As such, you would have inserted all the transaction dates as follow:
Step 2: Insert the “Net Cash Inflow / Outflow” Amount
The next step is to insert the net cash inflow / outflow amount for each date in that period.
So, for March 5, 2021, the amount to be inserted is “-4,362”.
This figure is negative as you paid RM4,362 to acquire 1,000 shares of Public Bank Bhd.
Cash flowed out of your pocket.
Next, for March 22, 2021, the amount to be inserted is “130”.
This figure is positive as you earned RM130 in dividends from Public Bank Bhd.
Cash flowed into your pocket.
Then, we have today’s date, which is March 6, 2026.
Although you intend to continue to hold onto your shares, you’ll need to imagine that you would be selling off your shares today in order to calculate your annualised returns.
Thus, the market value of your 3,000 Public Bank Bhd’s shares, which is RM14,610, is inserted as a “cash inflow” in the Google Spreadsheet.
Step 3: Insert the “XIRR” function
At this stage, we are ready to calculate the annualised return of your investments.
You may proceed to insert “=XIRR(cashflow amounts, cashflow dates)”.
You may first drag all the
First – all the “Cashflow amounts” inserted in Step 2 and
Second – all the “Cashflow dates” inserted in Step 1
Once done, you would get the annualised return figure, which is “0.08638…”
Then, kindly click onto the “%” function as shown below.
Of which, you would obtain the figure as 8.64%.
That would be the actual annualised return of your investments into Public Bank Bhd.
And yes, we are done!
Why Do Different Investors Obtain Different Results, Despite Investing in the Same Stock?
Now, what if another person invests in Public Bank Bhd at an earlier or later date?
What if another person just happens to buy Public Bank Bhd at the same dates above but not at the same quantity of shares?
You can play around with the XIRR calculator at Google Spreadsheet.
Of which, you’ll discover that different results are obtained when different investors buy:
1. at different prices
2. at different time or intervals
3. at different quantities of shares
Of course, we haven’t factored in – What if an investor chooses to sell off his shares partially?
As such, one thing is for sure.
Stock investing is more than just knowing what stocks to buy.
With XIRR, it reminds us that every investor’s journey in building portfolios is different.
By tracking XIRR, we can see if our decisions have contributed to compounding of our wealth.
Announcement:
I have just launched Dividend Vault – my latest book. It documents my 10-Year Journey as an investor, which includes my background, 15 case studies of stocks that I invested in, successes, mistakes and lessons learnt from them.
Link: Dividend Vault Book
