Posted on 9/3/2022 10:58:00 AM

Know Everything About XIRR in Mutual Funds
You may want to redeem your investments when nearing a crucial financial goal. However, before redeeming your mutual fund or equity portfolio, you may compute your returns depending upon the initial amount you invested and the maturity value. Computing point-to-point returns isn't easy, especially if there are multiple cash inflows and outflows, like in SIPs (systematic investment plans).
This is where the extended internal rate of return (XIRR) formula can help. Using the XIRR formula, you can efficiently compute your investment returns.
What is the XIRR formula?
XIRR formula factors in different cash flows. According to the XIRR formula, the annual average returns of every SIP instalment you make are computed and adjusted to provide the average annual return rate for all investments.
What is XIRR in mutual funds?
XIRR stands for Extended Internal Rate of Return. You can address it as a method to compute your mutual fund investment returns at irregular intervals. Using the XIRR formula for every SIP instalment or liquidation, if any, would endow you with an understanding of your overall investment's current value.
Mathematically, XIRR is viewed as a single return rate. In simpler terms, you can use this concept to compute consolidated returns whenever you buy or liquidate units in mutual funds. XIRR in mutual funds is the actual return on your investments.
How can you calculate XIRR in mutual funds?
Suppose you began a monthly SIP (systematic investment plan) in an equity mutual fund of Rs. 5,000 and continued investing for ten years. After various ups and downs in the market, your overall investment grew to Rs.12.33 lakh by the end of ten years.
In this case, your initial instalment of Rs. 5,000 was invested in the mutual fund for the longest time, i.e., ten years. As an outcome, your annual return on your initial instalment will be distinct from the returns generated on the other fund instalments.
As every instalment in SIP stays invested for different tenures, their corresponding CAGR (compound annual growth rate) differs. However, factoring in the CAGR to analyze the scheme's may be highly challenging. To simplify your calculations, you can combine all the CAGRs to adjust them into a common CAGR. This adjusted CAGR is called XIRR.
By using the XIRR formula in excel, you can calculate your XIRR in mutual funds. Alternatively, you also use the online SIP calculator to calculate XIRR in mutual funds. To calculate your XIRR in mutual funds, you must input three values in the online SIP calculator: the amount invested, amount at maturity and investment duration. Once you input these three values, your XIRR will be displayed.
How can you compute XIRR in excel?
You can calculate your XIRR in mutual funds easily through an inbuilt function in excel. The XIRR formula in excel is "=XIRR (values, dates, guess)." To compute, you must open the excel application on your mobile or desktop and follow the below-mentioned steps:
• Type all your mutual fund transactions in one column. All outflows, including investments and purchases, must be marked as negative. Inflows like your liquidations must be marked as positive.
• Input all your corresponding transaction dates in the next column.
• In the last row, input your holdings' current value and date.
• Use the XIRR function "=XIRR (values, dates, guess)."
• Guess is an optional parameter in the XIRR formula in excel. If you do not input any value, use the 0.1 value.
Unsure how to calculate XIRR in excel? Use this example
• SIP = Rs.1,000
• SIP dates = between 1/1/2021 and 1/1/2022
• Liquidation date = 1/2/2022
• Maturity amount = Rs.14,500
SIP Date (A column) Amount (B column)
01-01-2021 -1000
10-02-2021 -1000
13-03-2021 -1000
13-03-2021 -1000
13-03-2021 -1000
13-04-2021 -1000
14-05-2021 -1000
14-06-2021 -1000
15-07-2021 -1000
15-08-2021 -1000
15-09-2021 -1000
16-10-2021 -1000
16-11-2021 -1000
17-12-2021 -1000
17-01-2022 -1000
17-02-2022 -14,500
XIRR -19.8 %
• All transaction dates are inputted on the left, i.e., column A
• All SIP figures are inputted on the right with a negative sign as it is an outflow of cash, i.e., column B
• Redemption amount towards the end is mentioned with a positive sign along with its date
• In the box below the maturity/redemption amount, input: "=XIRR (B2:B15, A2:A15) *100" and press the enter button.
Once done, the XIRR value will show up, which is 19.8%.
Closing thoughts
XIRR is one of the most comprehensive ways to determine your investment returns for multiple transactions. Fund allocations to SIP mutual funds result in significant cashflows, and investment time plays a considerable role in your return calculations. Using the XIRR formula is a prudent way to compute SIP returns easily and, by doing so, know if it’s time to change your investment strategy.
Mutual fund investments are subject to market risks, read all scheme related documents carefully.

  • Did You Know?

    You can transact faster and enjoy more features on ICICI Pru AMC once you log in!

    Login Now
  • Need to talk?

    Call on our Toll free number 1800 222 999(BSNL/MTNL)

    or Get a call back

  • Follow Us

    The latest from ICICI Pru AMC, including our unique, industry-leading programs, conferences, reports, and special events.

Recommended fund