Mutual fund SIPs: Here's a DIY approach to calculate the returns on investment in mutual funds either as a lump sum or through a SIP

How to calculate returns from mutual fund SIPs

As per a Geojit Financial Services report titled "World Equity Indices Screener: Around the World in 5 Minutes" for the calendar year 2023, notable indices in 11 out of 45 countries concluded the year at levels near or exceeding five-year or all-time highs. The Sensex, registering a one-year return of 18.7%, marked its strongest performance since 2021 and the second-best since 2017, securing the 15th position among the 45 indices. Additionally, Indian indices secured the sixth position with a three-year Compound Annual Growth Rate (CAGR) of 14.8% and the fifth position with a five-year CAGR of 13.2%, as per Geojit's assessment.

How well has your mutual fund (MF) scheme performed? Let's explore various methods an investor can use to assess returns on their investment, whether it involves a single lump sum or is executed through a systematic investment plan (SIP).

Also read: 5 investment, financial planning questions answered for millennials

Point-to-point or absolute return

This method aids in computing the straightforward returns on your initial investment. All that's required is the initial and current (or concluding) net asset value (NAV) of the scheme. In the calculation of point-to-point or absolute return, the duration of holding does not influence the outcome. For instance, if your initial NAV was Rs 20 and has now reached Rs 40 after 3 years, the point-to-point return stands at 100%.

Simply put, absolute return = (current NAV - initial NAV)/ initial NAV x 100
You may put this in excel sheet to calculate it. In any box, start by typing =

Use this formula to calculate returns when the holding period is less than 12 months.

Simple annualised return

In cases where the holding period is less than 12 months, some investors may prefer to annualize the return. This process, often referred to as effective annual yield, involves extrapolating the returns to an annualized rate. However, it's important to note that while this approach provides an annual figure, it may not necessarily present an accurate and comprehensive representation of the actual returns.

If you need to annualise the returns, here's the formula:
((1 + Absolute Rate of Return) ^ (365/number of days)) - 1
You may put this in excel sheet to calculate it.

Illustration: The NAV of Rs 20 may shoot to Rs 25 in, say, 7 months, i.e., 210 days. The absolute return in this case is 25 per cent over 7 months, i.e., 0.25
So it becomes
=((1 + 0.25) ^ (365/210)) - 1
=47.38 per cent

As per the regulations set by the Securities and Exchange Board of India (SEBI), absolute return is disclosed when the investment duration is less than a year. On the other hand, if the period exactly spans a year, the standard practice is to present the simple annualized return. These guidelines ensure consistency and clarity in reporting returns for different investment durations.


Compounded annual growth rate (CAGR)

When the investment duration extends beyond a year, the Compound Annual Growth Rate (CAGR) becomes a more effective measure for illustrating returns. CAGR provides a single figure indicating how the investment would have grown if it had consistently generated the same return annually. Recognizing that actual returns may fluctuate from year to year, CAGR serves as a mean annual growth rate, smoothing out the impact of volatility and offering a more stable representation of the investment's performance over the specified time frame.

Let's now see how CAGR can be quickly computed using an excel file. Here, we consider CAGR of investment made in MFs.

Assuming you had invested Rs 1 lakh in an MF three years back at an NAV of Rs 20. Now, the NAV is Rs 40.

Here's the formula
=(((ending-value/beginning-value)^(1/number-of-years))-1*100

So it will be
= (((40/20)^(1/3))-1)*100
And on hitting enter, the result is:
=25.99%

If the holding period is in months, use this
Formula: =(((ending-value/beginning-value)^(12/number-of-months))-1*100
= (((40/20)^(12/36))-1)*100
=25.99%

Similarly, if you know the NAV and the number of days, then use
Formula: =(((ending-value/beginning-value)^(365/number-of-days))-1*100
= (((40/20)^(365/1095))-1)*100
=25.99%

For calculating SIP returns, use XIRR

The timing and amounts of cash inflows and outflows can vary, occurring at irregular intervals rather than being evenly matched. This is often the case in financial products like money-back plans or mutual fund Systematic Investment Plans (SIPs). In such scenarios, the XIRR function in Excel proves useful. XIRR is employed for calculating the internal rate of return (IRR) or annualized yield when dealing with a schedule of cash flows occurring at irregular intervals, providing a more accurate representation of returns in situations with non-uniform cash flows.

In a SIP, investors consistently contribute funds at regular intervals over an extended period. These investments occur on predetermined dates with fixed amounts. The number of units acquired is determined by the Net Asset Value (NAV) of the scheme on the investment date. As a result, investors accumulate units gradually over the duration of the SIP. Upon exiting the scheme, or redeeming the total units, the maturity amount is calculated by multiplying the NAV on the redemption day by the total units held on that day, providing the final return.

Now, to know how much returns ones scheme has generated, you may use the XIRR (a function in excel). It's simple and one doesn't even require the NAV of any date.

What you need is:
SIP amountDates of SIP investmentsDate of redemption, andMaturity (redemption) amount
Illustration:
SIP of Rs 5,000 a month with redemption amount of Rs 31,000
Starting date of SIP: 01/01/2016,
Last SIP date: 01/06/2016
Redemption date of SIP: 01/07/2016
(For better understanding, only six months considered)

Open an excel sheet.
Step1: In column A, enter the transaction dates on the left sideStep 2: In column B, enter SIP figure of 5000 as a negative figure as it's an outflowStep 3: Against the redemption date (Column A), enter redemption amount (Column B) (31000)Step 4: In the box below 31000, type in: =XIRR (B1:B7, A1:A7)*100 and hit enter. XIRR, or the return on ones SIP investments, comes to 11.88 per cent.Connect with Experts - Wealth creation made easy
This story originally appeared on: India Times - Author:Faqs of Insurances