Mutual funds have been a popular investment product for the masses for a very long time. They provide the investors with many benefits like steady returns, lower cost or investment, lower risk, diversified portfolio, tax benefits (ELSS), etc. In order to select a mutual fund for investment, the investor has to consider many factors like the historical performance of the fund, the average returns generated by the fund in the past years, expense ratio, fund manager’s experience, etc.
But calculating returns on the investment made has always been confusing for investors due to the presence of many different measures of calculating returns like CAGR (Compounded annual growth rate), ROI (Return on investment), IRR (internal rate of return), etc. Among these IRR is often considered to be a good measure of true returns.
Use the SIP calculator to check out returns on your SIP investments
What is IRR?
IRR is the Internal Rate of Return calculated to get an exact return on mutual fund investment based on the cash flows. These cash flows include systematic or periodic investments made by the investor at regular intervals or even a lump sum investment. IRR allows the investor to get a better or clear idea or returns as against the returns reflected in absolute terms, CAGR mode, or ROI which helps make better investment decisions.
CAGR (compounded Annualized Growth Rate) calculates returns based on the difference between initial investment and final investment value after considering the number of years of investment. ROI on the other hand is computed based on the returns earned by the investor while considering the initial investment without considering the years of investment.
In both the above cases, the investor does not get the correct picture of the returns earned on the portfolio. IRR provides the rate of interest at which the net present value (NPV) of the investment becomes zero. If such a rate is equal to or higher than the cost of capital, the investment is profitable.
Formula for IRR
Calculating IRR manually is a tedious job and involves a trial-and-error method. It is therefore advisable to use Microsoft Excel Sheets for calculating IRR on mutual fund investments. It is essential to note that IRR can be used to calculate the rate of return if the investment is made on a periodic basis or at regular intervals and is not scattered.
The formula for IRR is ‘IRR (Values, Guess)’
Consider the following example to get a better idea of IRR on mutual fund investment.
There are two portfolios of an investor, where the initial lump sum investment is Rs. 5,00,000, and the returns generated (units redeemed) in each following year are tabled below. The IRR for each portfolio is calculated using the IRR function in Microsoft Excel.
| Date | Portfolio A | Portfolio B |
| 1st Jan 2016 | -5,00,000 | -5,00,000 |
| 1st Jan 2017 | 50,000 | 2,50,000 |
| 1st Jan 2018 | 2,50,000 | 2,80,000 |
| 1st Jan 2019 | 1,50,000 | 1,50,000 |
| 1st Jan 2020 | 2,80,000 | 50,000 |
| IRR | 14% | 22% |
In the above example, the negative value represents the initial lumpsum investment and the positive value represents the amount redeemed each year. Although the absolute returns of the acquisition are the same (Rs.2,30,000). However, IRR shows a different scenario and thus helps the investor to decide among the profitability of the two portfolios.
What is XIRR for mutual funds? Significance of XIRR over IRR
IRR is used to calculate returns spread over years of investment. However, there are certain inherent limitations of IRR. It does not consider returns that are made at irregular intervals. In such a case, the returns are calculated using the XIRR function in Microsoft Excel Sheet. This function will give the true picture of returns when they are not made at regular periodic intervals. Returns on investment in mutual funds through SIP or lump sum mode or redeeming through SWP or lumpsum redemption can be calculated easily using the XIRR mode.
The formula for XIRR is ‘XIRR (Values, Date, Guess). The inputs needed for this function are,
- Date of investment
- Amount of investment
- Date of redemption
- Amount of redemption
Consider the following example to understand the calculation of XIRR.
An investor has two mutual fund portfolios where investment is made through SIP over a period of time.
| Date | Portfolio A | Portfolio B |
| 1st Jan 2020 | -10000 | -5000 |
| 5th Feb 2020 | -10000 | -5000 |
| 10th March 2020 | -10000 | -5000 |
| 1st April 2020 | -10000 | -5000 |
| 15th May 2020 | -10000 | -5000 |
| 25th June 2020 | 52000 | 26500 |
| XIRR | 13.84% | 21.21% |
In the above example, the investor makes mutual fund investments through SIP in each portfolio but at inconsistent intervals and redeems them at the end, hence, XIRR is used to calculate the returns. The negative values represent the amount invested through SIP and the positive value represents the amount redeemed. Although in absolute terms, Portfolio A has given more returns (Rs. 2,000) as compared to Portfolio B (Rs. 1,500), XIRR for these portfolios gives a better picture of the returns showing Portfolio B provides better returns to the investor.
Conclusion
As mentioned above, there are many modes of calculating the returns on mutual fund investment. IRR and XIRR provide the most optimum result of returns on investment. It can be easily used by any person having a basic knowledge of Microsoft excel and can thus make sound investment decisions
Frequently Asked Questions
IRR can be calculated using Microsoft Excel or manually as well. Calculating IRR using manual mode is through trial and error mode.
CAGR is the annualized rate of return on investment while IRR is the total return on investment that is made over a period of time. Also, CAGR does not consider periodic investments that are a common feature of investment in mutual funds.
ROI is the return calculated on basis of the difference between the initial investment and the current or final value of an investment divided by the initial investment. It is a basic rate of return that can be derived for any investment which can mislead the investors. IRR on the other hand is a realistic approach to calculate the rate of return on investment in mutual funds.
XIRR accounts for investments and redemption in mutual funds through SIP mode or lumpsum mode which need not be at regular intervals. IRR requires such investments to be at regular intervals for calculation purposes. Hence, XIRR is a better mode of measurement for the rate of return on mutual fund investment.
Related Articles
