Computation of the actual rate of return on SIP of mutual funds!

People prefer SIP as they can invest in small amounts and also, get the benefit of rupee cost averaging.

Presently every investor is getting aware of the fact that personal finance has become a crucial aspect of an individual’s life and which needs to be planned with appropriate understanding. A mutual fund is an ideal option for those investors, who are embarking on their journey in the equity market as it is managed professionally and has lower risk as compared to direct equity.

Investment in a mutual fund can be done in two ways i.e., via lumpsum and SIP. Generally, people prefer to invest via SIP as they can invest in small amounts and also, get the benefit of rupee cost averaging. So, now, the question arises as to how can we calculate the actual return on our SIP investments? The answer to this question is – by using the XIRR function in excel.

XIRR or extended internal rate of return is the function that can be used to calculate your real investment return. Calculation of returns in the case of SIP becomes quite difficult as you make multiple investments at distinct times. Calculating return on lumpsum investment is quite simpler than SIP as there are no complications related to a distinct time. Besides, multiple amounts of investments can be done regularly.

 
Let’s look at the illustration of how XIRR is calculated:

Illustration:

Suppose, you are going to make 12 monthly instalments of Rs 5,000 and the maturity amount stands at Rs 65,000. The start date of SIP is January 1, 2020, and the date of redemption is December 31, 2020, then what rate of return will you receive? 

Following are the steps to calculate actual investment return on investment:   

Step 1: Open the MS Excel sheet and enter the dates of your investment and investment amounts.    

Step 2: Use the XIRR function. The formula of XIRR in MS Excel is = XIRR (values, dates, guess).    

Step 3: Fill required fields in the XIRR formula and you will get your real investment rate.    

 
As you can see, in the above table the returns generated are 16.64% if you invest 5000 every month for 12 months. As you can see there are multiple cash flows at distinct dates that’s why we have used the XIRR function to compute the rate of return. What if in the above example the investment was done in lumpsum, what will be the rate of return if we calculate using the XIRR function:

Computation of return using XIRR function

As we can see in the above calculation that XIRR has come out to be 16.72%. This is how actual rate of return is calculated on the SIP you do. The above calculation is for illustration purpose.