How do we calculate Returns / CAGR in Portfolio ?

Mathematically,

XIRR is that single rate of return, which when applied to every installment (and redemptions if any) would give the current value of the total investment.

XIRR is your personal rate of return. It is your actual return on investments.

XIRR stands for Extended Internal Rate of Return is a method used to calculate returns on investments where there are multiple transactions happening at different times.

Mutual Fund investments are not as evenly spaced as you saw above in case of mutual funds. In the case of mutual funds, you tend to invest and redeem investments at irregular intervals.

It will cause cash inflows and cash outflows at different points in time. In this type of case, in addition to the invested amount, the time of such investment also assumes significance to yield a certain outcome.

Here you may use the concept of Extended Internal Rate of Return (XIRR).

So, XIRR is a good function to calculate returns when your cash flows (investments or redemption) is spread over a period of time.

Calculation

XIRR can be easily calculated using Microsoft Excel. Excel provides an inbuilt function to calculate XIRR.

XIRR is a more powerful function in excel for calculating the annualized yield for a schedule of cash flows occurring at irregular periods.

XIRR formula in excel is:= XIRR (value, dates, guess)

Step by Step Process to Calculate in Excel

Enter all your transactions in one column. All outflows like investments, purchases will be market negative while all inflows like redemption’s while are marked positive.
In the next column add the corresponding date of the transaction
In the last row mention the current value of your holding and the current date
Now Use XIRR function in excel which is something like this =XIRR (values, date, Guess)
Select values to a series of cash flows that corresponds to a schedule of payments in dates and date columns stand for the date when the first investment was made and when the cash flows were received, guess parameter is optional ( if you do not put any value Excel use a value of 0.1.

Example of How to Use the Function in Excel :

For this calculation you need is with an example of six-month SIP. Let

SIP amount = ₹ 5000

SIP investment dates = start-01/01/2017, end-01/06/2017

Redemption date = 01/07/2017

Maturity amount = ₹ 31000

Assume we have a set of cash flows like those in the table below :

01-01-2017	-5000
03-02-2017	-5000
01-03-2017	-5000
11-04-2017	-5000
01-05-2017	-5000
25-06-2017	-5000
01-07-2017	31000
	11.92429

In the above table, the cash flows are occurring at irregular intervals. Here, you can use XIRR function to compute the return for these cash flows. Remember to include the ‘minus’ sign whenever you invest money.

Open an excel sheet and follow these steps :

In column A, enter the transaction dates on the left side.
In column B, enter SIP figure of 5000 as a negative figure as it’s an outflow cashflow.
Against the redemption date (Column A), enter the redemption amount (Column B) (31000).
In the box below 31000, type in: “ =XIRR (B1: B7, A1: A7)*100 ” and hit enter

XIRR value of 11.92 % will be display as a result.

So XIRR makes this simpler by calculating one return for your investments. So, if you are looking to calculate returns on your mutual fund investments XIRR is the right way to go.