How to calculate returns from a SIP (Systematic Investment Plan)?

by Manshu on May 15, 2011

in Mutual Funds

About a week ago I exchanged comments with a reader who was comparing returns from a lump-sum investment in a mutual fund with a SIP (Systematic Investment Plan) in the same fund.

Basically, seeing whether Rs. 36,000 invested 3 years ago, amounted to higher or lower than Rs. 1,000 invested every month for 3 years in the same fund.

This is not an accurate way of comparing the two returns because in one case you have this massive capital that compounds for you for 3 years, whereas in the second case, you just have Rs. 1,000 that gets invested for the whole 3 years.

In my opinion, this is like comparing a fixed deposit to a recurring deposit. If you invest Rs. 36,000 in a fixed deposit for 3 years at 10% then at the end of 3 years you will get an amount of Rs. 47,916.

However,  a recurring deposit for Rs. 1,000 for 3 years at 10% will only give you Rs. 41,841.

Use IRR to calculate returns from a SIP

Since returns from a SIP involve outflow of cash at different time periods, and then a large inflow of cash at the end – you can use IRR to calculate the returns percentage from a SIP.

I have a very detailed post on IRR, so you can read that post to understand the intricacies of IRR, and then come back to read the rest of this post.

How to calculate returns from a SIP?

Let’s say I made a SIP in Canara Robeco which features as one of the funds in my post on the best balanced mutual funds.

Suppose I invested Rs. 1,000 every month for the last 3 years  - Moneysights has this calculator that shows me that this investment resulted in Rs. 47,927.79.

I use Excel and type in -1,000 in 36 rows followed by 47,927.79. Then I select this range and use the IRR formula to find the IRR value which I have to multiply by 12 to annualize this number.

This value comes out to be 17.93%.

To cross check this number I go back to the recurring deposit calculator and enter Rs. 1,000 per month enter the interest rate of 17.93% compounded monthly to get a value of Rs. 47,930.

This confirms that my calculation is accurate.

Just for reference – Rs. 36,000 invested in this fund 3 years ago will give you Rs. 48,171.60 today.

This is very close in absolute terms, but if you look at the CAGR then this fund only returned 10.19%.

As a side note – I’ve seen several websites use these type of calculations to tout the benefit of SIP, but they fail to mention that they’re working with the benefit of hindsight.

In general, I’d say SIPs will work better than lump-sum investment for most people but this is not the way to prove it.

Conclusion

Let me conclude by saying that this is a post about what I feel is the right way to calculate returns from a SIP, and not that a SIP is always better than a lump – sum investment. That’s a whole different discussion altogether, and it simply happened that the fund and time period I chose returned a higher rate of return by the SIP mode than the lump – sum mode.

 

Views: 34586


Get free daily updates in your email:






{ 22 comments… read them below or add one }

piyushmodi May 15, 2011 at 4:03 pm

a easier method might be just to assume that the entire sum paid over the period is invested for half of ( total period +1 ) and then calculate. Though this would be approximate, but should be a close approximation.

Reply

Manshu May 17, 2011 at 12:17 pm

You may not want to trade in accuracy for ease of use in this case esp. because all you need to do is enter a bunch of numbers in an excel sheet, and then that does all the hard work for you!

Reply

piyushmodi May 15, 2011 at 4:04 pm

this is applicable in case of equal installments. obviously same wouldnt be applicable when the amounts are different

Reply

Santosh Navlani May 15, 2011 at 10:28 pm

hi Manshu,

Just wanted to bring in to your notice – the moneysights link you shared, shows the first part of the post i.e. we allow users to check what the amount(s) would have grown in SIP vis-a-vis a lumpsum. The page doesn’t show the actual SIP returns (using XIRR method).

However, we do show people SIP returns of the entire MF portfolio as well as a specific fund that a user has in his/her portfolio. But this feature is limited to the portfolio manager (currently available under invite). Since you have one invite, i would be glad if you can try that feature out.

P.S. – We show SIP returns using XIRR only when the SIP is more than a year old. In case SIP tenure is less than 1 year, we show annual returns only.

Thanks!

Santosh Navlani | moneysights.com

Reply

Manshu May 17, 2011 at 12:16 pm

That’s very cool – thanks for letting me know – I wasn’t aware of that at all; will certainly check it out.

Reply

valueviews May 17, 2011 at 3:39 am

Very true . It totally depends on the time frame , there are times when SIP would appear to be fruitfull and at times lump sum would appear to be a better investment option .
Example let us compare two different years of investment . If you would have invested lumpsum in the year Jan 2007 to Dec 2007 and in the same tenure you would have invested in SIP than at year end that is DEC 2007 Lumpsum investment would have generated better return as the markets were in bull phase .
If similarly you would have invested lumpsum in Jan2008 and started SIP also inJan 2008 than at year end that is DEC 2008 , Investment in SIP would have appeared to be a better option as markets were continosly falling .
So the conclusion that you have drawn is absolutely correct

Reply

sorabh May 22, 2011 at 12:04 pm

