What is XIRR in Mutual Funds? How to calculate it? Many of the Mutual Fund Investors might have come across the word XIRR. But for the majority of them, it is hard to digest of what it is. Hence, let us simplify it.

There is a huge misconception in understanding the difference between CAGR and XIRR. Hence, first let us understand the meaning of CAGR. CAGR stands for compoundedannualizedgrowth rate.This means your every investment are annualized to arrive at CAGR. It basically points to point return.

CAGR = ((Ending Amount (FV)/Beginning Amount(PV))^(1/No. of years(N)))  1

If we assume that we invested Rs.10,000 today and after 5 years, the value of that is Rs.15,000, then theaveragerate at which investment has compounded year after year is as below.

This is just for one lump sum investments. But the life of investors does not end with single investment right? We invest regularly or irregularly and at the same time, there is a possibility that we may withdraw money regularly or irregularly. In such a situation, to calculate the returns, we have to use XIRR or Extended Internal Rate of Return.

It is a measure of return on the multiple investments we did at different points of time. XIRR is a method used to calculate returns on investments where there are multiple transactions happening at different times (like SIPs, lump sum investments or withdrawals in middle).

When you invest in Mutual Funds, if you are investing through SIP or lump sum or redeeming either through SWP or lump sum, XIRR is the function which helps you to calculate the returns considering timings of your investments and withdrawals.

Hence, XIRR as nothing but an aggregation of multiple CAGRs.

CAGR gives us the compounded annual growth rate. However, XIRR is the average rate earned by each and every cash flow invested during the period.

CAGR will not consider the multiple cash flow. It only considers the initial value, end value and the number of years you invested. However, XIRR considers the multiple cash flows (either it may be investment or withdrawal).

CAGR gives us the ABSOLUTE return as it is a point to point return indicator. However, XIRR gives us the annualized returns.

CAGR, as I pointed, will be the measure of performance of lump sum investment. However, XIRR is the measure of performance of cash flow.

Both XIRR and CAGR will be same if you are investing lump sum only once and calculating the returns after a year.

Both XIRR and CAGR will be same if you are making multiple investments but the annual returns are same throughout the investment period (Like Bank FDs).

Both XIRR and CAGR will be different if you are investing multiple times and also there may be withdrawals in middle.

Both XIRR and CAGR will be different if the annual returns are not the same (like equity mutual funds or any other investment products where annual returns are not constant).

XIRR in Mutual Funds How to calculate?

You can calculate the XIRR using the Excel sheet easily. Let us take an example that you are investing every month Rs.5,000 for 6 months (on 5th of every month). In middle, on 16th August 2018, you invest a lump sum of Rs.25,000. On 7th month 5th date, you withdraw the whole investment. Let me explain the same with dates as below.

We have to input these details in Excel sheet as below. But before jumping into entering data, take care of below points.

All outflows like investments are considered as a NEGATIVE value and all inflows like withdrawals are considered as POSITIVE value.

In the next column add all transaction dates.

I will show you how to use the above example and calculate it in Excel from below image. The direct formula to calculate the XIRR is XIRR formula in excel isXIRR (value, dates, guess)*100

Here, VALUE means the cash flow amount you have to select, dates means the transaction dates and leave the guess field blank. Either you can enter this formula in the cell where you want the result or else you can select it from the Formula menu of Excel. Have a look at below image. You will get more clarity.

Hope, now you got a clarity on the meaning of CAGR, XIRR and how it can be implemented for your Mutual Fund Investments return calculation.

Top 5 Best Liquid Mutual Funds in India in 2018

Budget 2018 Mutual Fund Taxation FY 2018-19

Top and Best Debt Mutual Funds to invest in 2018

Top 5 Best ELSS Tax Saving Mutual Funds 2018-2019

Top 10 Best SIP Mutual Funds to invest in India in 2018

Basavaraj Tonagatti is the man behind this blog. He is SEBI Registered Investment Adviser who is practicing Fee-Only Financial Planning Process and also an Independent Certified Financial Planner (CFP), engaged in blogging since 7 years. BasuNivesh blog is ranked as one among Indias Top 10 Personal Finance Blog. He is not associated with any Financial product/service provider. The purpose of this blog is to Spread personal finance awareness and make them to take informed financial decisions. Please note that the views given in this Blog/Comments Section/Forum are clarifications meant for reference and guidance of the readers to explore further on the topics/queries raised and take informed decisions. These should not be construed as investment advice or legal opinion.

