Recently, I have hosted a webinar presented by KC Lau on Time Value of Money. Of which, he demonstrated how to use XIRR to calculate the Compound Annual Growth Rate (CAGR) of a stock investment. I found this to be very useful and so, I would like to share how you can do the same with your stock investments. 


#1: What is XIRR? 

XIRR is the abbreviation of Extended Internal Rate of Return. 

XIRR is used to calculate the Annualised Compounded Returns of an investment where the amount and dates of cash inflows / outflows are not fixed. 


#2: Cash Study – DBS Group Holdings Ltd (DBS) 

Let’s use my investment in DBS as an example. There are a few steps to calculating the XIRR of my investment in DBS. 


First, I need to compile the following information: 

a. The purchase date of my investment into DBS
b. The purchase price (after brokerage fees) of my investment into DBS
c. The exact dates for all dividends received from DBS 
d. The exact amount for all dividends received from DBS 
e. The current market price (less brokerage fees) of my investment in DBS


They are as follow:

The above information is sourced from the relevant: 

a. Dividend vouchers
b. Investment statements. 


Here are some notes when inputting these figures into Google Spreadsheet: 

a. Cash inflow is positive. 
b. Cash outflow is negative. 
c. Market value (less brokerage fees) is deemed to be cash inflow. 
d. When inserting the date, use this function: ‘=DATE(year, month, day)’ 


Second, after preparing the table above, I could proceed to calculating the XIRR of my investment in DBS by inserting this function: 


Function: =XIRR(cash flow amounts, cash flow dates) 


For cash flow amounts, drag the figures from ‘(18.4691)’ to ‘31.9009’. 
For cash flow dates, drag the figures from ‘7 Apr 2020’ to 7 Dec 2021’. 


Out of which, I obtained an XIRR figure of 44.95% per year. Thus, it means that I had made an annualised compounded rate of 44.95% for a period of 22 months from 7 April 2020 to 7 December 2021. 


Here is a copy of the spreadsheet for your reference – XIRR Calculation
You may download it, duplicate it and save a copy for your own use. 


Alright, that is it for this week. If you have any questions / feedback, please post them to ian@kclau.com

Stay tuned for my latest next week.


Ian Tai
Ian Tai

Financial Content Machine. Dividend Investor. Produced 500+ Financial Articles featured in KCLau.com in Malaysia and the Fifth Person, Value Invest Asia, and Small Cap Asia in Singapore. Regular Host and Presenter of a Weekly Financial Webinar with KCLau.com. Co-Founded DividendVault.com, an online membership site that empowers retail investors to build a stock portfolio that pays rising dividends year after year in Malaysia and Singapore.

Leave a Reply

Your email address will not be published.