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

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

by 5paisa Research Team Last Updated: Nov 02, 2021 - 11:44 am 47.9k Views
Listen icon

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.

Share Market Today


How do you rate this article?

Start Investing in 5 mins*

Rs. 20 Flat Per Order | 0% Brokerage

378X91-D3

About the Author

Our research team is composed of some highly qualified research professionals, their expertise range across sectors.


Enjoy 0%* Brokerage with 5paisa
Resend OTP
Please Enter OTP
Mobile No. belongs to

By proceeding, you agree to the T&C.

Latest News
Trust Fintech IPO Subscribed 108.63 times

Trust Fintech IPO is book-built issue of ₹63.45 crores, consisting entirely of fresh issue shares totalling 62.82 lakh. Trust Fintech IPO commenced its subscription period on March 26, 2024, & concludes today, March 28, 2024.

Aspire & Innovative IPO Subscribed 15.17 times

Aspire & Innovative IPO is a book built issue of Rs 21.97 crores. The issue comprises entirely a fresh issue of 40.68 lakh shares. Aspire & Innovative IPO opens for subscription on March 26, 2024, and closes on March 28, 2024. The allotment for the Aspire & Innovative IPO is expected to be finalized on Monday, April 1, 2024.

Blue Pebble IPO Subscribed 56.32 times

Blue Pebble IPO, valued at ₹18.14 crores, comprises fresh issue of 10.8 lakh shares. Commencing subscription on March 26, 2024, Blue Pebble IPO is set to conclude on March 28, 2024. Allotment process is scheduled to be finalized by Monday, April 1, 2024. Following this, IPO is slated to debut on NSE SME, with tentative listing date of Wednesday, April 3, 2024.