You may want to capitalize on your investment as you get closer to an important financial goal. However, before redeeming your mutual fund or equity portfolio, you can calculate your returns based on the initial amount you have invested and the maturity value. Calculating point-to-point returns is not easy, especially if there are multiple cash inflows and outflows in a SIP (Systematic Investment Plan).
This is where the Extended Internal Rate of Return (XIRR) formula can help. Using the XIRR formula, you can calculate your investment returns efficiently.
What is the XIRR formula?
The XIRR formula factors in various cash flows. As per the XIRR formula, the annual average return of each SIP installment made by you is calculated and adjusted to provide the average annual return rate for all investments.
What is XIRR in Mutual Fund?
XIRR stands for Extended Internal Rate of Return. You can address this as a way to calculate your mutual fund investment returns at irregular intervals. Using the XIRR formula for each SIP installment or liquidation, if any, will give you an understanding of the present value of your overall investment.
Mathematically, XIRR is seen as a single rate of return. In simple words, whenever you buy or liquidate units in a mutual fund, you can use this concept to calculate the consolidated return. XIRR is the actual return on your investment in mutual funds.
How can you calculate XIRR in Mutual Funds?
Suppose you have started monthly SIP (Systematic Investment Plan) in Equity Mutual Fund of Rs. 5,000 and continued investing for ten years. After several ups and downs in the market, your total investment has increased to Rs 12.33 lakh by the end of ten years.
In this case, your initial installment of Rs. 5,000 was invested in mutual funds for the longest period i.e. ten years. Consequently, your annual return on your initial installment will be different from the returns generated on other fund instalments.
Since each installment in a SIP is invested for a different tenure, their respective CAGR (Compound Annual Growth Rate) is different. However, factoring in CAGR can be highly challenging to analyze the plan. To simplify your calculations, you can add up all the CAGRs to be adjusted for a common CAGR. This adjusted CAGR is called XIRR.
Using the XIRR Formula in Excel, you can calculate your XIRR in Mutual Funds. Alternatively, you also use
online sip calculator
To calculate XIRR in Mutual Funds. To calculate your XIRR in mutual funds, you need to enter three values ​​in the online SIP calculator: Amount invested, Amount on maturity and Tenure of investment. Once you input these three values, your XIRR will be displayed.
How can you calculate XIRR in Excel?
You can easily calculate your XIRR in Mutual Funds through inbuilt functions in Excel. The XIRR formula in Excel is “=XIRR(Values, Dates, Estimates).” To do the calculations, you need to open the Excel application on your mobile or desktop and follow the steps below:
• Type all your mutual fund transactions in one column. All outflows, including investments and purchases, must be marked as negative. Inflows such as your liquidation should be marked as positive.
• Enter all your respective transaction dates in the next column.
• In the last line, enter the current value and date of your holdings.
• Use the XIRR function “=XIRR(value, date, estimate).”
• Estimate is an optional parameter in the XIRR formula in Excel. If you don’t input a value, use the 0.1 value.
Not sure 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 (one 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 entered on the left hand side, i.e. column A
• All SIP figures are inputted on the right side with a negative sign as it is an outflow of cash, i.e. column B
• At the end the redemption amount is mentioned with a positive sign along with its date
• In the box below Maturity/Redemption Amount, input: “=XIRR (B2:B15, A2:A15) *100” and press the Enter button.
Once done, the XIRR value will appear, which is 19.8%.
closing thoughts
XIRR is one of the most comprehensive methods of determining your investment return for multiple transactions. Fund allocation in SIP mutual funds results in significant cash flow, and timing of investment plays a huge role in calculating your returns. Using the XIRR formula is a prudent way to calculate SIP returns easily and by doing so, know if it is time to change your investment strategy.
Disclaimer:
an investor education initiative
meeting
www.icicipruamc.com/note
To know more about the process to fulfill the Know Your Customer (KYC) requirement for investing in mutual funds. Investors should deal only with registered mutual funds, details of which can be verified on SEBI website
https://www.sebi.gov.in/intermediaries.html
For any queries, grievances and grievance redressal, investors may contact the AMC and/or Investor Relations Officers. In addition, investors can also file complaints on
https://scores.gov.in
If they are dissatisfied with the proposals made by the AMC. The SCORES portal allows you to register your complaint with SEBI online and view its status later.
Mutual fund investments are subject to market risks, read all the documents related to the scheme carefully.