Markets
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:
3.5

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.

## Get A Call From Industry Experts

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...

## Hospitality stocks gain the mojo back; Easy Trip Planners jumps 44.72 per cent in one month alone

### IRCTC is a known name from the sector that is outperforming markets by a good margin.

Yogesh Supekar / Article rating: 3.5

IRCTC is a known name from the sector that is outperforming markets by a good margin.

## 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...

## Alert: It's raining Mutual Fund Schemes in Indian markets

### Sachin Bansal-backed Navi Mutual Fund has filed draft papers for four more mutual fund schemes with the Securities and Exchange Board of India (SEBI).

Karan Dsij / Article rating: 4.0

Sachin Bansal-backed Navi Mutual Fund seems to have been following this adage to perfection.

## These Textile stocks gained up to 29.43 per cent in one week alone!

### The week gone by saw several textile stocks outperform on bourses with some of the top gainers soaring by ~30 per cent.

Yogesh Supekar / Article rating: 3.7
Next week however promises to be a challenging time for the textile sector stocks as there are unconfirmed reports of GST being increased on...

## Five metal stocks that delivered more than 140 per cent gains

### Compared to a 53 per cent return in the Nifty 50 benchmark, the Nifty Metal index has outshone the broader benchmark index, moving up 133 per cent.

Abhinav Lahoti / Article rating: 4.8
Compared to a 53 per cent return in the Nifty 50 benchmark, the Nifty Metal index has outshone the broader benchmark index, moving...

## Explained: Country risk premium

Vishwajeet Bhandigare / Article rating: 5.0
Any additional risk in the underlying asset is expected to be rewarded accordingly by any risk-averse investor. A risk premium is...

## Five stocks that turned Rs 1 lakh to Rs 1 crore in a matter of time

Abhinav Lahoti / Article rating: 3.6
Patience is one of the biggest virtues you need to have if you want to become a millionaire in the stock market. And along with...

## Valuable PTC facts you should know!

Shruti Dahiwal / Article rating: 4.0

A pass-through certificate or PTC is a certificate issued to an investor against mortgage-backed security.

## PMS vs MF: Which is the better option?

Shruti Dahiwal / Article rating: 4.0
PMS is not everyone’s cup of tea. It is more suitable for investors who are high net worth individuals with a big risk...