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 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!
Continue reading :
- Five important formulas to calculate returns on investments
- The importance of numeracy in becoming Financially Literate!
- Personal Financial Calculators – Tools to manage your Finances more easily!
(Image courtesy of Stuart Miles at FreeDigitalPhotos.net)
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.
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.
how to calculate when return is negative?
Dear Yogesh..i did not understand you query.kindly rephrase it..