Demystifying portfolio returns using XIRR

Share on whatsapp
Whatsapp share
Share on twitter
Tweet it out
Share on facebook
Share on FB
Share on linkedin
Post on LinkedIn

When talking about investments in a mutual fund or a portfolio of funds, one of the most important and also widely misunderstood terms is ‘returns’. Our earlier article on the different types of returns and how one should and should not use them can be found here

This article delves into the ‘XIRR’ or extended internal rate of return and how it can be the best way to arrive at the returns on your portfolio that is unique to you, because two people can invest in the same fund but have very different returns.

XIRR, XIRR article, what is XIRR?, Portfolio XIRR

What is XIRR?

XIRR is a very handy spreadsheet function that calculates the internal rate of return for a set of non-periodic cash flows – i.e., cash flows that are not occurring at fixed intervals of time (this is why the XIRR is preferred over IRR, since the IRR needs steady periodicity in cash flows). For this reason, it is the best way to arrive at the returns on investments in a fund or a portfolio of funds / investments, since investments can be made on any day. 

The XIRR function does the computation by trial and error and so there is no equation or formula with Greek letters that can be memorized to compute XIRR by hand or even with a calculator. 

The only data points that are needed are the investment and redemption (or current value) amounts and the corresponding dates. The interval between the investments need not be uniform and the amounts need not be the same. There can even be multiple investments and redemptions interspersed with one another.

Using the XIRR function in a spreadsheet

The XIRR function is fairly straightforward to use and is only particular about two things.

  1. The transactions have to be entered with valid dates. The dates have to match the transactions in the same row. (It is not mandatory to enter the transactions in chronological order and as long as the transaction value matches the date in the row, XIRR will be computed correctly).
  2. Investments have to be entered as negative values (as they are cash outflows for you). Redemptions (as they are cash inflows, bringing money back to you) have to be positive values. Use the current value if you are still invested.

Let’s look at an example of yearly investments in a mutual fund with a current market value of Rs. 8,78,346 as of 12th Aug 2021.

To calculate XIRR for these transactions, the data points have to be arranged keeping in mind the two points above. If you would rather not show investments and redemptions (or current market value) as negative and positive numbers, you could just enter them as absolute values in separate columns as shown below and have a column (column D in the example below) to convert them to +/- as the case may be with a simple formula. This has been demonstrated for you below.

XIRR in excel - 1

The above would result in values as shown in the image below.

XIRR in excel - 2

The final step is to enter the XIRR function which needs two inputs - cash flow amounts and cash flow dates (which can be found in columns D and A respectively). 


The excel formula, XIRR(cash flow values, dates), will get you the IRR for this set of transactions. For the above example, the formula would be =XIRR(D2:D7,A2:A7).

XIRR in excel - 3

(Please note that you could also directly feed in the numbers using the appropriate +/- as in column D and skip columns B and C.)

This gives us the XIRR of 19.21%, which is the return from the investments made in this fund.

Computing returns for a specific period

The XIRR function can also be used to arrive at the returns for a specific period of time, like the year 2020. We just have to make a few tweaks to the above illustration. Let’s assume you have an investment in a fund that has stayed invested for several years and the market value as of 31st December 2019 stood at Rs.1 lakh. Additional transactions were done during 2020 as follows.

  1. Invested Rs. 5,000 on 1st March 2020
  2. Invested Rs. 5,000 on 1st September 2020
  3. Redeemed Rs. 5,000 on 1st December 2020

Market value as of 31st December 2020 stood at Rs. 1.2 lakh.


The same spreadsheet and XIRR function can be used to compute the returns for the year 2020 by simply treating the market value of the investment as of 31st December 2019 (the start of the time period you want to compute returns for) as an investment made at the start of the year. All of the other steps remain the same. For this example, you should arrive at an XIRR of 14.2% as shown in the image below.

XIRR in excel - 4

Our handy template

