IRR (Internal Rate of Returns) & XIRR (defined as internal rate of return for irregular cash flows) are the very important terminologies that will help you to understand the returns on your Investments in more detail. IRR is used to calculate the returns given some amount at a fixed interval. The only difference between XIRR and IRR is that IRR considers periods of equal duration (e.g. year 1, year 2, year 3, …) and XIRR allows periods with different durations. Let us understand XIRR with a simple example;

As explained above, to calculate returns from your investments when the investments you make (inflows) and returns you get (outflows) are at different periods in time, XIRR become useful to arrive at a correct result. Suppose you have been investing in a particular mutual fund scheme (assume HDFC top 200 Growth) whenever you have some surplus money and redeem some units whenever you are in a need of it. See the table below.

Investment Flow

This seems to be a complex situation to figure out how much return (compounding return) you have made since the day you made your first investment of Rs. 10,000 on December 1, 2000 till December 2, 2013. Using the formula of IRR, it may be difficult to workout actual compounding yield as the intervals between various investments are not regular, also there are irregular outflows. This can be calculated using the XIRR function.

How to calculate the performance using XIRR

XIRR Calculation using MS Excel

In an Excel sheet, first enter the original amount invested. The amount invested should be represented by a ‘minus’ sign (cash outflow) and any redemption should be presented ‘positive’ (cash inflow). You can now apply XIRR function by mentioning =XIRR(values,dates,guess)

Values refer to a series of cash flows that corresponds to a schedule of payments in dates. The first payment refers to the investment made at the beginning of the investment period and must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive and one negative value.

Dates stand for the date when the first investment was made and when the cash flows were received. Each date should correspond to its respective investment made or cash flow received as shown in the above table. Problems can occur if dates are not entered in the right format. If any number in dates is not a valid date, XIRR returns the #VALUE! error value.

XIRR is a more powerful function in Excel for calculating internal rate of return or annualized yield for a schedule of cash flows occurring at irregular intervals. Most financial instruments these days are generally giving irregular cash flows. This is where the XIRR comes into play. XIRR allows for uneven cash flow intervals by taking into account the dates of which a cash flow occurs. XIRR can also help to figure out investor’s returns (actual yield) on Mutual Fund schemes with dividend option whether payout or reinvestments, returns on ULIP considering various partial withdrawals from time to time as well as trading Moneyback or Endowment Plans.

What is XIRR and How to Calculate it?

2 thoughts on “What is XIRR and How to Calculate it?

  • October 9, 2016 at 5:35 PM
    Permalink

    I have invested in ICICI Mutual Fund and i am paying 4,000 Rs every month since Oct 2015. I have invested 48,000 Rs until Sept 2016 (starting from Oct 2015). The current value of my investment is 53,396. How to calculate my percentage of return on my investment. Can you please help me to arrive at the percentage of my above said investment?

    Thank you in advance.

    Reply
    • March 29, 2017 at 10:22 AM
      Permalink

      Hi Vinodh,

      As explained above, you will have to enter all monthly investments as -ve figures in excel in one column & keep the respective dates on left side of each cells in one column. In last cell of date and amount, you need to put current date & current value (+ve) respectively, then choose any blank cell to enter XIRR formula as “=XIRR(select all amount column; select all date column)”. That’s it!

      Reply

Leave a Reply

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

css.php
Read previous post:
E-payment mandatory for Excise or Service Tax of Rs. 1 lakh or above

Any payment of excise duty or service tax of Rs. 1 lakh or above will now to be paid electronically....

Close