Calculate Rate of Return on Investments using XIRR function

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 1^st 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!

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)