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.

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