How to calculate rate of return on mutual fund investments?

• Akshatha Sajumon
• Jan 12 2022
• 7 minutes
Share on

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.

What is IRR?

IRR is the Internal Rate of Return which is 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 taking into consideration 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 the 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 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 investment 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.

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.

How is IRR calculated manually?
IRR can be calculated using Microsoft Excel or manually as well. Calculating IRR using manual mode is through trial and error mode.

How is IRR better than CAGR?
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.

How is IRR better than ROI?
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.

Is XIRR better or IRR?
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

Share on