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.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. 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.
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! (Image courtesy of Stuart Miles at FreeDigitalPhotos.net)