How to calculate SIP returns

Your high school mathematics is good enough while calculating returns on a lumpsum investment. However, if you are investing regularly, say, every month via a Systematic Investment Plan (SIP), high school math may not be enough to solve the problem.

An SIP investment takes place on a particular date at regular intervals. When you make the investment, you will get a fixed number of units depending on the prevailing NAV of the scheme at that time.

Over a period of time, you would accumulate a large number of units. That is why it becomes difficult to find out the total returns you have earned over a period. This is because every SIP instalment may have fared differently.


Don’t worry. All you need is a computer and the Excel program to calculate the return of all those investments made over a period. You can use the XIRR formula to calculate the returns your SIPs have made over a period.

Step 1
Type all your SIP dates in one column. Suppose you have started investing in January 2016 and your SIP date is 10^th of every month.

Step 2
Enter the SIP amount in another column corresponding to the SIP dates. For example, the SIP amount is Rs 2,000. You will have to enter the amount as -2000. Prefixing a minus sign is important as it depicts outflow of cash.

Step 3
Enter the total market value of all your units. Just enter the date at which you want to check the returns and the market value of all the units you hold in the scheme in the same columns where you have entered the SIP date and SIP amount.

You can check the market value of your holdings by logging into your online account with the AMC or through your latest statement. For this example, we will assume the date as 10 January, 2017 and the market value as Rs 28,000.

Please do not prefix a minus sign with the market value of your units as this will be an inflow of cash when you redeem your units. The redemption proceeds might not be the same as market value. It might be lower if there is an exit load.

Step 4
Use the XIRR function: Move your cursor to a blank cell and open the XIRR function. It will ask you to fill three parameters: Values, Dates and Guess. For Values, select the cells with SIP amount and market value. And, for dates select the cells with SIP dates and the date at which you are calculating the returns. You can leave the Guess field blank and click on OK button.

Step 5
Multiply the decimal number by 100. The result would show the return you have earned on your SIP investments on the date of your choice. In this example, the return works out to be 31.96 per cent.