XIRR In Mutual Funds in 2022: Step by Step Calculation Of XIRR In MF
XIRR in mutual fund
There are different ways of calculating returns for mutual fund investments. These methods include absolute returns, compounded annual growth rate (CAGR), extended rate of return (XIRR), etc. Absolute returns and CAGR methods are useful for lump sum investments. But, for SIP investments, the XIRR method is the more appropriate. In this article, we will understand the step by step calculation of XIRR in MF.
Relation between XIRR and mutual funds
For mutual fund investments and redemptions, most people use systematic methods like systematic investment plan (SIP) and systematic withdrawal plan (SWP). These methods include regular investments or withdrawals, for example, monthly. The investment and redemption amounts and dates may also vary.
Using SIP and SWP in mutual fund investments: Rajesh starts a SIP of Rs. 15,000 a month for 30 years in an equity-linked savings scheme (ELSS) to build his retirement fund. Rajesh increases his SIP amount by 10% every year as his annual income grows.
On retirement (age 60 years), Rajesh stops the SIP and transfers the money from the ELSS fund to a debt mutual fund. He starts a monthly SWP of Rs. 30,000 from his retirement fund. He increases the monthly SWP amount by 5% every year to keep pace with inflation.
The XIRR calculation method is designed to consider SIPs and SWPs with multiple investments and redemptions of varying amounts and dates to calculate returns. Hence, the XIRR method is the more appropriate method to calculate mutual fund returns where SIP and SWP are involved.
Step-by-step process to calculate XIRR with an example
Now that we understand what is XIRR in mutual fund, let us look at the step-by-step process of calculating XIRR. For example, Sheetal has started a monthly SIP of Rs. 3,000 on 1st October 2021 for 20 years. But, due to some emergency, she had to stop the SIP after one year and redeem the proceeds. She got Rs. 40,000.
Let us see how to record the above information in an Excel sheet and calculate the XIRR.
The above table shows the SIP dates from 1st October 2020 to 1st September 2021 (12 instalments) and the SIP amount of Rs. 3,000. The amount was redeemed on 1st October 2021, and the amount received was Rs. 40,000.
To calculate the XIRR, you have to select the XIRR formula in Excel and input the range of values (select the SIP amounts and redemption amount) and the range of dates (select the SIP dates and redemption date).
In the above example, on selecting the XIRR formula and entering the range of values and dates, you will get 0.2113. So, the XIRR in this example is 21.13%.
Key points to focus on while calculating XIRR
While calculating the XIRR, you need to focus on the following points:
- In a SIP, your monthly investment amount is a cash outflow from your pocket. Hence, while recording the SIP amount in an Excel sheet, you must show them with a minus (-) sign. Check the image in the above section; all the SIP amounts have been recorded as Rs. -3,000 (with a minus sign).
- In a SIP, the lump sum redemption amount or the monthly systematic withdrawal plan (SWP) redemption amount you receive is a cash inflow in your pocket. Hence, while recording the lump sum or SWP redemption amounts in an Excel sheet, you must show them as positive values. Check the image in the above section; the lump sum redemption amount has been recorded as Rs. 40,000 (positive value).
- The XIRR formula has three inputs: values, dates, and guess. You need not enter anything in the third parameter (guess).
Which is better between CAGR & XIRR?
Technically, both the formulae (CAGR and XIRR) are correct. However, both the formulae have their use cases.
The CAGR formula is appropriate for calculating returns on a lump sum amount. So, assume that an individual invests a lump sum of Rs. 50,000 in a particular mutual fund scheme. After two years, the money has grown to Rs. 70,000, and the individual redeems the entire amount. In this scenario, the compound annual growth rate (CAGR) formula is more appropriate to calculate the returns from the investment.
The XIRR formula is appropriate for calculating returns when the investment mode is SIP, and the mode of redemption is SWP. So, assume that an individual starts investing in a particular mutual fund scheme through a monthly SIP of Rs. 10,000 for five years. After five years, the individual stops the SIP and starts a monthly SWP of Rs. 15,000. In this scenario, the XIRR formula is more appropriate for calculating the investment returns.
So, to summarise, the CAGR formula is suited for calculating returns from lump sum investments, and the XIRR formula is suited for calculating returns from SIP investments.
For most investors, the XIRR formula is more appropriate
Building a retirement fund is one of the most important goals for almost all investors. Most investors use the systematic investment plan (SIP) mode of investing in an equity mutual fund for building their retirement fund during their working years. They either contribute a fixed SIP amount every month or increase the SIP amount by a certain percentage (for example, 5% or 10%) every year as their annual income grows.
Once they retire, investors transfer the retirement fund money to a debt mutual fund and start a systematic withdrawal plan (SWP) for meeting their regular monthly expenses during retirement years. They may increase the SWP amount annually by 5% or 10% to factor in inflation.
To conclude, most investors use the SIP investment mode to accumulate money for their financial goals. Once they reach the goal, they may withdraw money using the SWP mode. As most investors frequently use SIP and SWP modes, the XIRR method of calculating returns is more appropriate.
To read more similar topics check out below links:
To get investment recommendations for investing in mutual fund SIPs that have the potential to generate good XIRR, download the Glide Invest App from Google Play Store or Apple App Store and get started.