Alternatively, if you want to make things a little easier for yourself, simply download our readymade template spreadsheet below, which has XIRR and cash flow equations pre-filled up to 500 rows. All you need to do is enter initial and final market values and transactions during the period, along with their corresponding dates. You don’t even have to worry about the plus and minus signs for redemptions and investments because our template will take care of that for you. Just enter the investments in the column titled ‘Investments / Initial Value’ and redemptions in the column titled ‘Redemptions / Final Value’.

XIRR Calculator (Excel download)

XIRR or CAGR?

While reading up on any fund, you would have come across 1-year, 3-year, and 5-year returns. These are compounded annual growth rates or CAGR. A 5-yr return of 17% CAGR would mean that an investment of Rs.100, made 5 years ago, would currently be worth Rs.219 (compound interest of 17% for 5 years). However, this would only hold true if you invested a lump sum of Rs.100 in the fund 5 years ago and have not made any additional investments or redemptions since.

Most often this is not the case and if you, like many others, have made multiple investments into and redemptions out of the fund, CAGR will no longer be the right metric to use to arrive at an accurate returns figure. This is where the XIRR function wins with its ability to smooth out the effects of various inflows and outflows and compute a single rate of return.

XIRR for Portfolio Returns

Just like it is unlikely that you have a lump sum investment in a mutual fund with no activity until you hit the 1, 3, or 5-year mark, it is also unlikely that you have investments in just one fund (in fact, you probably shouldn’t!). You could have investments in one fund that is performing exceptionally well but there could be losses in another fund that are eating away the gains made and it is important to see how your portfolio is performing as a whole. Here too, XIRR will be the best way to understand overall portfolio returns.

Tip : If you are looking to arrive at the returns of your mutual fund portfolio, you can get the portfolio valuation as on a specific date from the consolidated account statement from Cams or Kfintech. Go ahead and take a look at how your portfolio has performed in 2020 – a year like no other.

To apply the XIRR metric to a portfolio of funds, note the following:

  • You do not need to calculate the XIRR for each individual fund. A portfolio’s XIRR is not the average of the XIRR of your funds (yes, even if you take a weighted average based on each fund’s share). Calculating the XIRR for an investment needs the date-wise inflows and outflows – in this case, it is your entire portfolio for which you want the returns. A fund’s weight will constantly change based on when you invest, how much you invest, and how the fund performs. Essentially, treat the full portfolio as an investment with inflows and outflows.
  • You need to enter all of the inflow and outflow (or current value) details, with corresponding dates for the entire portfolio, irrespective of the fund that the transaction pertains to. Then apply the XIRR function to the entire set of transactions. This will give you the aggregate IRR for your entire portfolio.
  • Treat any redemption in a fund as an outflow for your portfolio. If you reinvest the money, it will be an inflow for the portfolio. Simply put these redemptions and reinvestments with the corresponding dates. This will take care of any fund-level changes you’re making to your portfolio.

Still confused? Here's a very simple example.

You invested in Fund-1. Then you later switched it to a different Fund-2, and again later redeemed it completely; The transactions are given below:

Fund-1

2019-Jun-2: invested Rs 1 lakh

2020-Jun-1: (completely) redeemed Rs 90 thousand

Fund-2

2020-Jun-1: reinvested Rs 90 thousand

2021-Jun-1: (completely) redeemed Rs 1.3 lakh

The XIRR for each fund and the portfolio can be calculated with the XIRR calculator spreadsheet.

XIRR of Fund-1: -10%

XIRR 5

XIRR of Fund-2: 44.44%

XIRR 6

XIRR of portfolio: 14.02%

XIRR 7

As you can see, for one, the average XIRR of the two funds is 17.22%, which is not equal to the portfolio returns of 14.02%. Even if you had, say, a third or fourth fund, the average of these fund returns will not be the same as your portfolio. Two, any redemption and reinvestment you make is automatically factored into the portfolio returns. 

Therefore, all that a fund XIRR and a portfolio XIRR needs is a fairly simple process of inputting all investments and all redemptions date-wise. Of course, the more the transactions, the more the work you have in putting all these details in!

