How to calculate the actual return of SIP of mutual funds?

These days, people are quite aware of the importance of personal finance and investment. People prefer to invest via mutual funds as they offer an opportunity to diversify portfolios over various asset classes. 

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 & 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 on a regular basis. However, you can also calculate returns by XIRR in case of lumpsum investment.  Let’s look at the illustration of how XIRR is calculated:  

Illustration:  

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

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

Step 1: Open 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 cashflows at distinct dates that’s why we have used the XIRR function in order to compute rate of return. What if in above example the investment was done in lumpsum, what will be rate of return if we calculate using XIRR function:

Computation of return using XIRR function                                                                                                                             

Computation of return using Rate function     

In the above table, we calculated returns generated by investing lumpsum amount using XIRR function and normal rate function. As in case of lumpsum investment we can calculate return on investment using normal rate function as there are no complications like SIP. We can see there is nominal difference in outcome of both functions. So, in case of lumpsum investment you can use any of the above function but incase of SIP you can only use function either XIRR in case of uneven or irregular interval of time or IRR incase of even or regular interval of time.