A lot of investors stick to SIPs (systematic investment plans) in mutual funds, Ulips, etc where a fixed amount is invested at a pre-determined point of time. It’s not just investors wary of stock market vagaries who choose to go the SIP way of investing. There are various good reasons to invest in SIPs, but the two most compelling reasons making its case stronger are, namely, rupee cost averaging and doing away with the need for timing the market.
Ask any financial planner or an investment advisor the secret of creating wealth and he would most likely recommend SIP as one of the ways. The crux of SIP way of investing is that small investments, over a period of time, diversify our investment, benefit from market volatility and thus result in large wealth and help fulfill our dreams & aspirations.
But a big question arises when it comes to calculating the return of investment (ROI). How to calculate ROI on your mutual fund SIP? If you’re an SIP investor, do you calculate the return by subtracting the total return from the total investment over the years? If so, that is not the proper way of calculating and in fact it’s a misleading return.
Time Value of Money
SIPs allow us to invest very small amounts (Rs 1000-Rs 2000), as against larger one-time investment required, if we were to buy directly from the market. This makes it difficult to calculate its return over a period of time.
Suppose, you start investing Rs 1000 in a fund through SIP on the first of each month, say, Jan 1, 2014. The unit price of the fund is Rs 20 (units 50), Rs 22 in Feb (units 45.46), Rs 23 in March (units 43.48), Rs 25 in April (units 40), Rs 30 in May (units 33.33) and Rs 31 in June 2014 (units 32.26). Suppose you decide to redeem your total fund units (244.53) on June 1, the return you would be getting is 244.53*31= 7580.43.
You paid for six SIPs, that is, RS 6,000 while the return is Rs 7580.43. You would be forgiven to believe that the return on your investment is 26.34%. In reality though the return is not this!
Not only mutual funds, we invest a regular fixed sum in other financial instruments such as Ulips, life insurance policy premium, and in many cases, in PPF. After investing for 5, 10 years or 15 years, many of us may want to redeem our investments. At that time, we usually calculate the amount we invested in the beginning with the maturity amount that we got and to understand how much we gained.
But don’t fret! There is a simple and most popular method to do it — IRR or internal rate of return method. Before going into IRR it’s important to understand the concept of NPV or net present value (NPV) as without understanding NPV it’s difficult to understand IRR.
The fundamental principle of NPV is the time value of money that says the cash in your hand now is more valuable than the cash you would receive tomorrow or later on because of the inflation factor.
For example, let’s say you get 9% interest on your FD with a bank in a year. So Rs 100,000 that you’ve now could earn Rs 100,000 x 9% = Rs 9000 in a year. This means your Rs I lakh now would become Rs 1.9 lakh by next year. So Rs 109,000 next year is the same as Rs 100,000 now, or we could say the Present Value of Rs 1.09 lakh next year is Rs 1 lakh.
Now that you’ve understood Present Value, you can figure out the Net Present Value (NPV). NPV in simple terms means the present value of net cash inflows and outflows, i.e. adding the present values of what you receive and subtracting the present values of what you pay or invest. So while investing a sum each month/year, you need to first check how much that future money, incoming and outgoing, is worth today to arrive at the NPV. In financial terms, NPV is the sum of each cash inflow/outflow that is discounted back to its present value.
Internal Rate of Return or IRR is basically an interest rate and the bigger the better.
Excel Makes It Simple
You don’t have to do all these mathematical calculations to know the return on your investment. There’s a simple and smarter way to do this — MS Excel. Let’s first understand time value of money through Excel.
Open an Excel sheet and click on 'formulas' on top. You’ll see ‘Financial’ dropdown list, click on that and select ‘FV’ from the drop-box menu. The following box will appear after selecting 'FV': Let’s understand how to use this Box and the inputs. ‘FV’ stands for future value, or a cash balance you want to have after the last payment is made. ‘Rate’ is the interest rate per period. Interest is normally given per annum. ‘Nper’ is the tenure of investment. ‘Pmt’ is the payment made in each period. ‘PV’ is the present value, or the lump-sum amount that a series of payment is worth now. ‘Type’ is a value representing the timing of payment; payment at the beginning of the period =1 (e.g. SIP, premium); payment at the end of the period =0 (e.g. EMI of home loan). If you don’t mention any thing then by default Excel will consider payment made at end of period i.e. 0.
Now suppose you want to compute FV after 12 years of your lump sum investment of Rs 5,000 today at a rate of 9% per annum. In the PV box put -5000 figure (you’ve to put ‘minus’ because the payment means cash outflow, hence a negative sign). Put 12 in ‘NPer’ box, and 9% in the ‘Rate’ box. You’ll see 14063 in the bottom of the box i.e the future value after 12 years of Rs 5,000 invested today at a rate of 9% per annum. Let’s move now a step further and calculate the return on investment made not in lump-sum but periodic i.e. SIP. Let’s assume you’re investing Rs 10,500 a month and want to accumulate Rs 20 lakh in 10 years. In order to know the rate of return of your investment, you’ll have to choose a different Excel function. From ‘Financial’ dropdown list, select ‘RATE’. A box will appear after selecting 'RATE' (See Calculating SIP's ROI)
In the ‘NPer’ section you’ll have to multiply years of investment by 12 in case of monthly investment, in this case 10*12. In the ‘Pmt’ section, put 10,500 which is the amount of payment made in each period. In FV section, that is the future value of your investment, put 2000000. You’ll see the figure 0.007266 or 0.727% (0.73% after rounding off).
However, 0.73% is the monthly rate of return and in order to get annualized return you’ll have to multiply this figure by 12 and what you get is 8.72%. What it means is that if you continue to invest Rs 10,500 per month for 10 years, your investment will have to grow at 8.72% yearly to make you build a corpus of Rs 20 lakh in 10 years.
Now let’s put this same formula in the context of measuring SIP return. Suppose you want to measure the 5 years return of your mutual fund SIP in, say, ICICI Prudential Top 200 Fund (G). Suppose you started investing in this fund in Oct, 2009 for 5 years putting Rs 5,000 each month as SIP. After 5 years, the total value of your investment is now Rs 5 lakh. Using the Excel formula, you’ll get the monthly SIP return of 0.016150266 or 1.615026% which translates to an annualised rate of 19.3%.