Using XIRR to Calculate CAGR

How do you calculate returns from your investments when the investments you make (inflows) and returns you get (outflows) are at different periods in time ? Using XIRR to calculate CAGR. For example, you could buy shares of a company at different prices and at different periods of time. You could then sell it off after you have made some profits.

How do you calculate the returns in such a case ? Both the inflows and outflows are of different amounts and at different time periods.

This can be calculated using the XIRR function. Let’s look at the concept with an XIRR example.

XIRR example

Suppose, you buy 200 shares of company X at Rs 275/- on January 1st 2004 and then again 400 of them at Rs 225/- on April 4th 2005. When the share price climbed to Rs 287/- on May 5th 2006, you purchased another 75 shares.

On June 6th 2007, you finally sold off all the 675 shares at Rs 700/-.

How do you calculate your returns ? Enter XIRR.

XIRR returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. (To calculate the internal rate of return for a series of periodic cash flows, use the IRR function).

Formula for XIRR is XIRR (values,dates) where

values is a series of cash flows and

dates is a schedule of payment dates

Let us use excel sheet to calculate XIRR. Open excel and do the following :

1. List the dates of your deposits and withdrawals in the first column.
2. In the second column called “OutFlows”, list your withdrawal amounts as negative numbers.
3. In the third column called “InFlows”, list the deposits as positive numbers.
4. The fourth column called “Total” is the sum total of entries in the OutFlows and InFlows columns.

After the 4 steps, you will see the table look like below.

The outflows is the money which you invested in buying the shares. The inflows is the money you received when you sold the shares. Note that the transaction date is very important when calculating XIRR. If you input that incorrectly, then your answer for XIRR will be incorrect.

You then run the XIRR function on the figures in the Total column (column D) and Date or Sl No Column (column A).

See the below snapshot for more clarity.

More explicitly, in the cell D7 type out = XIRR (D3:D6,A3:A6) and hit Enter. This is the same formula XIRR (values,dates) listed above.

Your answer 50.10% will be displayed ! This is the total return on your investments.

Using XIRR to Calculate CAGR – another example

A reader of TheWealthWisher asked me to calculate the returns on his chit fund investments.

Following were the inflows and outflows of the investor :

The investor, over a period of 20 months, was putting in varying amounts of money into a chit fund. He got a lump-sum of Rs 466,000/- after the 17th month after paying charges of Rs 34,000 apart from the Rs 24,050/- installment amount of the 17th amount.

His return on investments stands at -4.29%.

XIRR is also used for calculating returns on mutual fund investments, especially when you are using the systematic investment planning route. It is also useful to calculate your returns from Unit Linked Insurance Plans (ULIPs). In both these scenarios, you invest different amounts at different periods of time.