Our WhatsApp number cannot be used for calls. This is a chat only number.
However, have you ever wondered what the right method to calculate the return on mutual fund investments is?
Investors can use two methods, namely (CAGR- Compound Annual Growth Rate) and XIRR (Extended Internal Rate of Return). The CAGR method doesnot allows calculating returns for multiple cash flows. However, by following the XIRR method, one can easily calculate mutual fund returns for multiple cash flows.
Read on to know what is XIRR in mutual funds, its importance, and the calculation process!
XIRR, or Extended Internal Rate of Return, is a single rate of return that is used when investors make multiple investments at different points of time in an investment vehicle such as a mutual fund.
This single rate of return is applied to all transactions (including investment and redemption) to calculate the current rate of return and the current value of the entire investment.
XIRR is the real investment return.
XIRR is important in mutual fund investment because investors can use this formula in an excel sheet and put data of uneven cash flow intervals and get correct results.
XIRR is a modified version of IRR with added flexibility. The formula of XIRR can assign particular dates to each cash flow and calculate returns accurately.
While investing in a mutual fund, investors can put a lump sum amount that involves two cash flows. It means they will have a single cash outflow (in the form of investment) and a single cash inflow (in case of redemption). In such cases, calculating the return through the CAGR (which reflects the rate at which a mutual fund grows per year throughout an investment period) method is the correct way.
However, in cases where investors invest in mutual fund schemes through Systematic Investment Plans (SIP) or wishes to redeem units of mutual funds investment through a Systematic Withdrawal Plan (SWP).
Due to the multiple cash flow involved, one cannot use CAGR (as stated earlier) to calculate return from a mutual fund. Hence, using XIRR seems correct. Before delving deep into using XIRR, one must know about IRR.
IRR (Internal Rate of Return) is a metric to calculate the return of a series of cash flows. Investors can use IRR to calculate returns of SIP, SWP, multiple redemptions, lump-sum investments with additional purchases, etc. However, IRR has certain limitations.
While calculating the IRR formula in Excel, it automatically assumes the time interval between with cash-flows is the same, which rarely happens throughout the tenure.
Let’s take an example!
Suppose an investor puts ₹4,000, ₹9,000, ₹5,000, ₹4,000, and ₹6,500 in SIPs over 5 years and earns ₹53,000 at the end of the tenure. Here, the return on investment is 22% (as per the IRR calculation method).
In the case of a mutual fund, investment occurs at an irregular interval (even in the case of SIP) due to a different number of days in a month, holidays etc. For uneven cash flow or irregular cash flow intervals, the calculation with IRR in the excel sheet will go on. It is here one can use XIRR and calculate the right value or return on mutual fund investment for multiple cash flows.
Let’s see how this formula works in Excel,
Take an example of a six-month investment of SIP.
|
SIP amount |
₹5000 |
|
Start date |
01.01.2020 |
|
Cash flow intervals |
|
|
1st Month |
01.01.2020 |
|
2nd Month |
03.02.2020 |
|
3rd Month |
01.03.2020 |
|
4th Month |
11.04.2020 |
|
5th Month |
01.05.2020 |
|
6th Month |
25.06.2020 |
|
7th Month |
01.07.2020 |
|
End date |
01.06.2020 |
|
Redemption date |
01.07.2020 |
|
Maturity amount |
₹31000 |
Here is a step-by-step guide on calculating XIRR in excel,
Step 1 – Prepare a single column (Column A) for all purchases. Mark outflows as market negative (enter SIP figure of 5000) and inflows as a market positive.
Step 2 – Select the next column for putting the corresponding date of the transaction.
Step 3 – Enter the prevailing value of all holdings and the date in the last row. (Against redemption date and column A, enter redemption amount column B – i.e. 31000)
Step 4 – Next, use the XIRR function ‘= XIRR (values, date, Guess)’. XIRR (B1: B7, A1: A7)*100, date, Guess)
Step 5 - Choose values to a series of cash flow that corresponds to a payment schedule in dates, the column of dates refers to the first date of investment; guess is optional. If investors do not enter any value, excel automatically uses a value of 0.01.
Step 6 – The excel sheet will show 11.92% as a result.
Conclude your search for what is XIRR in a mutual fund and get correct results of mutual fund investment returns in case of uneven cash flow intervals.
Please try one more time!
Disclaimer: This information is added only for informative purposes and collected from different sources across the Internet. Digit Insurance is not promoting or recommending anything here. Please verify the information before making any decisions.
Continue with {{bikeCtrl.lastVisitedData.lastVisitedUrl.indexOf('DigitPaymentGateway/payments')
!== -1 ? 'payment completion': 'previous choice'}}
