What is IRR and XIRR and how to Calculate it

POSTED BY manish ON August 10, 2009 COMMENTS (119)

How do you calculate your returns when you every year you invest different amount and at the end you receive your Money back? Suppose your invest 5000, 10000, 6000, 4000 and 6500 in 5 yrs and Get 53,000 at the end of 5 yrs then what is your Return? It’s 17.4%. The concept is called IRR. Read below to understand more:

So Here we will learn two things IRR and XIRR

What is IRR and How to Calculate it?

IRR is Internal Rate of Return and it is used to calculate the returns given some amount at a fixed interval i.e. after every 3 months or after every 1 yr. The only thing which matters is that there should be equal distance between two installments. We will learn how to Calculate IRR in Excel Sheet. You would also love to read what is NPV ( Net Present Value) .

How to calculate?

  • Enter your Investments (amount which you paid) in each row (you have to put “-” before each value)
  • Enter the Amount you Received at the end (put “+” after that amount)
  • Formula: =IRR(values)( place your values put the range of cells which contains values)  see below:

What is IRR and XIRR and how to Calculate it
What is IRR and XIRR and how to Calculate it
[ad#big-banner] Use this Spreadsheet to calculate IRR for yourself

Things to NOTE

  • The values need to be a set of Positive and Negative Values
  • The last value is the amount you receive
  • Any amount Invested will be Negative so if you invest Rs 10000, put -10000
  • Any amount you Receive will be Positive so if you get Rs 5000, put +5000
  • All the payment or receiving of money are equidistant, Like 1st of every month OR May 15th Every year
  • All the payments are assumed to be yearly by default. If its’ some other time frame like monthly or quarterly use XIRR and put specific dates.

In the above example, the CAGR return was 17%. See this video post to understand how to calculate CAGR .

What is XIRR and How to Calculate it?

IRR does not solve one problem and that is when the payments are at Irregular interval. In that case we use XIRR. So in a Spreadsheet we put the date and the value both. See the example below:

How to Calculate

  • Put Date and Value for each row
  • At the last row put the Date and amount you received
  • Put the formula as: =XIRR(values, dates), values and dates are the cell ranges

What is IRR and XIRR and how to Calculate it
What is IRR and XIRR and how to Calculate it
Use this Spreadsheet to calculate XIRR for yourself

In the above example the CAGR Return was 38.96% (I have multiplied the return by 100 the actual value will be .3896)

Real Life scenario when you can use it

Scenario 1

Suppose you Invest in a Mutual Funds per month on your own , you invest on 15th of every month in year 2006

  • June 15 you invested 5000
  • July 15 you invested 6000
  • Aug 15 you invested 3000
  • Sep 15 you receive 5000 (dividend)
  • Oct 15 you invested 4000
  • Nov 15 you invested 12000
  • Dec 15 you Sell everything and Receive 35000

You can use IRR in this case and calculate your returns , the values you will be -5000 , -6000 , -3000 , +5000 , -4000 , +12000 , Calculate the IRR and put it as comments , lets see if you are correct or not ?

[ad#text-banner]

Scenario 2

You can also compare two business ideas using the XIRR , and decide which one is better then other . In any business concept you have to invest money and you get back some return , but these returns can be irregular and different amount every time , In that case you can use XIRR and compare the returns of both business and decide the one which has better XIRR

Note : the formula can give answers in a but different ways on Excel , OpenOffice spreadsheet , google docs or Zoho Spread sheet . Use this Spreadsheet to calculate IRR and XIRR for yourself . The spreadsheet is shared , so please dont make any changes other than “values” and “dates” .

Comments ? I would love to hear if these concepts are of use to you or can be of any help to you . is IRR a good way of measuring returns ?

Liked the post , Subscribe to Get Posts in Email or RSS Reader

Get FREE call back from Jagoinvestor Team

First Name*
Email*
Mobile*
City*
Yearly Income*
Time to Talk*
Which Package*
Your Requirement
Lead Source
Lead Status

119 replies on this article “What is IRR and XIRR and how to Calculate it”

  1. Sundar says:

    Hi Manish,

    I still dont get this IRR / XIRR calculation logic at all.

    Take my investment example: I have a SIP of Rs.3000/month from 1st June 2015 to 1st June 2016 amounting to total investment of 39,000 always done on the first working day of every month in a MF.

    Now the current value of this investment on 1st June 2016 is 40,498. Which is (40,498-30,000)/30,000 * 100 = 3.84 % – Absolute return. So 39,000 outflow of cash from me over a period of 13 stand at 40,498.

    But when i use XIRR for this scenario, i get the value as 15.61% where i was forced to provide a ‘guess’ in excel sheet formula and i gave it as 0.1.

    Could you please explain what exactly is the return on my investment ?

    1. Hi Sundar

      I am not sure how you calculated it. I used excel sheet on my end to calculate and I got 7.75% answer

      01-06-2015 -3000 7.75%
      01-07-2015 -3000
      01-08-2015 -3000
      01-09-2015 -3000
      01-10-2015 -3000
      01-11-2015 -3000
      01-12-2015 -3000
      01-01-2016 -3000
      01-02-2016 -3000
      01-03-2016 -3000
      01-04-2016 -3000
      01-05-2016 -3000
      01-06-2016 -3000
      01-06-2016 40,498

      Note that the 3.84% return answer is surely wrong, because that would be the answer if you had invested all 39000 at one go in start and if you got the 40,498 after 13 months, still that return would be absolute return , not a year return as it was 13 months , not 12 .

      Use the XIRR formula again and see ..

      I never put the GUESS value , it gave answer without that..

      Manish

  2. hds says:

    04-जनवरी-07 -5000 04-जनवरी-07 5000
    06-मई-07 -10000 06-मई-07 10000
    04-अप्रैल-08 -6000 04-अप्रैल-08 6000
    01-अगस्त-08 -4000 01-अगस्त-08 4000
    04-जून-09 -6500 04-जून-09 6500
    06-जुलाई-09 53000 06-जुलाई-09 -53000
    XIRR 38.96% XIRR 38.96%

    Sir,
    In above examples XIRR comes same, how it is possible, please clarify. All data are taken from your above cited example, only out flow & inflow sines are changed.
    Thanks.

  3. Rajdip says:

    how to calcullate roi from xirr as in majority of the calcullator they asks for roi along with duration and amount and then they gives the output.

    Hence need to know that formula.

  4. MMago says:

    Suppose the investments are from different sources and known is the overall project return. How do we calculate the returns due to the different sources that contributed different amounts at random times? Thank you and best regards.

    1. YOu need to combine all of them and just find out the outgo and incoming money at different times and then use XIRR function

    2. Krishna says:

      Hello,
      You can combine the amounts. Remember that money going out is -ve and coming in is +ve. So ideally, if your investments are to be computed for each purpose, as in investment in different instruments, it is best to calculate indvidual XIRR, ie , if I put 5k monthly in ULIP and ELSS for a period of 5 years, then I would, at the end of 5 years, want to know which has given me better returns, including dividends, money back etc. If I club both the instruments in one single bracket called investment, then my future investment decisions may go wrong.

  5. Tasfia says:

    Why is XIRR same for:

    (1)
    Time Cash Flow
    11-01-14 1000
    12-05-11 20
    13-01-01 30
    14-01-01 -2000
    24.888%

    (2)
    Time Cash Flow
    11-01-14 1000
    12-05-11 20
    13-01-01 30
    14-01-01 -2000
    15-04-01 0
    24.888%

    1. Why should;nt it be . you got 0 one a date, even if you have not written than in first example, its still there !

  6. Neeraj says:

    Hi Manish,

    I have XIRR values and portfolio values for three Mutual funds as below:

    MF Portfolio value, Rs XIRR
    A 5,500 26.4%
    B 6,500 16.3%
    C 6,000 -20.4%

    How will I calculate portfolio XIRR?

    Thanks

    1. Its not possible, because you dont have individual cashflow date wise. For portfolio XIRR , you need portfolio values !

  7. Vic says:

    Pls help with this cash flow issue. I used XIRR to determine the effective interest rate (EIR) of a loan with monthly repayment but the final balance isn’t giving zero but rather 66,927.74. Whereas using IRR, you arrive at zero as the final balance.

    Payment Date Opening bal. Interest at EIR Cashflow Closing Balance
    15-Jan-2010 (5,600,000.00) 5,600,000.00
    1-Feb-2010 5,600,000.00 88,804.03 1,000,000.00 4,688,804.03
    1-Mar-2010 4,688,804.03 115,662.41 1,000,000.00 3,804,466.44
    1-Apr-2010 3,804,466.44 103,902.88 1,000,000.00 2,908,369.32
    1-May-2010 2,908,369.32 76,867.53 1,000,000.00 1,985,236.85
    1-Jun-2010 1,985,236.85 54,218.33 1,000,000.00 1,039,455.18
    1-Jul-2010 1,039,455.18 27,472.56 1,000,000.00 66,927.74

    XIRR = 32.16%

    Thanks.

    1. Hi Vic

      https://www.jagoinvestor.com/forum is the right place to ask these questions !

  8. Divesh says:

    -5000
    -6000
    -3000
    5000
    -4000
    -12000
    35000
    11% IRR

    1. mayank says:

      its 10.63

  9. Puneet says:

    hi Manish.
    Thanks for this good article.

    I had a small question.
    To calcualte retruns on SIP or some stock scripts which i have bought over few months, can i use the WEIGHTED AVERAGE i.e. can i compare the weighted avg cost price vs current value to give me returns ?
    Basically i wanted to understand when to use IRR & when to use some other tool to calculate retrun.

    Thanks

      1. Puneet says:

        Hi Manish.
        What i meant was, suppose in invested monthly in a equity MF SIP @ 2000 INR/month for 21 months. Now the current value is 62k. So, if i calculate IRR it comes as 4% (isnt this too low return?). Where as if i calculate using Weighted Avg (wt avg purchase cost comes to 42k) – the retrun is 20k/42 k = ~48% ??
        Is this correct ?

        Thanks

        1. Yea .. the returns are too low, but then markets have moves like that only.. I hope you know that mutual funds returns are not guaranteed !

          1. Puneet says:

            Thanks for your reply Manish.
            Wanted your views on comparsion of returns basis IRR calculation & weighted avg ? My Avg Cost, because of the market fluctuation is 42k. While current return is 62k – so by this is my return 48% ?
            Which one is a better tool for returns calculator in this case specifically.

            1. Its IRR which you should look at !

          2. Naren says:

            Sorry, but the explanation provided and formula used for IRR is not correct for your specific case involving monthly cash flows. By default, Excel assumes annual time periods for IRR calculations, therefore, in your case, it is treated as 21 years (not 21 months), hence your cash flows get discounted very aggressively leading to the 3-4% figure that you get.

            1. ok , will check it out and correct it !

            2. Bhushan says:

              Agree with Naren, IRR assumes annual time period.

              Puneet,
              Use XIRR where you can put monthly dates and with that Rs.2000 invested for 21 months and 62000 as returns will give XIRR rate of 49.51%. It is indeed a very good return.

  10. DEBRAJ SENGUPTA says:

    NOT BEEN ABLE TO GET XIRR CALCULATION. THE DATE AND VALUE FIELD ARE ENTERED AS YR/MONTH/DT AND OUTFLOW IN NEGATIVE BUT THE CALCULATION SHOWS #VALUE. PLS HELP

    1. Can you share the snapshot ?

  11. Abhijit says:

    Simply awesome explanation. I didn’t look any other site after reading this article. I am trying to calculate XIRR of my MF portfolio.

  12. Rahaman says:

    Hi Manish,
    How I can calculate final value using IRR/XIRR. E.g. the use case I have is, say I know the approximate IRR rate of one of my insurance policy is 6% & I am paying 10000/yr for 20 yrs. I want to get an idea how much my final return value would be. I tried some function NPV/FV in excel, but could not figure out..

    Thanks,

    1. IRR is something which needs to be calculated backwords . 6% which you are assuming is just a scenario !

  13. ABC says:

    xirr is 12.94 and irr is 12.36%
    When the cash flows are at a constant rate – quarterly.. thought certain quarters have no cash flows

  14. ABC says:

    Date Cash Flows
    26/06/13 (160,488) 12.36% 12.94%
    26/09/13 –
    26/12/13 – IRR XIRR
    26/03/14 –
    26/06/14 –
    26/09/14 –
    26/12/14 –
    26/03/15 –
    26/06/15 –
    26/09/15 –
    26/12/15 –
    26/03/16 –
    26/06/16 –
    26/09/16 –
    26/12/16 –
    26/03/17 7,134
    26/06/17 7,293
    26/09/17 7,293
    26/12/17 7,213
    26/03/18 7,134
    26/06/18 7,293
    26/09/18 7,293
    26/12/18 7,213
    26/03/19 7,134
    26/06/19 7,293
    26/09/19 7,293
    26/12/19 7,213
    26/03/20 7,194
    26/06/20 7,273
    26/09/20 7,273
    26/12/20 7194
    26/03/21 7,134
    26/06/21 261643

    In the said cash flows which are at regular interval we are getting different IRR and XIRR . The difference is quite significant…
    Pls suggest

    1. The IRR assumes yearly payments

  15. Anand says:

    XIRR is showing the correct value. The value -1.64% is telling us that the value of investment is decreasing by 1.64% every year.

    1. Not decreasing, but what is the return till now !

  16. Karthikeyan says:

    Manish, I tried out XIRR with my mutual fund investments and wanted your insight on some observations. One of my MFs is at a loss (i.e. current value is lower than principal). When I use XIRR, the value is lower than the actual return.

    To quote an example, I invested in UTI Infrastructure in 2007-2008 in 13 SIPs of Rs. 2,500 each. The total investment is Rs. 32,500. The present value of my investment is Rs. 29,946. The absolute return is -7.86%. However, XIRR is coming to -1.64%.

    Isn’t it misleading that XIRR is showing a lower negative return? Or am I not interpreting this result correctly? Please guide me.

    1. Can you also put the dates which you have put . Because IRR is the main return you got , not the absolute return .

      1. Anand says:

        Let me give an example.

        If you invested 1000 Rs on 1-Jan-2009 and got 500 Rs only on your investment on 1-Jan-2013, then your absolute loss is 500 Rs i.e. 50%.

        But if you calculate XIRR over the period, it comes out to be -15.90%
        This is the loss every year.

        Got it?

        1. Yes I got that Anand , but not sure what is your confusion ?

          1. Anand says:

            I was trying to clear Karthikeyan’s confusion, not yours.
            I learnt this from you only!

  17. Amitav Paul says:

    Hi,
    I have never purchased or invested in any shares or egold or mutual funds. My earning is nominal and has no savings.Now I wish to invest a little amount in either egold or shares or mutual funds. Can you pliz help me where shall I invest to get a good return?
    I would really appreciate if you can help me out in this for one time as I am a layman when it comes to investment. Thanks.

    1. In long term, only equity can deliver you good return or real estate

  18. prasenjit says:

    is there any website/software which computes portfolio return(portfolio consisting of stocks,mutual funds, fixed deposits, gold etc) based on time weighted methodology rather than dollar weighted (which is similar to xirr)

    1. Try Mprofit or Perfios

      1. prasenjit says:

        great. trhanks a lot.

  19. kishore says:

    dear manish,
    you did good job….. you gave excellent information to us. i appreciate your kind information. i have question for you? can u help me out? i have some goals. and i would like to make SIP for each goal…. like car, (in 3 year, 6 lac ) flat ( 6 year, 75 lac) and retirement plan (2.5 cr.). i am working in private bank. my annual salary is 1.8 lacs. my age is 24. plz give me some information regarding how much invest in each SIP and how much amount., which kind of instruments and portfolio i will used for investment…plz help me out

  20. Rajaram says:

    thanks for giving good explanation

  21. Sweet says:

    Thanks guys …good information especially converting monthly to Annual IRR. which approximately equals IRR & XIRR values.

  22. Raj says:

    Hi Manish,

    My online MF SIP portfolio tracker is using XIRR instead of IRR to show the return being generated. Any reason behind the usage of XIRR even if it’s monthly SIP plan with equal interval?

    Thanks/-Raj.

    1. IRR just one case of XIRR ! .. Hence they might be using the highest standard ! .. Its like they must have implemented only XIRR so that all cases are handled, it might happen that some one is manually doing it on different dates and same algo will handle that case also , Whats the harm in that !

  23. A V Gosavi says:

    Dear Manish,

    I am really impressed. With your Reply that I consider IRR Or XIRR without me knowing what it is, I started searching on google, what are these terms. And before breaking my head on the mathematics involved, I got your Rejoinder to your Reply which explained what I needed with excellent analytical debate.

    Indeed great and many many thanks.

    Anand Gosavi.

    1. Thanks for your appreciation !

  24. Mike B says:

    If I have a series of outflows and inflows, and want to see what the rate or return is on this, can I use XIRR? I am working out a bisiness plan with monthly payments and revenues, and the net result of the costs and revenues is sometimes profit and sometimes loss.

    Must IRR/XIRR have all outflows, and the last one inflow? Or can they vary?

    1. You can use XIRR in this case , it can have multiple inflows and outflows

  25. Magesh Dhasayyan says:

    https://docs.google.com/spreadsheet/ccc?key=0AkUngjHf_bCldGZNRENhbFNvTnR1a25vUGM5MGhFeEE

    Shows a sample SIP investment calculation.
    Is my assumption correct?
    IRR(monthly-values) * 100= monthly-IRR %
    IRR(monthly-values) * 100 * 12 = yearly-IRR %
    XIRR(regular-monthly-values, date-values) * 100 = yearly-IRR %

    If XIRR() is an extension of IRR() with date (regular or irregular), why is there a 1 % difference between IRR result and XIRR?

    Thanks

    1. Will look at this soon … kindly email me

      1. Magesh Dhasayyan says:

        Ref. https://www.brighthub.com/office/project-management/articles/99310.aspx

        Added one more rate to the page. Monthly to Yearly IRR conversion calculated using ‘(1+r)^12 – 1’ is close to XIRR(). So I think that answers my question.

  26. SYAM says:

    DEAR SIR
    NICE ARTICLE SIR . EVEN THOUGH I UNDER STOOD THE CONCEPT OF IRR FUNCTION I AM UNABLE TO GET ANNUAL RETURN RATE FOR MONTHLY SIP AMOUNT BY USING XIRR FUNCTION I AM GETTING #NAME AS ANSWER. CAN YOU KINDLY CLARIFY .

    1. Can you put all the numbers with date here ?

  27. Lino Asir says:

    Really good one, which explains the IRR & XIRR in a simple and value added way.. Thank u…

    1. Lino

      Good to hear that you learnt it easily 🙂

      Manish

  28. Wakas says:

    Hi Manish,

    So going over your definations, what I understand is that we use XIRR when there are irregular intervals between the payments? (Since IRR assumes regular intervals between payments).

    So if I am calculating my returns on equal distance semi-annual cashflows, using IRR and XIRR should give me same results? Or should I not be using the XIRR at all?

    Does IRR consider investments on an annualized basis by default?

    1. Wakas

      Yes .. IRR and XIRR should give the same answer incase of equi distance payments . Note that IRR will assume that the payments are per year .. incase its for monhtly basis , then all you need to do is consider the returns as monthly and then convert it to yearly returns

      Manish

  29. Debanjan Ray says:

    XIRR formula does not seem to be accurate. I have calculated CAGR with trial-and-error basis and the result is coming significantly different from XIRR especially when CAGR value is more. See the excel sheet in:
    https://spreadsheets.google.com/ccc?key=0Apwu-qirgStCdDlMV0RSTmFiUGFZMjFtZDFlU0NnVlE&hl=en#gid=0
    My objective was to plot a graph of GAGR agains time to see that how CAGR is varrying and at what goal, one can sell a MF, if he reaches goal.

    1. Debaranjan

      What is “Trial and error” in this case ? XIRR can not be wrong , if thats the case you are challanging Microsoft patents !

      Manish

      1. Debanjan Ray says:

        Hi Manish,

        “Trial and error” is a mathematical (numerical) method to find solution of a equation when direct solution is either difficult or not possible. In case of finding CAGR with a SIP, the formula is Y= (X *(1+r/12)^n-1)*12/r, where Y=Present market value of Mutual Fund ( = present NAV * no. of units of Mutua Fund), r= Annual CAGR, n= no. of months of SIP. Known values are Y, X and n. Our goal is to find out r.
        Now, as you can see it is an equation of nth degree polynomial, which is difficult to solve. In numerical analysis, iterative techniques like newton-raphson method or method of bisection can be used, by guessing a CAGR value, and then calculating error (difference) between left side and right side of the above equation. Then process would be continued till the error value is well within accepted limit ( you can never get perfect solution).
        Internally XIRR also does iteration, as described in Microsoft help. Actually XIRR has three parameters: XIRR(values, dates, guess) . In general, to start any iteration, an initial value is required. This is the “guess” value i.e. the third parameter. If you do not give the “guess” value, XIRR would assume something random, and the solution can become erroneous.

        Microsoft claims “XIRR cycles through the calculation until the result is accurate within 0.000001 percent.” In your article, you have skipped to enter the 3rd parameter. This can be possible chance of variation in the example excel sheet I have given in my earlier post.

        I gave an example in the excel: a SIP on HDFC Top 200, starting from 5-Apr-2010 with Rs. 1000, in the 7th month (5-Oct-10). When I gave 57.5% CAGR manually, error value was -3.10, whereas XIRR calculated CAGR was 75.17% resulting into an error of -374.28, whose absolute value is far more than my manual method. The anomaly increases as the CAGR increases.
        I check with the SIP calculator with https://www.moneycontrol.com/ . It is also in line with excel.
        May be I am wrong; but I need to know where I am wrong. Did I use wrong formula of CAGR? But, I derived CAGR formulae from basic; it is sum of G.P. series.

        With best wishes
        Debanjan

        1. Debaranjan

          Looks like you did lot of study on this 🙂 . good work

          I think there can be one point we are missing , we are not sure if the payments are considered at the end of the period or the starting of the period because thats the only reason I can think for the difference we are getting .

          Manish

          1. Debanjan Ray says:

            Hi Manish,
            I have considered payment at the starting of the period. So the last SIP will run for zero duration. This means in the previous example, the 7th month SIP will not have any gain. At the beginning of 7th SIP, the 1st month SIP (=X) will run for 6 months growing to X*(1+ r/12)^6; 2nd month SIP will grow to X*(1+r/12)^5 and so on. Now do a simple addition.
            At the end I can not trust XIRR; but can trust on basic mathematics of compound interest formula.
            – Debanjan Ray

            1. debranjan

              I think you should go with the first principles only which is working for you , this whole topic will actually become a big project to study if we do it

              Will take up this some time later , In between if you find something insightful in your studies , let me know

              Manish

            2. Anand says:

              It’s not the question whether you trust XIRR or not. It’s like an eternal truth.

              And excel does excellent job in giving you the answer; otherwise you have to break your head in mathematical formulas.

        2. Anand says:

          Debanjan Ray, can you just give me a small example where XIRR formula of excel returns false value?
          I want to investigate.

      2. Rupert says:

        Dear Manish,

        Could you tell me after calculating IRR how we can tell our client about annualized return say for example he invested 5000 for 11 months (SIP) and latest value is 56504 please guide me and which is good IRR or XIRR ???

        1. Rupert

          You can use IRR here , XIRR and IRR are same things ,just that XIRR is for irregular payments !

          1. Rupert says:

            Thanks for your reply I got it but when you want to convert to annualised how we have to do and which is the better irr or xirr ??

          2. Rupert says:

            Thanks for your reply I got it but when you want to convert to annualised how we have to do and which is the better irr or xirr ?? How to do annualised for an a SIP investments

            1. What ever IRR you get from for monthly payments , just do this

              (1 + Monthly-IRR)^12 – 1

              That would give you yearly IRR

              Manish

          3. Seenivasan says:

            Thanks Sir

            This calculation is very useful for my final CFP exam

  30. ankita says:

    Hey your website is of gr8 help to people.
    Thanks

  31. SKDhawan says:

    Thanks for explaining the cocept in such an easily understadable language. Please continue this as your contribution towards educating the educated illiterate.

    1. Welcome 🙂 . Did you come to this article after seeing the video ?

      Manish

  32. Lalit Bajaj says:

    Problem to install analysis toolpak, pls guide

  33. Jayaprakash Kanreddy says:

    Hi Manish,

    Excellent information. How can I use this IRR/XIRR to check the return I’ve got in MF SIP invested over a period of 3 years? For example investing monthly 1k over 3 years and current value is 42k. So how much is the return, which formula should we use to calculate the return in this case?

    1. If the gap between the investments is same , then use IRR else use XIRR

      Manish

    2. Dinesh says:

      Download CAMS APP and you get the returns. No need to break your head calculating unless you want to study.

  34. Akash Acharya says:

    Indeed explained well, my first google search reading for XIRR and I could do this myself.

    1. Akash

      thats great to hear 🙂

      Manish

  35. Raju says:

    Thanks for the information. Very clearly described. I appreciate your effort.

    1. Raju

      Thanks for the comment , keep commenting

      Manish

  36. sandeep says:

    i understand this will not work in case I’ve lumpsum invested for x yrs, right. IRR/XIRR is only for regular payments, like SIPs,yrly ULIPs etc, right ?

    1. Sandeep

      If you have invested one time then its more easy to find the CAGR .

      CAGR = [(final_amount/amount_invested)^(1/tenure)] – 1

      Manish

  37. Pingback: Personal Finance « Resources
  38. Manish Jain says:

    Hey Manish,

    We are pointing people to your post to explain XIRR.

    .-= Manish Jain´s last blog ..Asset Class: Bond’s =-.

    1. manish says:

      Thanks Manish

      I can see some people coming in from that link when i checked my live traffic 🙂

      Manish

  39. mona says:

    very gud info Manish.

    Thanx. Keep going.

    1. manish says:

      thanks mona .. keep visiting 🙂

      Manish

  40. VSN says:

    Does XIRR provide annualised return

  41. manish says:

    @Wayan

    I think you should be using some Numerical method to compute XIRR .. There wont be just one formula where you can stuff values and get answer .

    Manish

  42. Wayan says:

    Can anyone give me an example of the equation to calculate the XIRR?
    I want to make an application that calculate an XIRR like excel does. I used the Secant Method to compute the cash flow, but it still have a different result than excel does.
    Any help would be appreciated
    Thanks

  43. Manish Chauhan says:

    @Whitelotus

    yes , it means the annual CAGR return

    @karan

    https://www.investopedia.com/terms/m/mirr.asp

    1. Wayan says:

      Can anyone give me an example of the equation to calculate the XIRR?
      I want to make an application that calculate an XIRR like excel does. I used the Secant Method to compute the cash flow, but it still have a different result than excel does.

  44. Karan Batra says:

    I've also heard a term by the name of MIRR.. NAy idea regarding what is it??

    1. khagesh says:

      irr is used as a concept to calculate the break-even discount rate when u have a conventional cash flow, i.e., one outflow followed by multiple inflows during the entire life of the project

      but in case of non-conventional projects, where we have multiple outflows during the life of the project, we use the concept of mirr (modified int rate of return), because simple irr will give us multiple values. using irr will give us a number of discount rates, equivalent to the number of outflows.

      1. Khagesh

        Isin’t XIRR ? whats the difference between XIRR and MIRR ?

        Manish

  45. White Lotus says:

    IRR/XIRR are good for relative comaparisions.

    But what does it mean in absolute terms? Does a 17% IRR mean I get 17% annualised return on an average for every installment?

    WhiteLotus

  46. Manish Chauhan says:

    @Nikhil

    What other information do you need ? I thought that this can be called as the "information on how IRR and XIRR can be used" ?

    @Yogi

    Thanks 🙂

  47. Yogi says:

    wow, I good formula.

    I ll search excel for somemore..hehe

    Regards
    Yogi

  48. Anonymous says:

    Very Nice Information..With IRR Function You use Insurance Cash Flow…

    Manish Can You make One Excel Sheet about How To Use IRR & XIRR Formula With Complete Details ..

    Thakns..

    Nikhil Shah

  49. Manish Chauhan says:

    @Aditya

    So when you put the formula for IRR or XIRR , you put some random value like .1 or .2 also as Guess value , so formula looks liks

    =IRR(values , guess value)
    = XIRR(values , dates , guess value)

    Just put .1 or .2 in place of guess value. I hope it will work .

    To download the spreadsheet , go here :
    https://www.jagoinvestor.com/2008/02/stuffs-to-download.html

    To download videos from youetube you can use keepwid.com tool .

    @Sriram

    Thanks 🙂

    Manish

  50. Sriram says:

    Nice writeup Manish.

    Aditya
    If you are using MS-excel, you need to have analysis toolpak installed to get XIRR. Go to Tools -> Add Ins -> Analysis Toolpak

  51. aditya M says:

    nice information
    keep it on

    really good

    P.S.

    I am unable to use XIRR formula
    its asking for a guess value in excel please clarify

    Also at many offices-Its not possible to view your video tutorials or excel spreadsheets

    Please see if you can do something

    Aditya

Comments are closed.

Subscribe to Emails

More than 1,50,000 investors across the world trust & read Jagoinvetor articles. Subscribe for emails updates and get instant access to a free ebook.

Jagoinvestor is one of the most trusted personal finance websites which writes on various topics like financial planning, mutual funds, insurance and banking.