What is XIRR in Mutual Funds?
5paisa Research Team
Last Updated: 19 Oct, 2022 02:12 PM IST
Want to start your Investment Journey?
Content
- Introduction
- What is XIRR in Mutual Funds?
- Why Does it Make Sense for Mutual Fund Investments?
- What Is an IRR?
- XIRR Calculation
- Example of How to Use the XIRR Function in Excel
- Open an Excel Sheet and Follow these Steps:
- Conclusion
Introduction
Earning returns is the primary goal of any investment. A return can take the form of income, capital appreciation, or both. Compound annual growth rate (CAGR) and Extended Internal Rate of Return (XIRR) are mutual funds' most popular return measures.
An investment's CAGR represents its annual growth rate assuming compounding every year. The CAGR, however, does not apply to multiple cash flows; it applies to XIRR in mutual funds. But what is XIRR in MF?
What is XIRR in Mutual Funds?
As mentioned above, XIRR stands for Extended Internal Rate of Return. An Extended Internal Rate of Return (XIRR) calculates the total current value of an investment by applying a single rate of return to each instalment (or redemption).
An investor's return rate is their XIRR. In other words, it is the actual return on your investment.
It can be a bit complicated to calculate returns considering the multiple investments (and therefore multiple purchase prices) and different periods for each instalment in a SIP. Hence, a mutual fund SIP's return is typically calculated using XIRR.
Why Does it Make Sense for Mutual Fund Investments?
Let’s understand XIRR's meaning in mutual funds in detail.
Investors can invest a lump sum amount in a mutual fund with two cash flows. This means they will only have one cash outflow (from investments) and one cash inflow (from redemptions). The CAGR method (which reflects a mutual fund's growth rate per year over an investment period) is the most appropriate in such cases.
However, investors who invest in mutual fund schemes through Systematic Investment Plans (SIP) or redeem units of mutual fund investment through Systematic Withdrawal Plans (SWP) cannot take advantage of this. CAGR cannot be used to calculate returns from a mutual fund because of the multiple cash flows involved. Therefore, using XIRR makes sense.
To understand XIRR, one must first understand IRR.
What Is an IRR?
An IRR (Internal Rate of Return) measures the return on a series of cash flows. The Investor Return Ratio (IRR) estimates returns on investments, such as SIPs, SWPs, lump-sum investments with additional purchases, multiple redemptions, etc. IRR, however, has some limitations.
IRR formulas in Excel automatically assume that cash-flow intervals are the same throughout the tenure of the investment, which rarely occurs.
Take a look at an example!
An investor makes 53,000 after investing 5,000, 4,000, 4,000, 6,500, and 9,000 in SIPs during the tenure. Based on the IRR calculation method, the return on investment is 22%.
However, investments in mutual funds are not so regular (even if they are enrolled in a systematic investment plan) since there are holidays, fewer days in some months, etc. At various points in time, there will be cash inflows and outflows. Aside from the amount invested, the duration of the investment also has an impact on the outcome.
Hence, XIRR can determine the right return on multi-cash flow mutual funds investments.
XIRR Calculation
Now that you’re familiar with XIRR meaning, let’s look at its calculations.
Applying the XIRR formula to Mutual Funds in Excel and following the steps below will calculate your XIRR.
1. Make a column for all your investment transactions, marking outflows as negatives like investments and acquisitions and inflows as positives like redemptions.
2. In the next column, enter the investment date.
3. The next column should include your investments' current value and date.
4. Use the XIRR function in Excel, which looks like =XIRR (values, date, Guess).
5. Finally, select cash flow values corresponding to the date column in the payment schedule. Include the investment dates in the date column along with the cash flow dates. The Guess parameter is optional, and if no value is entered, Excel defaults to 0.1.
Example of How to Use the XIRR Function in Excel
Let's take a look at how this formula works in Excel.
Suppose we have a set of cash flows like those in the table below:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Interest inflows are irregular in the table above. Hence, to calculate the return on these cash flows, you can use the XIRR function.
Open an Excel Sheet and Follow these Steps:
● Enter the original investment amount in an Excel sheet. Use A 'minus' sign to represent the amount invested.
● Enter each period's returns in the following cells. Every time you invest money, remember to include the minus sign.
● Now, find XIRR using the function: “=XIRR(values, dates, guess)”
Your XIRR here is 4.89%.
The term values refer to a series of cash flows corresponding to a schedule of payments. During the investment period, the first payment represents the investment made at the beginning. Afterwards, all payments are discounted according to a 365-day calendar. The series of values must have at least one positive and one negative value.
The date represents the day that the first investment was made, as well as when the first returns were received. The above table shows each date corresponds to the investment made or income received. Dates should be entered in the DD-MM-YY format (date-month-year) to avoid errors. Any invalid date number, or an inconsistent date format, will result in the “#VALUE!” error.
Conclusion
Both XIRR and CAGR are used to calculate mutual fund returns. When it comes to single payment contributions, CAGR is the most commonly employed formula, while when it comes to SIP instalments, XIRR is used most commonly. For investors, it's usually better to know how return calculations work so that they aren't reliant on others.
More About Mutual Funds
- NPS vs ELSS
- XIRR vs CAGR: Understanding Investment Return Metrics
- SWP and Dividend Plan
- What is Solution Oriented Mutual Funds?
- Growth Vs Dividend Reinvestment Option
- Annual vs Trailing vs Rolling Returns
- How to Get Capital Gain Statement for Mutual Funds
- Mutual Funds Vs Real Estate
- Mutual Funds vs. Hedge Funds
- Target Maturity Funds
- How to Check Mutual Fund Status with Folio Number
- Oldest Mutual Funds In India
- History Of Mutual Funds In India
- How To Redeem ELSS Before 3 Years?
- Types of Index Funds
- Who Regulates Mutual Funds In India?
- Mutual Fund Vs. Share Market
- Absolute Return in Mutual Fund
- ELSS Lock in Period
- Treasury Bills Repurchase (TREPS)
- Target Date Fund
- Stock SIP vs Mutual Fund SIP
- ULIP vs ELSS
- Long Term Capital Gain Tax on Mutual Funds
- Smart Beta Funds
- Inverted Yield Curve
- Sinking Fund
- Risk-Return Trade-Off
- Registrar and Transfer Agents (RTA)
- Mutual Funds Overlap
- Mutual Fund Redemption
- Mark to Market (MTM)
- Information Ratio
- Difference Between ETF and Index Fund
- Difference Between Mutual Fund and Index Fund
- Top 10 High Return Mutual Funds
- Passive Mutual Funds
- Passive Funds vs Active Funds
- Consolidated Account Statement
- Mutual Funds Minimum Investment
- What is Open Ended Mutual Fund?
- What is Closed End Mutual Fund?
- Real-Estate Mutual Funds
- How to Stop SIP?
- How to Invest in SIP
- What is a Blue Chip Fund?
- What is XIRR in Mutual Funds?
- What is a Hedge Fund?
- Tax Treatment of Long Term Capital Gains
- What is SIP?
- NAV in Mutual Funds
- Advantages of Mutual Funds
- Stocks vs Mutual Funds
- What is STP in Mutual Fund
- How Mutual Fund Works?
- What is Mutual Fund NAV?
- What are Mutual Funds?
- Mutual Fund Cut Off Time
- Mutual Fund the Best Investment Option for Conservative Investors
- Advantages and Disadvantages of Mutual Funds
- How to Choose Mutual Funds in India?
- How to Invest In Mutual Funds?
- How to Calculate NAV of Mutual Fund?
- What Is CAGR In Mutual Funds?
- What is AUM in Mutual Fund
- Total Expense Ratio
- What is XIRR in Mutual Funds?
- What is SWP in Mutual Fund
- How to Calculate Mutual Fund Return?
- Gold Mutual Funds
- Tax On Mutual Fund Investment
- The Top Benefits and Drawbacks of The Rupee Cost Averaging Approach
- How to Start a SIP Investment?
- What Is SIP & How does SIP Work?
- Best SIP Plans for Long Term: How and Where to Invest
- Best SIP Mutual Fund Plans
- ELSS Vs SIP
- Top Fund Managers in India
- What is NFO?
- Difference Between ETF and Mutual Fund
- ULIPs VS Mutual Funds
- Direct Vs. Regular Mutual Funds: What’s The Difference?
- ELSS vs Equity Mutual Fund
- NPS vs Mutual Fund
- Can NRIs Invest in Mutual Funds?
- Mutual Funds Categorisation In India
- Everything You Need to Know About Small-Cap Funds
- What is Public Provident Fund ?
- What is Large Cap Mutual Fund ?
- What is Index Fund ?
- What is IDCW in Mutual Fund ?
- What is Hybrid Fund?
- What is Gilt Fund ?
- What is ELSS Fund ?
- What is Debt Funds?
- What is an Asset Management Company - A Thorough Explanation
- What are Mid Cap Funds
- Liquid Funds - What are Liquid Funds?
- A Beginner's Guide to Investing in Fund of Funds Read More
Disclaimer: Investment in securities market are subject to market risks, read all the related documents carefully before investing. For detailed disclaimer please Click here.