Calculating annualized ROI of Mutual Fund SIP investment with Dividends

Systematic Investment Plans (SIP) is my favourite way to invest in mutual funds (MF). With my regular job, I don't have the time to time the market and make exponential profits. SIPs allow me to invest a certain amount from my income every month to various MF schemes and benefit from averaging out my investments over the long term.

The benefit of averaging out investments

Initially, I started out by investing ₹ 5,000 on the 25th of every month. However, I noticed that I was not averaging the market volatility properly. My investments were made on 12 specific dates for a year, and I missed out on volatility between any two dates. So, I started spreading my investments over a month. For instance, instead of one instalment of ₹ 5,000 per month, I would invest 5 SIPs of ₹ 1,000 each on the 1st, 7th, 14th, 21th and the 28th of a month. This way, my investments reflect a better average of the year-round volatility.

The problem of monitoring SIPs and calculating ROI

I like to monitor my investments and understand the movement of markets and how they affect my ROI. However, with SIPs spread over multiple dates in a month, the number of instalments quickly add up. There are many websites, such as moneycontrol.com, which allows you to create an online portfolio and monitor your investments. However, they show your investments as a spreadsheet and as a summary of return, which is usually not annualized.

What is annualized returns, you ask? Annualized returns are returns re-scaled to 1 year for assets held for different amounts of times. For example, let's assume an SIP of twelve ₹ 5,000 instalments to be invested on the 25th of every month. After a year, the 1st instalment would have been invested for 12 months, the 2nd for 11 months, so on so and so forth. Annualized return would calculate the return of each instalment / or all instalments for 1 year period so that they are comparable, not only with each other but also with other forms of investments such as savings account interest rates, fixed deposits, corporate debt, etc.

A picture speaks a thousand words

Calculating annualized returns for SIP investments is helpful. Even more helpful is a graphical representation of my investments:

Charting an SIP investment

This chart shows how instalments in an SIP are averaged out over a 2 year period. The red line represents the NPV of individual instalments. The blue line shows how the average moves over time. The green line is the current NPV of the MF scheme. The ROI of this investment is approx. ₹ 2.9 per unit (the difference between the green line and the blue line on 14-Oct-19).

What is the annualized return though?

ROI of ₹ 2.9 per unit is an absolute return. Since the average investment is ~ ₹ 17.1 per unit, can we say that our ROI is 2.9/17.1 = 17% per year? The answer would be no. For annualized ROI, we have to take into account the duration that each instalment is for. The 1st instalment in our example above is made on 14-Sep-17 and stayed invested for over 2 years. Moreover, this is a dividend-paying scheme (notice the NPV drops around Dec 2017 and 2018), so there were cash inflows that need to be taken into account as well.

So, we use an Excel spreadsheet to organize our SIP instalments and dividend received:

SIP outflows and inflows in an Excel spreadsheet

Note the three columns on the right provides the data for the chart. This Excel file is attached below for download. We use the XIRR function in Excel to calculate the annualized return (using "Inv. date" and "Inflow / Outflow" columns. Considering the various dates of investment and dividend received, the ROI of this particular example is 28% per year.

You can use the link below to download this Excel file and look at various functions that I have used in this example. Feel free to modify the file to chart your own SIP investment and calculate the annualized ROI.

MF SIP annualized ROI v2