XIRR: Way to calculate Mutual Fund SIP returns

A college friend bought a property in one of the suburban areas of Mumbai for Rs 30 lakhs in 2002. He recently sold the property at Rs 1.5 Crores – a profit Rs 1.2 Crores on an investment of Rs 30 lakhs. Many of my other friends were super impressed with the profits which this friend made – but I was not as impressed as my other friends. Let me explain why –

While this friend’s absolute returns were good, I use a metric known as Compounded Annual Growth Rate (CAGR) to evaluate the performance of investments. The CAGR of my friend’s investment was 10.6%. While this was more than returns from FD or other risk free investments, top performing diversified equity mutual funds over the last 15 years have given more 15% CAGR returns.

What is CAGR?

CAGR, as the name suggests, is the rate at which your investment grows per annum over the investment period, assuming annual compounding. The equation for calculating CAGR is as follows:-

Unlike Absolute Returns, CAGR is not biased by how much you invested or for how long you remain invested. CAGR is the best measure of investment performance. As per regulations in India, mutual funds are required to use CAGR to calculate returns for investment periods of more than one year. However, the problem is CAGR is that, it works only for one-time investment and one-time redemption.

Limitation of CAGR

Many investment plans comprise of series of investments and / or series of withdrawals (e.g. SWP). Take the example of my college friend’s property investment. My CAGR calculation was simplistic because I assumed that he invested one time. In reality, he had a taken a home loan, for which he was paying monthly EMIs. He also rented out his property, a few years after he got possession of the same and received rental income. In other words, my friend’s investment comprised of a series of cash-flows, either outflows (investments) or inflows (withdrawals).

Similarly, mutual fund investment plans like Systematic Investment Plans comprises of a series of cash-outflows for the investors and Systematic Withdrawal Plans comprises of a series of cash-inflows.

Internal Rate of Return (IRR)

The metric used to measure the returns of a series of cash-flows is known as Internal Rate of Return. Internal Rate of Return (IRR) is of great importance in Project Finance and is used to evaluate feasibility of capital projects like infrastructure projects (ports, highways, power etc.), new manufacturing facilities etc. It is also has a lot of importance in the world of investments.

The textbook definition of IRR is complex, involving cash-flows and Net Present Value (NPV). Keeping the larger cross-section of our readers in mind, we will skip the textbook definition of IRR. Investors can think of IRR as the annualized discounted cash-flows (DCF) rate of return. In the DCF method, cash-flows are discounted at a certain rate (IRR) based on when the cash-flows occur.Cash-flows (either inflows or outflows) which occur earlier in the investment tenure are discounted less, while cash-flows which occur later are discounted more.

Investors can use Internal Rate of Return to calculate the returns of their SIP or SWP investments because it takes it account, all cash-flows and the times at which cash-flows take place. Some of readers have written to us, asking for the formula of IRR. The IRR equation is quite complex – a trial and error method is required to calculate IRR manually. It will be difficult and time consuming for the average investor to calculate IRR using a calculator. Fortunately, investors can easily calculate IRR of cash-flows using the built in formula for IRR in Microsoft Excel. Please see the screen shot below.

Limitation of IRR

The biggest limitation of the IRR formula in Excel is that it assumes that cash-flows are equally spaced in time. In other words, when you use the IRR formula in Excel, the time interval between any two adjacent cash-flows must be the same. If the time intervals are not exactly the same, then Excel will return an incorrect value. From the perspective of practical applications, this is a serious drawback because time intervals between cash-flows are rarely the same throughout the tenure of the investment.

Take the example, of a regular monthly SIP. Even though there is a fixed SIP date, the number of days between two SIP instalments is likely to be different from month to month. Firstly, different months have different days – some month has 30 days, some 31 and some 28 or 29. Secondly, there is always the possibility that the SIP date in some months will be non-business day (e.g. weekend, holiday etc.) and the transaction will take place on the next business. To complicate matters further, as you continue your SIP, you may make additional purchases or partial redemptions – these transactions can take place on any day. Last but not the least, you may decide to stop your SIP but not redeem your units.

XIRR

Fortunately, Microsoft Excel has a built in formula called XIRR which can take care of all the complications described and more. XIRR is simply a modification of IRR with the additional capability of factoring in uneven periods of time. All you have to do is to enter the transactions (SIP instalments, additional purchases, redemptions etc.) and the corresponding dates. You will get all these details from the account statement sent by the fund house. Then apply XIRR formula and Excel will calculate the SIP returns for you. Please see the screen-shot below for XIRR calculation of a simple SIP.

Please note that for calculating XIRR all investments (SIP instalments, additional purchases etc.) have to be entered as negative values (affix minus sign before the amount) and all redemptions have to be entered as positive values. If you have not yet redeemed all your units then the current investment value has to be entered along with the NAV date. If you have invested in dividend re-investment options, then the dividend re-investment will show up as a transaction in your account statement but dividend re-investment should not be included in XIRR calculation because it is not a cash-flow for you.

Conclusion

We started this post with a discussion on CAGR but ended with XIRR.

Most investors are familiar with the concept of CAGR, but are not very familiar with XIRR. Like CAGR, XIRR also used the concept of compounding – in fact, you can think of XIRR as an aggregation of CAGRs. In this blog post, we have discussed why XIRR should be used to calculate SIP returns. XIRR can also be used for SWP, dividend payouts, annuities and other types of investment plans. In Advisorkhoj Research we always use XIRR for calculating SIP and SWP returns. For your SIPs in your investment portfolio, you should always use XIRR. If you have questions or comments, do feel free to write to us or post your comments below.