Here is how you can calculate returns on your SIPs

• Follow

It is one of the biggest myths even today, many investors consider that systematic investment plans (SIPs) and mutual funds are the same. The real truth is that the SIPs are just a method of investing in mutual funds, and the other method being a lump sum to invest.

A systematic investment plan or SIP is a process of investing a fixed sum of money in mutual funds at regular intervals on a weekly, quarterly, or monthly basis.

That said, the SIP requires an investor to regularly invest over a lengthy period of time and get the lump sum amount upon maturity. SIP investments occur on a schedule determined in advance by the investor, and even the SIP amount is predetermined.

An investor will receive a set number of units based on the fund’s net asset value (NAV) on that particular day. As a result, he should continue accumulating units from the start of his SIP. He will receive the maturity amount on the day he redeems the fund, which is equal to the NAV on redemption day multiplied by the total units on redemption day.

How can one calculate returns on SIPs?

An investor can use the Extended Internal Rate of Return (XIRR) function in a Microsoft excel sheet to determine the rate of return on a fund. It’s straightforward, and there’s no requirement for a specific date’s NAV. The XIRR formula is used in mutual funds to compute returns on investments where multiple transactions occur at various periods.

When it comes to other returns like compound annual growth rate (CAGR) or point-to-point returns, they are irrelevant or cannot be used in the case of numerous cash flows, while XIRR in mutual funds can be applied.

“We have graduated from using calculators to Excel. And now from Excel to online tools.  There are many online SIP tools available where you can visit and have look at how your SIPs are doing. You can always visit AMC’s websites. Most of the AMCs have online SIP tools, or else you can have an account with a digital platform. These tools will give your SIP returns in a click. Also, you can have a graphical representation of all you SIP investment at one place. This is a simpler and easier way. You can make scenarios and have a rough idea of how your portfolio looks,” said Sandeep Bhosle, VP- Customer Interaction, Quantum AMC.

Steps to calculate

Simply enter the transactions (SIP installments, additional purchases, redemptions, etc.) and their associated dates. You will obtain all of these facts from the fund house’s account statement. Then, using the XIRR formula, an excel sheet will compute the returns on your SIP investments for you.

Open an excel sheet and perform the following steps

-On the left side of column A, enter the transaction dates.

-In column B, provide a negative value for the SIP of say Rs5000, as this is an outflow cash flow.

-Enter the redemption date (Column A) and the maturity redemption amount (Column B)  i.e. Rs 31,000.

-Type ” =XIRR (B1: B7, A1: A7)*100 ” in the box below 31000 and press enter. An XIRR value of 12% will be displayed as a result.

Points to note while calculating

To calculate the XIRR on mutual funds, all cash outflows (SIP installments, lump-sum purchases, etc.) must be recorded as negative values (add a minus sign before the amount), and all cash inflows (systematic withdrawal plans, dividends, and redemptions, etc.) must be entered as positive numbers.

If you have not yet redeemed all of your units, you must enter the current investment value along with the date of the NAV to calculate the XIRR on your mutual fund investment. Certain operations, such as dividend reinvestment, do not generate cash flows and should therefore be excluded from the XIRR calculation.