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.
What is CAGR?
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 “compounded annualized growth rate”. This means your every investment are annualized to arrive at CAGR. It basically points to point return.
The formula used to calculate this as below.
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 the average rate at which investment has compounded year after year is as below.
8.44%=((15,000)/10,000)^(1/5))) – 1
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.
XIRR in Mutual Funds – What is this?
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 CAGR’s.
Difference between XIRR and CAGR
- 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.
- First SIP on 5th June 2018
- Second SIP on 5th July 2018
- Third SIP on 5th August 2018
- Lump Sum investment on 16th September 2018
- Fourth SIP on 5th September 2018
- Fifth SIP on 5th October 2018
- Sixth and final SIP on 5th November 2018
- You withdraw all the money on 5th December 2018.
We have to input these details in Excel sheet as below. But before jumping into entering data, take care of below points.
- Enter all transactions in one column.
- 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 is XIRR (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.
Our other posts related to Mutual Funds:-
- 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
30 Responses
I am DIY investor from few years now. I do maintain an excel (created by me) to track and calculate XIRR or CAGR for each MFs. I do visit various sites like ValueResearchOnline or morningstar few others to check performance of fund(s) from time to time. I have also referred to excel uploaded on various forums to check XIRR or CAGR but % shown are not really close to shown in my excel.
I am seriously confused how to validate or tally MFs true performance shown on various site vs mine?
Dear Krish,
Don’t trust the website. Calculate on your own.
how do calculate if I have increased SIP amount and have lumsum investment too
Dear Krish,
You have to enter those values with the dates.
Hi Sir,
How to calculate the return for multiple schemes (SIP) invested over a period of 3 years using XIRR?
Dear Shan,
Each of individual investment should be calculated separately and then arrive at portfolio returns.
hi,
Can you elaborate about Kuvera platform advertised along?
Dear Satish,
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.
Dear Satish,
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 child’s 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 ?
Dear Satish,
Go for Hybrid Funds.
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
Dear Sat,
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?
Dear Udita,
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-
DSP Tax Saver Fund Dir G-65K (on 2.5 K SIP)
DSP Small Cap Dir G – 1.10K (on 5K SIP)
SBI Bluechip Fund Dir G – 70K (on 5K SIP)
Mirae Asset Emerging Bluechip Fund Dir G (just started) – 5K (on 2K SIP)
PPF – 190K
NPS – 180K
I wont figure out the Debt:Equity calculation. Please advice.
Sorry, DSP Small Cap 110K.
Dear Udita,
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?
Dear M,
What is the time horizon of your investment? What asset allocation you are following?
My time horizon is at 15-20 years.
following is my approx allocation
samll cap 2funds 136000 37.00
large cap 2funds 61500 16.73
mid 2funds 67000 18.23
balance and elss 2funds 40000 10.88
large and mid cap 1fund 38000 10.34
multi cap 1fund 25000 6.8
Dear M,
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
Dear M,
How can I say this? It depends on how much volatility you are ready to digest.
Yes its my preference to bear fluctuations
Dear M,
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?
Thnks and regards
Dear M,
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.