Share on whatsapp
Share via Whatsapp
Share on twitter
Tweet it out
Share on facebook
Share on FB
Share on linkedin
Post on LinkedIn

More like this

Please note that any specific queries on any of our recommendations will be answered ONLY through email. If you are a subscriber, please mail contact@primeinvestor.in.  Only general queries or discussions will be answered through the comment section of the blog. For full details, please refer to this post – How to communicate with PrimeInvestor.

5 thoughts on “Demystifying portfolio returns using XIRR”

  1. Hi, which is better TWRR or XIRR ? Same fund in different PF/SIPs shows different XIRR.will it happen with TWRR as well ? In one SIP is going on & in other it was stopped.
    TWRR and XIRR are they in simplified view giving us compounded annual growth rate ?
    Thanks.

  2. Tavinder Singh Saran

    Very informative & helpful, thanks a lot Bipin. I wonder why MF investment platforms like FundsIndia can’t show this metric to its end users the way they depict Annualized return or Absolute return? Any thoughts?

    1. We generally don’t comment on individual platforms. But we’ll make an exception here to clarify in the case of FundsIndia’s platform alone, since we have used it; they do show the portfolio XIRR for each portfolio created. Annualised return, where the period is more than 1 year, is the portfolio/fund actual XIRR. – thanks, Bhavana

  3. Sivasubramanian Viswanathan

    HI, can we calculate for stocks with different years to current years to find out XIRR?
    Say 2007-100, 2009-50, 2010-10. 2011-100, 2015-150, 2021-500 – How do get XIRR for this stock not a SIP format, but adhoc?

    Thanks
    Siva

    1. Bipin Ramachandran

      Hello, to calculate XIRR, we need cash inflow(s) and outflow(s). In the case of a stock, you may calculate cashflows first then find XIRR. Say hypothetically, in the example you’ve given, one has invested Rs 1 lakh each in year 2007 and 2015 and holds it till date. In that case, calculation will be as below:

      Date: 2007-01-01 (assuming quotes are of 1st Jan)
      Price: 100
      Investment: Rs 1 lakh
      Purchased quantity: 1000

      Date: 2015-01-01
      Price: 150
      Investment: Rs 1 lakh
      Purchased quantity: 666.67

      Date: 2021-01-01
      Price: 500
      Available quantity: 1666.67
      Current value: Rs 8,33,333

      Using the dates and cashflows, we get XIRR = 13.85%

Leave a Comment

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Register for FREE!

Gain instant access to more PrimeInvestor articles, researched products, and portfolios

The essence of PrimeInvestor

Register for FREE!

Gain instant access to more PrimeInvestor articles, researched products, and portfolios

