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.
