XIRR - investment return

XIRR: How to calculate returns on your portfolio?

by

It was a lazy Sunday morning. While I was sitting on the window ledge, sipping my morning cuppa tea, the door bell rang. I took the last sip and opened the door.

“Good Morning”, I saw Shekhar standing out wearing a broad smile.

“Good Morning Shekhar, long time!” I was seeing him after several days though he lived just next doors. We were good friends.

“Yes Vipin. Long time! And as always I have come to trouble you a bit.”

“Sure, tell me.”

“You know, I have invested in mutual funds. It’s been a few years now. But I have never been able to understand what is my return on these investments.

This time I happened to open my account statement. And that also doesn’t seem to show it. Can you tell me what’s the best way to calculate it?”

“Sure, why not?”

“Here it is.” Shekhar kept his laptop on the table. He opened it and turned towards me. On the screen was a PDF document with several entries of his mutual fund transactions including SIPs and lump sum.

“I see that I have invested approx. Rs. 2.31 lacs and the current value is Rs. 3.42 lacs. Does that mean my return is 42%?” Shekhar looked at me.

“Well Shekhar, it is your total return. But usually, we tend to measure returns on an annualised basis. One of the most used measures for the same is CAGR or compounded annual growth rate.

Now suppose if you invested Rs. 1 lac, 6 years ago and now it is valued at Rs. 2 lacs, then your CAGR is 12% approx.

The formula would be Rate of Return =((Current Value/Original Investment)^(1/no. of years)) -1. 

But this measure is good for single item investments.

Since you have made investments at several times and different amounts, CAGR is not the right measure. In such cases, we will use another method known as XIRR.”

“What is this XIRR?” asked Shekhar, with a sense of frustration. He was a sharp guy but not very familiar with the financial terms. Yet he was curious and hence he came knocking.

“XIRR or Extended Internal Rate of Return is a method to calculate returns on investments where there are several transactions happening at different points in time. Take the case of your own portfolio. You have run an SIP and made an initial lumpsum investment.

Now, while you can have a CAGR for each of these transactions, to know the return of the entire set, you will have to use XIRR.”

“OK. Can you help me understand this better on my investments?”

“Sure.”

Calculating XIRR

Let’s first copy all these transactions in an excel file.

  1. All transaction amounts have to be mentioned in one column. For the purpose of this calculation, all cash outflows will be marked as negative values such as an investment or a purchase, and all inflows will marked as positive values such as a redemption.
  2. Against every transaction, there has to be a date on which the transaction took place.
  3. In the last row, you should mention the current value of your holding or portfolio, along with the date on which the value is taken.

XIRR calculation

In the table, all red color numbers are the investment amounts. The final figure of Rs. 3.41 lacs is the current market value of these investments on June 15, 2015.

Now, we will apply the XIRR formula. In one of the cells below the table, enter =XIRR and a formula tip will appear, which reads something like this:

=XIRR(values, dates, [guess])

So, there are 3 key inputs that we will need for calculating XIRR – Values, Dates and a guesstimate return. We already have the first two in the excel file.

To supply the necessary information into the formula, select all the invested amounts and the current value figure for values and dates for the dates. 

For the guess input, you can choose to ignore it.

Click enter and the answer that we get is 11.1%. That is what your annualised returns are from the entire set of investments you made over this period. Makes sense?”

“Yes it does.” Shekhar paused and then continued.

“So, if I get it right, it is some sort of average return on each of my transactions. As I see, my earlier transactions that I did in 2009 or 2010 would have delivered a higher return, while the ones I made recently would have delivered a lower return for now. The simple reason is that the earlier transactions had more time to grow and compound.”

“Fantastic. I have always admired your sharpness and you prove it yet again. And I see that you still remember the idea of compounding.” I shared my appreciation with Shekhar.

“Oh, I can never forget it. If Einstein could call it the 8th wonder, then its importance cannot be lost on anyone.” Shekhar smiled.