Legal Disclaimer : PrimeInvestor Financial Research Pvt Ltd (with brand name PrimeInvestor) is an independent research entity offering research services on personal finance products to customers. We are a SEBI registered Research Analyst (Registration: INH200008653). The content and reports generated by the entity does not constitute or is not intended to constitute an offer to buy or sell, or a solicitation to an offer to buy or sell financial products, units or securities. All content and information are provided on an ‘as is’ basis by PrimeInvestor Financial Research Pvt Ltd. Information herein is believed to be reliable but PrimeInvestor Financial Research Pvt Ltd does not warrant its completeness or accuracy and expressly disclaims all warranties and conditions of any kind, whether express or implied. The services rendered by PrimeInvestor Financial Research Pvt Ltd are on a best-effort basis. PrimeInvestor Financial Research Pvt Ltd does not assure or guarantee the user any minimum or fixed returns. PrimeInvestor Financial Research Pvt Ltd or any of its officers, directors, partners, employees, agents, subsidiaries, affiliates or business associates will not liable for any losses, cost of damage incurred consequent upon relying on investment information, research opinions or advice or any other material/information whatsoever on the web site, reports, mails or notifications issued by PrimeInvestor Financial Research Pvt Ltd or any other agency appointed/authorised by PrimeInvestor Financial Research Pvt Ltd. Use of the above-said information is at the user’s own risk. The user must make his own investment decisions based on his specific investment objective and financial position and using such independent advisors as he believes necessary. All intellectual property rights emerging from this website, blog, and investment solutions are and shall remain with PrimeInvestor Financial Research Pvt Ltd. All material made available is meant for the user’s personal use and such user shall not resell, copy, or redistribute the newsletter or any part of it, or use it for any commercial purpose. PrimeInvestor Financial Research Pvt Ltd, or any of its officers, directors, employees, or subsidiaries have not received any compensation/ benefits whether monetary or in kind, from the AMC, company, government, bank or any other product manufacturer or third party, whose products are the subject of its research or investment information. The performance data quoted represents past performance and does not guarantee future results. Investing in financial products involves risk. Investments are subject to market risk. Please read all related documents carefully. As a condition to accessing the content and website of PrimeInvestor Financial Research Pvt Ltd, you agree to our Terms and Conditions of Use, available here. This service is not directed for access or use by anyone in a country, especially the USA, Canada or the European Union countries, where such use or access is unlawful or which may subject PrimeInvestor Financial Research Pvt Ltd or its affiliates to any registration or licensing requirement.

Aditya Birla Mutual FundAxis Mutual Fund Baroda Mutual FundBNP Paribas Mutual FundBOI AXA Mutual FundsCanara Robeco Mutual FundDSP Mutual Fund Edelweiss Mutual FundEssel Mutual FundFranklin Templeton Mutual FundHDFC Mutual FundHSBC Mutual FundICICI Mutual FundIDBI Mutual FundIDFC Mutual FundIIFL Mutual FundIndiabulls Mutual FundInvesco Mutual FundITI Mutual FundKotak Mahindra Mutual FundL&T Mutual FundLIC Mutual FundMahindra Mutual FundMirae Asset Mutual FundMotilal Oswal Mutual FundNippon India Mutual FundPGIM Mutual FundPPFAS Mutual FundPrincipal Mutual FundQuant Mutual FundQuantum Mutual FundSahara Mutual FundSBI Mutual FundShriram Mutual FundSundaram Mutual FundTata Mutual FundsTaurus Mutual FundsUnion Mutual FundsUTI Mutual FundsYes Mutual Funds

Equity: Large Cap Funds | Mip Cap Funds | Large And Mid Cap Funds | Small Cap Mutual Funds | Contra Mutual Funds | Dividend Yield | Focused Mutual Funds | Find Top Index Funds | Best Sector Funds | Thematic Mutual Fund | Best Value Mutual Funds | Equity Linked Savings Scheme | Tax Saving Funds
Debt: Banking And PSU Funds | Corporate Bond Funds | Credit Risk Funds Mutual Funds | Dynamic Bond Funds | Floating Rate Funds | Gilt Mutual Funds India | Find Top Liquid Funds In India | Long term debt funds | Low Duration Funds Debt Funds | Medium Duration Debt Funds | Medium To Long Duration Funds | Money Market Debt Funds | Overnight Debt Funds | Short Duration Debt Funds | Ultra Short Term Debt Fund
Hybrid: Aggressive Hybrid Funds | Arbitrage Mutual Funds | Balanced Advantage Mutual Funds | Conservative Hybrid Funds | Dynamic Asset Allocation | Equity Saving Funds | Multi Asset Funds | Multi Asset Allocation

Mutual fund rolling returns by category: Balanced Advantage | Conservative Hybrid Fund | Corporate Bond | Dividend Yield | Dynamic Bond | Equity Linked Savings Scheme | Floating Rate | Index Funds | Large and Midcap fund | Large Cap Fund | Liquid funds | Low Duration | Mid Cap Fund | Multi Cap Fund | Short Duration | Small cap Fund | Solution Oriented – Childrens Fund | Ultra Short Duration

Login to your account
OR