Calculate Rate of Return on Investments using XIRR function

Important Personal Finance Ratios

The XIRR function which is available in MS-Excel is a simple function which can be used to calculate returns on investments when irregular payments are made or received.

You can use XIRR function to determine the annualized returns on your investments like Mutual Funds, Stocks, insurance policies (Endowment / money-back / ULIPs), Chit funds, business ideas or any investments where you make some payments and receive some monies.

Example  Mr Gupta had invested Rs 1,00,000 in a Mutual Fund scheme on 1st January, 2010. He had opted for Dividend option. On 07-Jan-2010, 15-July-2011, 21-Aug-2012 and 01-July-2013, he had received dividends of Rs 500, Rs 600, Rs400 and Rs 800 respectively. He sold the units on 10-Oct-2014 and received Rs 1,20,000. What is the compounded annualized growth rate on his investment?

In the above example, Mr Gupta invested Rs 1 Lakh in MF and he received series of payments (Dividends). He redeemed the MF units and received the redemption amount. The payments happened on different dates. This is where XIRR function is very useful and can be used to calculate internal rate of return for irregular payments.

XIRR Function in MS Excel

XIRR function is available under “Formulas” menu option. Click on “Insert Function” to get the Functions list.XIRR function

You have to enter two variables in this function. First one is “Values” and another variable is “Dates.”  You need to provide amount values and date of payments as inputs. XIRR function values
You must enter atleast one positive and one negative cash flow. If any date precedes the ‘starting date’ then XIRR function returns #NUM error value. All the dates should be in similar and valid Date format

XIRR calculation – Example

I have calculated rate of return for the above example (Mr Gupta’s investment) using XIRR as below. XIRR Example

You may use below XIRR Calculator. You can edit the values and find out the rate of return on some of your investments.

Do you use XIRR function to analyze your investments? Do share your comments. Cheers!

Continue reading :

(Image courtesy of Stuart Miles at FreeDigitalPhotos.net)

About The Author

  • k vijaya bhaskar reddy says:

    hai sreekanth
    i want invest in Chits what is the rate of return ?
    is it a good saving instrument?

    k vijaya bhaskar reddy

    • Dear vijaya bhaskar ..It is not easy to calculate or estimate the returns from Chit investments. It depends on many factors.
      Personally, I avoid investing in chit funds. There are better investment options which are well regulated and are more transparent.
      If you decide to invest in Chits, you may opt for Registered Chit funds only.

  • Unmesh says:

    Hi Sreekanth,

    I have ULIP from ICICI Pru ( Life Time Premier Equity ).

    Tenure is 15 Years and Premium is 24000 anually.

    I have paid 5 premiums and the current Value or NAV is about 1,62,000 for premium paid 1,20,000.

    my question is how do i calculcate the returns policy has acquired so far ?

    thanks in advance,

    Unmesh

    • Dear Unmesh,
      You have to key in all the cash outflows (premiums amounts as negative figures) date wise and key in the cash inflow or the fund value as the positive value. You can then calculate using XIRR function.

  • Yogesh Shinde says:

    how to calculate when return is negative?

  • >
    Scroll to Top