Click here to read the same article in Tamil
Click here to get the Excel template for calculating the SIP returns
Calculating SIP returns for current holding of mutual fund schemes
Click here to get the Excel template for calculating the SIP returns - Attached
sample template gives detailed working for all the three function. Usually XIRR
is widely used in mutual fund industry to arrive at returns. If you encounter
issues while using these functions, you can reach out to me
Click here to get the Excel template for calculating the SIP returns
Most of us are doing SIP now a day's and yes, it is
immensely popular among the investors! But do we know how our SIP is
performing? Mostly the answer is big “NO”. Someone may say that it can be taken
from the newspapers or media or directly from Fund house website. But I wish to
bring the fact to your notice - The return published in the open domain and the
actual return you are getting is different. So, what is the solution? How can
we calculate SIP returns for our investments? Continue reading to get these
answers.
Return percentage of mutual funds
Generally, the return we get in newspapers and web
are mostly of point to point basis and hence it’s called “Point to Point”
returns. These returns are calculated for example from January 1st to December
31st in the particular year or in the financial year from April 1st to 31st March
of next year. In reality these returns will be very different from the actual
returns obtained from our SIP Investments. The reasons are multi fold, firstly
our Investment month date and our maturity date are different. It is not from
the beginning of the financial year or calendar year. Secondly, most of the
time Returns are calculated only for lump sum amount invested in particular date
and redeemed in another date. They do not consider monthly instalment amount, so
in order to get actual Returns of our SIP investment from the date of
investment to maturity date, we can use Excel to get more relevant and accurate
return applicable for our SIP Investments.
Lump sum vs SIP return
Please refer the table to understand the returns
between lump sum investment and SIP investment.
Lump sum investment
|
SIP Investment
|
||
Initial investment
|
36,000
|
Monthly
Investment
|
1,000
|
Period in months
|
36
|
Period
in months
|
36
|
Final value received
|
53,336
|
Final
value received
|
44,941
|
Rate of return for this investment
|
14%
|
Rate of
return for this investment
|
14%
|
Absolute profit received
|
17,336
|
Absolute
profit received
|
8,941
|
- In both cases the investment amount of 36000 is the same.
- Invested period of 36 months (3 years) is also same.
- We also assume the return received in both investments @14% per annum compounded rate
The Absolute return difference is due to the fact
that in the first case all the amount of Rs 36,000 are available for
appreciation for the period after 36 months, whereas in the case of SIP the
first instalment of Rs 1,000 is available for appreciation in the same period of
36 months and the last instalment of Rs 1,000 is available for appreciation
only for one month. Hence the amount received in second case is very much less
even though the CAGR is 14% in both cases.
Calculating SIP return using Excel
Click here to get the Excel template for calculating the SIP returns
Return calculation of one-time payment and maturity is slightly easier than calculating the return when amount is paid monthly over several instalments. In that case we can calculate return using Excel “Rate” function. The arguments or the variables to feed into Excel function is given below:
Return calculation of one-time payment and maturity is slightly easier than calculating the return when amount is paid monthly over several instalments. In that case we can calculate return using Excel “Rate” function. The arguments or the variables to feed into Excel function is given below:
NPer
|
Number of instalments paid - In our example it is
36
|
PMT
|
Payment is monthly instalment paid in the mutual
fund. It is usually denoted in
negative sign because it is a cash flow out
|
PV
|
Present value is taken as zero because in the
start of the investment there is no value for investment
|
FV
|
Future value - It is maturity value or current
value of the Investments
|
Type
|
Whether interest is compounded from the beginning
of the period or at the end of the period, it usually taken as 1
|
Guess
|
Usually ignored and no input required. If Excel
throws error, input the guess value in percentage format very close to the
expected answer.
|
Generally calculating return using given value and
final value is not easy, Excel is arriving the result by trial and error method
Steps involved in calculating return using rate
function
See the image below.
The required arguments/variables are fed into Excel
function screen. On submitting, excel will give the result as 1.17 %. Excel will give monthly return
since we gave monthly instalment values. To get Annual return we have to
multiply this by 12 (12 * 1.17 = 14%) gives investment return
for the SIP as 14%.
Calculating SIP returns for current holding of mutual fund schemes
In the case of running SIP where investment is
active and running, we can calculate return using the same formula ‘Rate
function’. In this case we have to feed number of instalments paid so far and
the amount paid during each period. In case of future value we can take the
current market value by multiplying number of units accumulated using the SIP
and current NAV. By this we can calculate SIP return for the given
current SIP.
Important point to be noted here is the rate
calculation method for SIP return will work only in the case of equated monthly
instalments. Different functions can be used to calculate returns for
un-uniform flow of instalments.
IRR
function -> When SIP amount varies each month.
XIRR
function -> When the investment date is not uniform and amount paid also
varies.
