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

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.

Also Read

How much will you get after 20 years by investing Rs 5,000 every month – Find out

Debt vs Equity Funds: Which fund is good for investment?

MUTUAL FUNDS: Know the risks associated with quant funds

Air pollution: Delhi govt extends work from home for govt employees, ban on entry of trucks till November 26

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.