Value reviews – What about sip vs lumpsum over a long term ? where you would have enough of booms and busts ? or bulls /bear cycles ? check out the comments on this post the one manshu might be referring to in the begining of this article.. i would like to see you take a shot at explaining ths ? as it will help me and many others… because i feel there is more to than just “lumpsum may be good if it is timed or SIP is “always” better as its a safe return”
http://www.onemint.com/2011/04/17/ft-india-dynamic-pe-ratio-fund-of-funds

sorabh

Reply

Anand May 22, 2011 at 12:01 am

Manshu,

Why haven’t you considered the time value of money in your calculations. Dropping in 36K at one go means I have given up on all other ways to multiply this money of mine. So, if suppose, I had this 36K in a savings account and take out 1000 from it every month – it means I am not only making money off the SIP, but the rest of my money is multiplying as well. Of course, there are more tax calculations to account for here that makes it all the more complex. But I guess, you got the point I am making.

Reply

Manshu May 22, 2011 at 3:31 pm

The goal is to find the rate of return on the cash flow every month, and that’s what I mean when I take the analogy of the FD and RD – I’m not looking at a starting capital of 36K and then take the route of SIP or FD, but rather illustrate how to calculate return from a SIP, so that it can be compared with a lump – sum investment.

Reply

bemoneyaware December 20, 2011 at 1:33 pm

The link – Moneysights has this calculator seems to be broken!

Reply

Santosh Navlani December 20, 2011 at 3:44 pm

@bemoneyaware – here is the correct link – https://www.moneysights.com/mutual-funds/details/64/Canara-Robeco-Balance—Growth

Hi Manhsu,
We recently changed the URL structure for some of the pages….maybe thats why this was discovered by @bemoneyaware.

Santosh Navlani | moneysights.com

Reply

bemoneyaware December 21, 2011 at 9:59 am

The fix was quick.
Thanks Manshu for following it up and Moneysights(Santosh) for fixing it.

Reply

Manshu December 21, 2011 at 9:15 pm

Thank you so much for pointing that out!

Reply

dhanasekar February 15, 2012 at 12:50 pm

Dear sir. I am dhanasekar working in a private concern from chennai,India. .I need some basic investment details about ” CANARA ROBECO MONTHLY INCOME PLAN ” . .

I want to invest 25000 Rs in this scheme. I need information from a customer point of view.. like
1.How much money should I invest at one time ?
2.How much money can I get monthly…? Is that money taxable?
3.How much time duration the plan exist.?
4.Any nomination facily available for this scheme.. ?
5.please give an explanation with example…

please reply me …

Reply

Sobh nath October 20, 2012 at 3:25 pm

I want to invest(single investment) Rs.25000 for 20 years in SIP or any mutual fund.
Wht is total amt(approx). After the period.
Is it safe&scure,

Reply

Vikas October 31, 2012 at 3:11 pm

Hi Manshu

Came across your post. Your calculation is ‘mostly’ right. Up to the point of calculating the monthly IRR, it is fine. However, you have made a mistake in converting that monthly IRR to a yearly IRR. Simply multiplying it by 12 is wrong. You need to account for the compounding effect. The right formula in Excel would be =power(1+monthly IRR, 12) which will give you a rate of 19.48%, not 17.93%.

Refer to the link below if you need further clarification on the calculation.
http://www.brighthubpm.com/project-planning/99310-converting-monthly-irr-to-annual-irr/

Reply

Manshu October 31, 2012 at 7:36 pm

Thank you for pointing this out Vikas. I’ve certainly made a mistake though I’m still thinking on what’s the best way to correct it.

I say that because if you compound 17.93% monthly, you will get the same result as 19.48% annually in this example. So, I need to make that point clear.

But I do really appreciate your pointing out this error and making this clear to me. Thanks!

Reply

Balaji December 20, 2013 at 3:34 pm

The formula for determining the Annualized IRR is power(1+monthly IRR, 12) -1. (The -1 was missing)

Reply

Amar July 2, 2013 at 5:33 pm

Hi Manhu,
I am totally new to this terms. I am investing in few MF for quite a time, with the help of the agent. I am being investing from 2007 to now, I want to know exact returns(Including Dividends received). Also I started with Rs 1000/ month, then few months later I changed it to 1500/month again I changed to 2000/month. So How do I calculate?

Please help

Reply

Hema December 16, 2013 at 7:01 pm

Hi ,

Can you throw some light on what is CD accounts. and is it available in India and is it a good option for investment.

Reply

Balaji December 20, 2013 at 3:39 pm

Hi Manshu,

If you “type in -1,000 in 36 rows followed by 47,927.79″ and use the IRR formula in MS-Excel it will most likely give you a #Div/0! or #NUM! errors. The tip is to use -0.1 (-10%) as guess value, to get the result, as described in Microsoft Help pages.

Reply

krishan mohan June 27, 2014 at 4:45 pm

i want about per year interst of sip

Reply

Leave a Comment

Previous post:

Next post: