Calculating SIP return using Microsoft Excel

Click here to read the same article in Tamil 
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

Absolute amount received in the first lump sum method is Rs 17,336. But shockingly for someone, in SIP amount received is only Rs 8,941.

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:

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.

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