BSE See NSE See 59,015.89
-125.27 (-0.21%)

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

Siddhi Sharma
/ Categories: Knowledge, MF
Rate this article:
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:  


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.

Please login or register to post comments.

Get A Call From Industry Experts

Send Otp

   I authorize DSIJ Pvt Ltd to contact me. This will override registry on the NDNC.


Do you know these auto and auto ancillaries stocks more than doubled in 2021 alone?

After having traded negative in a three month period the BSE Auto index is up by over 3 per cent in one month and about 2.6 per cent in past one week alone.

Yogesh Supekar / Article rating: 4.0
In 2021 we find that several stocks from the auto and especially auto ancillary sector have more than doubled with the top gainer jumping higher...

Multibagger Alert: These Finance Sector Stocks have gained up to a whopping 3330.77 per cent in 2021 alone!

Some of the financials that outperformed the markets have turned out to be multibaggers with the top gainer Flomic Global Logistics jumping higher by a whopping 3330.77 per cent in 2021 alone.

Yogesh Supekar / Article rating: 3.3
The leveraged businesses considered most risky, however are seen making a strong comeback with Nifty Bank trading at all time highs. It is in fact...