Mutual Fund calculation: Here’s how to find the actual return on your SIP investments

To calculate the returns on your mutual fund investments made on different dates, using XIRR formula in excel makes the task easier.

Mutual Fund Return Calculator SIP: In mutual funds or any investment where returns are linked to the market, the returns over a year are shown as compounded annualised growth rate or CAGR. For each investment made on different dates, the CAGR will be different as its period of holding will be different. In the case of mutual fund SIPs, the investment is done on a different date and hence there are two ways to calculate the effective return – either calculate CAGR of each investment or else use the XIRR formula.

To calculate the returns on your mutual fund investments made on different dates, using XIRR formula in excel makes the task easier. By using the XIRR calculation in a mutual fund, you can 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.

Even if you have ongoing monthly SIP investment of Rs 15,000 and invest a lump sum intermittently, you can calculate the returns using the same XIRR formula. Also, if you redeem certain units in between on any date, the XIRR will help you find the actual return as on date.

Before you begin using the XIRR function using the Excel sheet, keep these 4 things handy – Investment dates, Investment amount, Redemption date and Redemption amount.

Steps to use XIRR formula

The XIRR formula in Excel is: XIRR (Value of investments, dates)

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

• After opening the Excel sheet, in column A, enter the transaction dates.
• In column B, enter SIP amount as a negative figure. Make sure the dates are on the left side, while Amount is on the right side.
• Against the redemption date (Column A), enter the redemption amount (Column B). If you haven’t redeemed, you may use the current date and redemption amount equal to amount arrived by multiplying the current NAV and units you hold.
• In the box below type in: =XIRR (B1: B6, A1: A6)*100 and hit enter. Here, boxes A1, to A6 and B1 to B6 are illustrative. You will have to use the box number depending on your own figures.

So, the next time, when you read the returns of a mutual fund scheme, it is not what your scheme as earned. XIRR will help you calculate the actual return on your own investments in a better way.