Mutual Fund Unlocked: XIRR - Calculating your portfolio performance

Calculating performance is the cornerstone of evaluating investments. It answers many of the important and critical questions such as if you should continue with your current investments and portfolio or you need to churn it. Nevertheless, the calculation of performance is not as easy as many of us believe, especially, if it involves multiple investments for multiple periods.

Broadly, there are only two ways of calculating your investment returns, one is simple returns which only considers the start value and end value of your investment, without considering when such investments were made and withdrawn. Other measures of performance take into account the time, value of money and calculate performance on the basis of when you have made the investment and when you have exited the investment. The former way of measuring performance can be used for lump sum investment while latter is used for systematic investment plan (SIP).

Simple Return or Point to Point return

It gives you the absolute return that your investment has generated and requires only initial net asset value (NAV) and current NAV. It is calculated as

Simple Return = (current NAV - initial NAV)/ initial NAV x 100

The above formula can be further modified to annualise your returns if your holding period is not perfectly one year.

Annualised returns = ((1 + Simple Return) ^(365/number of days)) – 1

Example: Assuming you had invested Rs. 1 lakh in an MF scheme on January 21, 2017, at a NAV of Rs. 10. Now (February 26, 2018) its NAV is Rs. 35.

The simple return will be = (35-10)/10*100 = 250%.

Simple annualised return = ((1+2.5) ^ (365/401))-1 = 212%

SIP Returns using XIRR (Extended Internal Rate of Return)

As mentioned before, XIRR or Extended Internal Rate of Return is a method to calculate returns on investments when you are investing and exiting investments at various points of time. It is mostly used when the cash inflows and outflows happen at the different point of time and are not regular. This is the case of SIP, where you keep on investing regularly usually a fixed amount and on a certain date.

On every instalment of investment, you get certain units of mutual fund scheme depending upon the NAV of the scheme. At a certain date, investor either redeems the total accumulated units or part of the units. This creates a positive cash outflow. To add to the complexity, you have multiple SIPs and you redeem units at different point of time. Therefore, in such conditions, you use XIRR a function in Excel (Microsoft Office) for calculating the XIRR or annualized yield for a schedule of cash flows occurring at irregular intervals.

To calculate XIRR you need,

·         SIP amount

·         Dates of SIP investments

·         Date of redemption, and

·         redemption amount

Example:

SIP of Rs 10,000 a month for 12 years with redemption amount of Rs. 1,50,000

Starting date of SIP: 01/01/2017,

Last SIP date: 01/12/2017

Redemption date of SIP: 31/12/2018

Calculation of XIRR in excel

We hope next time you calculate returns, you'll use the proper method to make an informed decision.