How to calculate mutual fund returns in Excel: Find out SIP returns of your MF schemes

By: |
Published: April 27, 2020 2:54:02 PM

XIRR basically calculates the internal rate of return of an investment when there are irregular cash flows made on different dates or period of time.

 XIRR formula in excel with example, XIRR sip calculator, using XIRR formula in excel, XIRR formula in mutual fund, XIRR calculation in mutual fund, mutual fund sip return calculatorUsing XIRR calculation in a mutual fund will help you calculate the actual return on your total investments.

To calculate the returns on your mutual fund investments made on different dates, using XIRR formula in excel makes the task easier. You may be investing through SIP or making lump sum investments on different dates. However, using the XIRR calculation in mutual fund will help you calculate the actual return on your total investments. XIRR basically calculates the internal rate of return of an investment when there are irregular cash flows made on different dates or period of time.

For example, one may have an SIP in which certain amount is invested on the 7th of every month. The investor may add a lump sum on any other date as well. He or she may redeem certain units on a specific date. The actual return for the investor can be captured by using XIRR formula.

XIRR also helps in calculating the real return for the investor. For example, the NIFTY 50 is down by nearly xx per cent over the last 1 year and by almost xx per cent over the last 5 years. But, this is a point to point return as on specific date, while your own specific MF investment could be on different dates. Therefore, the returns for you will be different from what the index returns are. Using XIRR SIP calculator is, therefore, a better way to calculate returns from mutual funds SIP or lump sum investment.

Before you begin using the mutual fund sip return calculator using the XIRR function, keep these 4 things handy – Investment dates, Investment amount, Redemption date and Redemption amount.

If you haven’t redeemed, you may use the current date and redemption amount equal to current NAV and units you hold.

Below is an example of XIRR for a SIP of Rs 10,000 made for six months and redeemed on 1st of the 7th month. For practical purpose, we have used 6 months, you may run the exercise longer.

The XIRR formula in excel is – XIRR(cash flow amounts, cash flow dates)

In the excel sheet, every cash outflow is denoted with a minus sign while inflows are positive numbers.

=XIRR(B1:B7,A1:A7)*100

Here, the return is 11.92 per cent for the investor after investing Rs 10,000 for 6 months and receiving Rs 62,000 as maturity amount after 6 months. The calculations and the numbers considered in the example are only for understanding purpose.

Get live Stock Prices from BSE and NSE and latest NAV, portfolio of Mutual Funds, calculate your tax by Income Tax Calculator, know market’s Top Gainers, Top Losers & Best Equity Funds. Like us on Facebook and follow us on Twitter.

Financial Express is now on Telegram. Click here to join our channel and stay updated with the latest Biz news and updates.

Next Stories
1Mutual Funds: Pharma fund, International funds and SIP queries answered
2BHARAT Bond ETF: Edelweiss Asset Management to launch 2nd tranche soon – Check details
3Debt funds are not risk-free, but they can still be the right investment for you; Here’s why