In the complex world of stock investing, fundamental analysis is one of the processes that investors find most reliable. Since fundamental analysis calculates the internal value of a stock and determines whether it’s over or undervalued, it comes close to predicting how the market will eventually react price-wise.
Of course, fundamental analysis doesn’t depend on just one variable or metric. Its strength lies in the process of checking and double-checking the numbers in every way until predictions are sufficiently reinforced.
One crucial metric in this process is the XIRR, which stands for Extended Internal Rate of Return. XIRR is a robust and popular metric that goes beyond what’s attainable by other traditional measures. It unveils the true story behind numbers, accounting for the timing and magnitude of both cash inflows and outflows.
In this article, we’re going to dive into what the XIRR is, what it can be used for, how it’s calculated, and why you should use it the next time you’re fundamentally analysing a stock.
You may also like: Beat the market: Your ultimate guide for fundamental analysis tools.
What is the XIRR?
The Extended Internal Rate of Return is the return you get on your recurring investments. If you have an SIP in a mutual fund and you invest a certain sum every month, the XIRR will help you understand what you’re actually earning. Essentially, this metric is used when returns have to be calculated with multiple transactions.
This also comes in handy in more sophisticated investment strategies where investors get the option to have an early exit or withdrawal from a long-term investment strategy. Some also skip instalments and still want to know how much they’re making.
If you invested ₹100 in a mutual fund and withdrew ₹110 at the end of the year, you simply made 10% on your investments. And that’s enough information for most people.
However, in more complicated investment ventures, investors need to know which time period made the most returns, which deposits came up with losses, and when they should consider reinvesting. That’s where the XIRR comes in.
Calculating XIRR on Excel
Microsoft Excel is a great tool to use to calculate your XIRR. This is the formula template you can use:
XIRR (value, dates, guess)
How to use the formula:
- Enter all your transactions in one column. Correspondingly, enter the dates for all transactions. Make sure that your SIP purchases are marked negative (outflow) and redemption or selling transactions are marked positive (inflow).
- In the last row, mention the current value of your investments with the current date. Make sure your first investment date along with your redemption date is accurate.
- Now, use the XIRR function using the above formula template.
- Now, in place of “values” select the cash flows, in place of “dates” use the date column, and the “guess” is optional. Excel uses 0.1 by default if you don’t make an entry of your own.
When the formula is placed currently and the data is accurate, the cell returns a single numerical value. This is your XIRR. It is the consolidated return of your investments considering the timings of your investments and withdrawals.
Why not use the CAGR?
We’re assuming you’re using an SIP to invest in a mutual fund. Most people use a Consolidated Annual Growth Rate (CAGR) calculation to gauge how good the returns from a particular fund are. These could be 3-year, 5-year, or even 10-year CAGRs.
Here’s the simple difference between a CAGR and XIRR.
CAGR, by principle, calculates the adjusted return of a lump-sum investment over a given period of time assuming no adjustments, deductions, or additions. So, the CAGR doesn’t really take into account SIPs where you invest a fixed amount every month.
The XIRR, on the other hand, has the ability to take into account irregular and regular purchases, withdrawals, and even missed payments and consolidate them into the return calculation. For those who want more accurate reports of what they made on any investment, XIRR is the way to go.
XIRR is a fantastic tool to use to gauge if your investments are giving you the returns you want. Generally, an XIRR of around 12% is considered decent for equity-based mutual funds. For funds that invest in debt, anything above 7% should be a good benchmark.
I am passionate about stock investing and have a knack for simplifying complex market concepts. Providing readers with valuable insights and empowering them to make informed investment decisions is my jam.