“Yes, absolutely. Now from an application point of view, any investment portfolio with multiple cash flows happening over a period can benefit from calculation of XIRR. It could be your ULIP, endowment or money back policy, your stock market portfolio, your EPF, PPF or NPS account too.

It is also important to understand that XIRR is like your personal rate of return. People sometimes look at the returns shown by mutual funds for 1 year, 3 year, 5 years, etc. However, those are the fund returns. Your returns are determined by the size of your investment and the time when you made those investments. XIRR is your personal return on your investments calculated with these numbers.” I added further.

“Yeah, right. I totally get it now. I feel that everyone needs to calculate this return on the portfolio to know the true picture. Else, one can be misled in terms of what are the actual returns on the investments. I am going to calculate returns for all my funds today itself.” Shekhar had owned up XIRR completely by now.

“Thanks again my friend”, Shekhar shook hands real tight.

“Always a pleasure.”

Between you and me: Have you measured the returns on your investments? All of them. What is the XIRR of your portfolio?  What other measures of investment returns do you use?

Read more on how to know your mutual fund portfolio XIRR. 

30 thoughts on “XIRR: How to calculate returns on your portfolio?”

  1. How do you calculate XIRR when the portfolio contains Cash and market linked instruments? Ex: Added cash Rs 5,000 to the account on Day 1. Bought shares worth Rs, 4,500 on day 2. Spent Rs 100 for fees. Today value of shares is Rs 4,700 and cash balance isRs 400

    • IN the amount column, the amounts will be
      +5000
      -4500
      +4500
      -100
      -5100 (4700+400) (market value)

      Enter the dates in the next column and use the formula for xirr.
      Hope this helps.

  3. Hi Vipin,

    I have below mentioned mutual fund (SIP and lump sum) and their amount. Can you please verify if my portfolio looks good or do i need to add/remove any mutual fund.

    SIP
    DSP BlackRock Small and Mid Cap Fund (G) 1000
    Franklin India Smaller Companies Fund (G) 1000
    Kotak Select Focus Fund (G) 1000
    ICICI Prudential Banking and Financial Services Fund (G) 3000
    ICICI Prudential Top 100 Fund (G) 3000
    ICICI Prudential Balanced Fund (G) 3000
    ICICI Prudential Value Discovery Fund (G) 3000
    ICICI Prudential Infrastructure Fund (G) 3000

    Lumpsum
    ICICI Prudential Balanced Fund (G) 100000
    ICICI Prudential Capital Protection Oriented Fund SeriesXI Plan C 1255 Days-Cumulative (G) 300000

    • Dear Gopajit
      I have no background of why you built this portfolio and hence I cannot offer a view. In any case, these quick tips may not be as useful.
      You can do a quick analysis of your portfolio on Unovest.co.
      Thanks

      • Hi Vipin,

        My apologies for not providing complete info.I’m 31 years old married person working in an IT MNC. I built the above mentioned portfolio hoping to create wealth of around 1-1.5 cr after 20 years. My take home salary is 55000 per month. After paying the rent & bills, i can save around 20K. Besides the mutual funds I have invested directly in equity (both intra day & delivery) and 3 ULIPs (premium 1 lac each per year) (completed 4 years of 10) from HDFC. Just needed some professional insight on the mutual funds e.g. if they are good enough to build the wealth i am looking for or need to add/remove/modify anything from the same.
        Also, i can start another SIP of around 2000 per month into more aggressive fund (long term).
        Thanks

        • Dear Gopajit
          Your investment in MFs appear random. Not sure why you invested in banking fund, or an FMP, I am sure you have investments in EPF. Plus your ULIP fund. Do you know what is the fund that is working for you in the ULIP.
          What is the return on your direct equity investment?
          On the topic of goals, what will be the value of Rs. 1.5 crore in 20 years?
          Just to give you a hint – Rs. 30,000 a month expense today at 10% inflation, will be equal to 2.4 lakhs a month in 20 years. that is you will need Rs. 2.4 lakhs a month or approx. 30 lakhs a year to be able to live the same standard of living after 20 years. And inflation doesn’t stop.
          You should take a more structured approach and ensure that the limited resources are working to achieve your goals.
          Hope this helps.

  4. Kindly suggest me about this portfolio

    Me (age 36yrs): Wife (age 36yrs) Children

    Profession Doctor Doctor 3yrs Girl
    Take home after TDS 1,26,000 80,000 1yr Boy
    Term insurance 75 lacs 75lacs
    Family Floater 10 lacs
    Disability 50lacs 50lacs
    Home loan 28 lacs EMI 28,000/month
    Expense including EMI 90,000/month
    PPF 2,50,000 5,40,000
    Sukanya samridhhi 150,000
    RD FOR SON 40,000/month
    FD 4,00,000 3,00,000
    Jwellery 7,00,000
    Monthly savings around 1,10,000/

    My Approach.
    1.Fixed Saving PPF/Sukanya Samridhhi to continue to avoid market risk and returns are comparable to bond funds with added tax advantage
    2.Land our FD to Reliance liquid Fund and use its ATM for day to day use………7lacs also its 5yr return is comparable to short term corporate bonds and its a well managed fund
    3.Transfer RD to HDFC balanced funds …………………………20,000/month
    4.Going Aggressive portfolio
    1.Quantum long Term Equity 35%………good 5 yr returns
    2.ICICI Pru True value discovery Fund 10%………historical good performer
    3.Mirae asset Emerging Bluechip Fund 25%………good 5 yr returns
    4.L & T India Value Fund 10%…….. good performer,nice allocation in power/steel/oil/international
    5.HDFC balanced funds 20 %………historical good performer
    6.Not preferring ELSS over PPF because..though both are giving tax benefit and ELSS gives better result but I already have other investments for returns plus PPF replaces bond requirement

    Please suggest changes

  5. Thanks for the article, Vipin! Very helpful of you to write this.

    I have 2 questions.

    1. Does XIRR give you the annual compounded interest rate?

    2. What if I would like to know all the XIRR values at the end of all the previous months. Is there a way to do this? (Something like instead of 2 columns for amount and date, we have 3 columns for amount, date and XIRR until the end of that particular month/investment)

    Thank you,
    Rahul

  7. dear sir , i am small investor and have invested in some mutual funds. i need a tracking software so that i could know the latest their value instantly. thanking you

  8. Dear Vipin,

    I have two separate project which have their own outflow & inflow. I want to have separate XIRR & a combined XIRR. Separate XIRR is possible. How can I draw a combined XIRR without putting all value (of project 1 & 2) in one column.

  9. Dear Vipin,

    I came across your site and would appreciate your help if you are able. XIRR uses a 365 day count where as I require a 360/30 day count. This is to tie in with the PMT function which also works on a 360/30 day count.

    i.e. I have given loans to people and wish to review my overall returns.

    Is there a way to do this?

  10. Dear Vipul,
    Very good explanation on calculating and application of XIRR

    Keep it up with further knowledge sharing

    Thanks

  11. I want to calcualate returns from a particular stock so in this case how to calculate . Assuming I hold the stock for 3 years buy @rs 5000 and sold for Rs 9000.

    Secondly I have portfolio of 10 stocks intial investment amount of RS 120000 and todays market value is Rs3Lakh. Here the issue is I bought shares at diffrent dates between 2011 to 2016 . So please help me out in both the CASEs.

    Thank You

  12. Hi Vipin – a query on computing xirr for a portfolio of investments
    – if some investments are sold at a profit (or loss) and further if dividends are received how does the treatment of this be accorded while computing xirr??
    Regards

  13. I somehow landed on your website while searching around mutual funds. And i have spent almost an hour reading various articles. Very interesting and very well written. I like you! 🙂

    Bookmarking your page.

    Thanks
    Gagan

Comments are closed.