• Skip to main content
  • Skip to header right navigation
  • Skip to site footer
Site Logo

BasuNivesh

Personal Finance Blog

  • Home
  • Service
    • Fee-Only Financial Planning Service
    • Financial Wellness Session
  • Blog
  • Old Articles
  • About Us
  • Contact us
  • Media
  • Search

XIRR in Mutual Funds – What is this and how to calculate?

October 23, 2018by Basavaraj Tonagatti

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.

  1. First SIP on 5th June 2018
  2. Second SIP on 5th July 2018
  3. Third SIP on 5th August 2018
  4. Lump Sum investment on 16th September 2018
  5. Fourth SIP on 5th September 2018
  6. Fifth SIP on 5th October 2018
  7. Sixth and final SIP on 5th November 2018
  8. 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.

XIRR in Mutual Funds - How to calculate

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
Category: Mutual FundTag: XIRR in Mutual Funds

About Basavaraj Tonagatti

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 India's 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."

Previous Post: « Public Provident Fund Interest Rate 2018 Public Provident Fund Interest Rate 2018 and 50 Yrs History
Next Post: 10.4% Manappuram Finance NCD Oct 2018 – Is it worth to invest? 10.4% Manappuram Finance NCD Oct 2018 »

Reader Interactions

Comments

  1. krish

    December 28, 2019 at 12:10 PM

    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?

    Reply
    • Basavaraj Tonagatti

      December 28, 2019 at 4:40 PM

      Dear Krish,
      Don’t trust the website. Calculate on your own.

      Reply
      • krish

        January 2, 2020 at 10:59 AM

        how do calculate if I have increased SIP amount and have lumsum investment too

        Reply
        • Basavaraj Tonagatti

          January 2, 2020 at 11:38 AM

          Dear Krish,
          You have to enter those values with the dates.

          Reply
  2. Shan

    October 21, 2019 at 11:05 PM

    Hi Sir,

    How to calculate the return for multiple schemes (SIP) invested over a period of 3 years using XIRR?

    Reply
    • Basavaraj Tonagatti

      October 22, 2019 at 8:44 AM

      Dear Shan,
      Each of individual investment should be calculated separately and then arrive at portfolio returns.

      Reply
  3. satish

    November 15, 2018 at 3:59 PM

    hi,
    Can you elaborate about Kuvera platform advertised along?

    Reply
    • Basavaraj Tonagatti

      November 15, 2018 at 5:47 PM

      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.

      Reply
      • satish

        November 15, 2018 at 9:54 PM

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

        Reply
        • Basavaraj Tonagatti

          November 16, 2018 at 7:24 AM

          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.

          Reply
  4. satish

    November 15, 2018 at 2:25 PM

    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 ?

    Reply
    • Basavaraj Tonagatti

      November 15, 2018 at 5:43 PM

      Dear Satish,
      Go for Hybrid Funds.

      Reply
  5. sat

    November 5, 2018 at 4:15 PM

    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

    Reply
    • Basavaraj Tonagatti

      November 5, 2018 at 4:37 PM

      Dear Sat,
      It is CAGR not XIRR as they show point to point.

      Reply
  6. Udita

    November 2, 2018 at 2:28 PM

    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?

    Reply
    • Basavaraj Tonagatti

      November 2, 2018 at 2:46 PM

      Dear Udita,
      What asset allocation you are following between debt and equity?

      Reply
      • Udita

        November 2, 2018 at 9:22 PM

        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.

        Reply
        • Udita

          November 2, 2018 at 9:23 PM

          Sorry, DSP Small Cap 110K.

          Reply
        • Basavaraj Tonagatti

          November 3, 2018 at 12:27 AM

          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.

          Reply
  7. m

    October 30, 2018 at 10:50 PM

    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?

    Reply
    • Basavaraj Tonagatti

      October 31, 2018 at 10:36 AM

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

      Reply
      • m

        October 31, 2018 at 11:35 PM

        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

        Reply
        • Basavaraj Tonagatti

          November 1, 2018 at 9:27 AM

          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.

          Reply
          • M

            November 1, 2018 at 6:51 PM

            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?

            Reply
            • M

              November 1, 2018 at 6:53 PM

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

              Reply
              • Basavaraj Tonagatti

                November 2, 2018 at 8:50 AM

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

                Reply
                • M

                  November 2, 2018 at 4:20 PM

                  Yes its my preference to bear fluctuations

                  Reply
            • Basavaraj Tonagatti

              November 2, 2018 at 8:49 AM

              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?

              Reply
              • M

                November 2, 2018 at 2:37 PM

                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

                Reply
                • Basavaraj Tonagatti

                  November 2, 2018 at 2:48 PM

                  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.

                  Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Sidebar

Newsletter Signup

Subscribe to our weekly newsletter below and never miss the latest product or an exclusive offer.

Enter your email address

Categories

  • Banking
  • Consumer Rights
  • EPF and PPF
  • Insurance Planning
  • Investment Planning
  • Mutual Fund
  • Real Estate
  • Real Life Stories
  • Retirement
  • Tax Planning
  • Uncategorized

Subscribe to our YouTube Channel

Recent Posts

  • Interesting Tax Dispute – Whether Sachin Tendulkar Actor or Cricketer?
  • List of Index Funds in India 2021
  • 7.15% Power Finance Corporation NCD Bonds 2021 – Review
  • ENEMY No.1 of your investment
  • 39 Year Multimillionaire’s Tesla stock value is Rs.83 CRORE – What we can learn?
  • Best Investment Plans for 2021 in India

Are you looking for Unbiased, Simple and Conflict-Free Financial Planning Service?

We neither SELL any product nor representative of any Insurance or Mutual Fund Companies.

We offer you an unbiased Fee-Only Financial Planning Service.

GET STARTED

    Follow along on social media

Get in touch with us here

Address: Basavaraj Tonagatti,
1446, Aastha, Sir M Vishweshwarayya Layout, 5th Block, Bangalore-560056.

Phone: (+91) 9019580450

Email: tonhokrani@gmail.com

Subscribe to our newsletter

Newsletter Signup

Subscribe to our weekly newsletter below and never miss the latest product or an exclusive offer.

Enter your email address

© Copyright 2020

Return to top