Calculate the return of investment in mutual funds using proper methods based on the type of investment. One can manage to calculate the return on lump-sum investment. But the return on regular investments like SIP is complex to calculate.

Let us understand various methods to return calculation:

Point-to-Point or Absolute Return

To calculate point-to-point or absolute return, you need the current Net Asset Value (NAV) and the Initial NAV. It calculates the simple return on your initial investment. The calculation of the -point-to-point return or absolute return does not require the holding period. 

The formula for the absolute return:

(Current NAV-initial NAV)/initial NAV*100

One can use this method to calculate the returns when the holding period is less than a year. 

Assuming the initial NAV value was Rs.10, and now after three years, it is Rs.20, the absolute return is 100%.

Simple Annualised Return

If the holding period is less than 12 months, some investors may require an annualised return. It is also referred to as an effective annual yield. 

Annualised return formula:

((1 + Absolute Rate of Return) ^ (365/number of days)) – 1

For example: The NAV is increased to Rs. 50 in 7 months from initial investment value of Rs. 40. So the absolute return is 25% i.e. 0.25, then annualised return can be calculated as-

((1 + 0.25) ^ (365/210)) – 1

i.e. 47.38%

However, this method holds good if the period of holding is exactly one year.

Compounded Annual Growth Rate (CAGR)

The CAGR gives the average annual growth rate of the returns. The returns can be calculated by the CAGR method if the holding period is more than a year. The returns may not be the same every year. 

CAGR formula:

(((ending value/beginning value)^(1/number of years))-1)*100

For Illustration:

If Rs.1 lakh is invested in a mutual fund with a NAV of Rs.60 and after three years, the NAV is Rs.30. Then it will be calculated as-

(((60/30)^(1/3))-1)*100, i.e. 25.99%

However, if the holding period is in months, the formula is:

(((ending value/beginning value)^(12/number of months))-1)*100 

Similarly, CAGR calculation for period in days can be- 

(((ending value/beginning value)^(365/number of days))-1)*100 

XIRR Method

XIRR or extended internal rate of return can be used for investments made in SIPs. Cash inflows and outflows are at irregular intervals in SIP. One can follow the XIRR method to calculate annualised yield or internal rate of return in irregular cash flows. It can be used where multiple transactions are happening at different intervals.

To know about the returns of the fund, one can use the XIRR function in excel. It provides an inhibit function to calculate XIRR. 

The IRR method can be used where the cash flows are equally spaced in time. But in mutual funds, the cash flows are not so evenly distributed. Also, the CAGR method is useful in calculating returns for investment in a lot of mutual funds. It is not easy to calculate CAGR for 17 months, 16 months, 15 months and so on for all the different investments made in a SIP. Hence, XIRR is the right way to calculate the return on mutual funds.

Steps to calculate XIRR in excel:

1. Enter all your transactions in one column. All outflows like purchases will be marked negative, while all inflows like redemptions shall be marked positive.
2. In the next column, add the corresponding date of the transaction.
3. In the last row, mention the current value of your holding and the current date.
4. Now below that, use the XIRR function, which is like this =XIRR (values, date, guess)*100
5. To input ‘values’ in the above formula, select a series of cash flows that corresponds to a schedule of payments. 
6. To input ‘date’ in the above formula, select the dates of the first investment and the dates of cash flows received.
7. The ‘guess’ parameter is optional (if you do not put any value, excel use a value of 0.1).

For illustrative purposes:

SIP amount = Rs.5,000
SIP start date-01/01/2020
SIP end date-01/06/2020
Redemption date = 01/07/2020
Maturity amount = Rs.32,000