They are advertising on my blog. Hence, the ad showing there. But I personally never promoted a single brand. It is up to you which one you choose.

so it has nothing to do with you, nor their recommendations have your endorsement.

Yes, it has nothing to do with me and I will not endorse their recommendations. When we ADVERTISE either in a blog or in any media like TV or print media, it is the responsibility of the reader to judge. If I have written in my blog that I have responsibility or I will endorse their recommendations, then it turn to be my responsibility towards my reader.

Hi, I am planning to invest some additional funds in childs name. This is actually the gifts etc received and to be received by child periodically and also some additional surplus which i may get.I have already made plans and allocation for child education.My lookout will be for 10 years at least. I am contemplating choice between solution oriented child funds and hybrid aggressive funds.funds in consideration are hdfc/sbi/icici children funds in the first category or to go for aggressive hybrid funds like mirae asset/l&t/hdfc/sbi hybrid equity funds. What is your suggestion ?

Hi Basu sir, you explained us in simple words which common man can understand. Even I had heard this many places but never understood what is this and how to calculate now its very clear. I have a question in the websites like value search, morning star what ever the return percentages they show are they aboslute returns or returns over period of time

It is CAGR not XIRR as they show point to point.

I am an ardent follower of your blog for last 3 years. Based upon the recommendations and confidence gained from your blog I had started investing in DSP BR Small Cap Direct Fund-Growth, DSP Tax Saver Fund, SBI Bluechip Fund Direct Growth and Mirae Asset Emerging Bluechip Fund (Direct) Growth for last 2 years in SIP of Rs 5000(DSP-small), 2500 (DSP Tax saver), 5000(SBI Bluechip) and 2000 (Mirae Bluechip). But for the past few months its running on negative trend. I am 40 yrs old single lady with family burden working in a PSU. I have PPF and NPS also for the purpose of tax relief. I am constantly reassuring myself and continuing my SIPs as because I have a long term horizon of minimum 10 years and like 2008 this time also the market will perform on its best again. Please guide me if there is any correction of fund allocation needed or should I continue with the same?

What asset allocation you are following between debt and equity?

I wanted to follow 70:30 allocation between equity:debt initially but after starting investment a couple of years back I couldnt track down the ratio of allocation. Now, unfortunately I failed to understand/ calculate down what proportion of allocation am I following at present. Moreover I am worried regarding the market condition but still continuing the SIPs. At present, I have my total asset like this-

SBI Bluechip Fund Dir G 70K (on 5K SIP)

Mirae Asset Emerging Bluechip Fund Dir G (just started) 5K (on 2K SIP)

First do that allocation ratio, then rest of the discussion. It is simple proportional ratio calculation, which we did during our primary school days.

Hello sir, I am currently investing in reliance growth fund (mid cap) from may2017 . I am thinking to shift my new mid cap investment to HDFC Mid-Cap Opportunities Fund or L&T Mid cap Fund as their 5year performance is good and bit more consistent . Should i change or continue with relaicne mf midcap?

What is the time horizon of your investment? What asset allocation you are following?

First do the asset allocation between debt and equity-like 40:60. In equity, you just need to have one large cap and one mid cap and one small cap in the proportion like 50:30:20. For debt, use Ultra Short Term Debt Fund or PPF. Hence, you need only around 3-4 funds not more than that.

Sir why ultra short term for debt funds while i want to invest for long term. Can i choose balanced funds instead of debt fund?

Should not i invest more aggresively as 25:75 debt to equity ratio as my age is 30years

How can I say this? It depends on how much volatility you are ready to digest.

You look for debt funds just to diversify and compensate the risk which is already there in equity. Try to study the risk involved in debt funds also and based on that you can take a call. Balanced Funds are DEBT FUNDS?

I know Balanced funds are not debt fund. But balanced fund have some large portion in debt. But as i checked some debt funds their return is not significantly better than fds or ppf. So i thinking to invest in ppf or fds by nbfc or comapnies (those are also risky). But one Question left why u suggested ultra short term debt?

Do you think debt portion of balanced funds RISK FREE? Now regarding why I suggested Ultra Short Term Debt Fund, understand the meanings of Average Maturity and Modified Duration meanings and then we discuss.

Overnight Funds Vs Liquid Funds After exit load in Liquid Funds