What is XIRR- Definition, Formula & Calculation
XIRR, or Extended Internal Rate of Return, is the correct method to calculate the multiple cash flows and especially, for portfolio returns. But why?
Suppose, we invest a certain amount of money in single or multiple financial instruments like stocks, bonds, mutual funds, precious metals, real estate, or small businesses. Let’s say after a period of time of 5, 10 or 15 years, we wish to redeem our investment amount.
As Abigail Johnson said, “Returns matter a lot. It’s our capital.” This makes returns a benchmark while you are doing portfolio investing. Generally, we end up calculating the return amount by observing our original invested amount with the maturity amount we receive to understand the total gain. Isn’t it?
But is this the right way?
Read further to know what is XIRR, XIRR meaning, its importance, the calculation process, and more.
What is XIRR?
XIRR (Extended Internal Rate of Return) is a financial metric used in the stock market to calculate the rate of return of an investment based on its cash flows. The XIRR meaning is the returns earned by you over a period of time, and amount of all cash inflows/outflows, including dividends, capital gains, etc.
XIRR return is a valuable tool for portfolio investors, as it allows them to compare and identify which shares in their portfolio baskets are incurring losses or eating up gains of others.
So, in the case of SIP (Systematic Invest Plan), since there are multiple payment investments with different investment prices operating at different time periods, calculating returns can be a bit tricky. Thus, returns through SIP are commonly calculated through XIRR.
For example, you invested ₹7000 for 5 years. After 5 years, you decided to redeem your investment amount i.e. ₹4,20,000. At an interest rate of 12%, the estimated return value is ₹1,57,405, while the total value one can expect is about ₹5,77,405. Thus, the resultant value is called IRR. Internal rate of return or IRR is a financial metric to figure out the profitability of the investments over time. So, basically, the higher the IRR, the more desirable an investment becomes.
Why is Extended Internal Rate of Return Important?
XIRR is a powerful method that helps to calculate returns even incase of irregular investment plans over time. It enables you to calculate the exact return amount instead of an estimated return based on compounding. You can use the tool to determine whether your investment portfolio is giving you likable returns or not.
Thus, for every SIP payment or liquidation, you can use the XIRR formula to figure out the overall value of your investment. By using the XIRR formula, we can assign particular dates to each cash flow (inflow & outflow) and calculate returns in an accurate manner.
How to Calculate XIRR?
Mathematically, XIRR return can be easily calculated using Microsoft Excel or Spreadsheet by using its inhibit formula.
| XIRR Formula in Excel = XIRR (value, dates, guess) |
- Values: Values representing a series of cash flows.
- Dates: Series of dates that correspond to first and future investment dates.
- Guess: The initial guess or estimated value of what IRR will be
Step-by-Step Process to Calculate XIRR in Excel
Let’s take an example,
SIP amount = ₹5000
Period of Investment Dates = 1st Jan, 2018 to 25th June, 2018
Redemption date = 1st July, 2018
| Investment Dates | SIPs (Systematic Investment Plan)/ Cash Flow (₹) |
| 01/01/2018 | -5000 |
| 02/03/2018 | 5000 |
| 03/01/2018 | 5000 |
| 04/11/2018 | 5000 |
| 05/01/2018 | 5000 |
| 06/25/2018 | 5000 |
| 07/01/2018 (Redemption Date) | 31,000 (Maturity Amount) |
- As the first step, enter the investment amounts in negative figures. Add investment values in positive figures with the current dates, i.e. (-5000 or 5000)
- In the next column, enter the date of the transaction.
- Enter the overall values of your holdings and the current date of the transaction.
- Now, enter the above-mentioned XIRR formula in excel i.e. = XIRR (value, dates, guess).
Now, you will get 2,700.73 as your result displayed in C11.
- Choose values to a series of cash flow that corresponds to a payment schedule in dates, the column of dates refers to the first date of investment; guess is optional.
(Note: Excel uses a value of 0.01, if you fail to enter any value.)
How to Calculate IRR in a Spreadsheet?
You can also calculate the IRR of the expected cash flows by using the same data table. Simply by entering the formula for calculating IRR in a spreadsheet or excel.
| IRR Formula = [=IRR (Cash Flow Amounts, Rate Guess)] |
Thus, now, enter the above-mentioned IRR formula i.e. = IRR (value, guess).
Now, after applying the formula, you will get 0.98 or 98.36% as your result displayed in C11.
XIRR vs Other Investment Metrics
XIRR vs IRR
Let’s review some of the basic differences between XIRR and IRR (Internal Rate of Return).
| Metric | XIRR | IRR |
| Definition | The extended internal rate of return for a series of cash flows that occur at irregular intervals | The internal rate of return for a series of cash flows that occur at regular intervals |
| Calculation | Calculates the discount rate that makes the net present value of cash flows from an investment equal to zero, accounting for irregular cash flows | Calculates the discount rate that makes the net present value of cash flows from an investment equal to zero, assuming regular cash flows |
| Use | Most appropriate for investments with irregular cash flows, such as private equity or real estate investments | Most appropriate for investments with regular cash flows, such as bonds or annuities |
| Limitations | Requires accurate cash flow data, may not account for timing of cash flows, may not be appropriate for all types of investments | May not be appropriate for investments with irregular cash flows, may not account for timing of cash flows in the same way as XIRR |
XIRR vs CAGR
Let’s begin to understand the difference between XIRR and CAGR.
| Metric | XIRR | CAGR |
| Definition | The extended internal rate of return for a series of cash flows that occur at irregular intervals | CAGR is a financial metric used to measure the compounded annual growth rate of an investment. |
| Calculation | Calculates the discount rate that makes the net present value of cash flows from an investment equal to zero, accounting for irregular cash flows. | Calculates the rate of return required for an investment to grow from its initial value to its final value over a specified period of time. |
| Purpose | Measures the annualized return of an investment with irregular cash flows. | Measures the annualized growth of an investment with a fixed rate of return. |
| Limitations | Requires accurate cash flow data, assumes same reinvestment rate, sensitive to small changes in data, & may not be appropriate for all types of investments. | Assumes constant growth over the entire period, does not account for volatility or fluctuations in returns. |
What are the Benefits of XIRR?
Read the following reasons to understand why the extended internal rate of return method is considered as a powerful tool for evaluating investments.
- Enables You to Compare their Options: The extended internal rate of return method allows smallcase investors to access the profitability of investments over the period of time.
- Calculate Portfolio Returns in One Go: You need to calculate the XIRR for each individual fund. Now, you can calculate the XIRR for your entire portfolio in one go.
- Accurate Return Calculation: The XIRR method is an apt indicator to calculate returns on multiple investments and withdrawals.
What are the Limitations of XIRR?
Just like its undeniable advantages, the XIRR return method also has some disadvantages. Let’s review some.
- Requires Accurate Cash Flow Data: XIRR requires accurate and complete cash flow data, including the date and amount of each cash flow. If the data is incomplete or inaccurate, it can affect the accuracy of the XIRR calculation.
- Sensitive to Small Changes in Data: XIRR is a sensitive metric that can be affected by even small changes in the cash flow data. This can make it difficult to compare different investments or make decisions based on the XIRR return alone.
- May Not be Appropriate for All Types of Investments: XIRR is most appropriate for investments with irregular cash flows, such as private equity or real estate investments. It may not be appropriate for investments with regular cash flows, such as bonds or annuities.
To Wrap it Up….
By understanding XIRR meaning, you can assess the potential for future returns and the risk involved in a particular fund. Also, you can make investment decisions based on your financial goals and risk tolerance.
Extended Internal Rate of Return is a promising investment opportunity for those looking to invest in a diversified portfolio of small-cap companies in India. Therefore, to gain exposure of diversified portfolios you can begin your portfolio investing journey with smallcase. smallcase provides you with various features such as automated rebalancing, tracking, and monitoring, which could help you make informed decisions and optimize their investment.
So, start your investment journey with smallcase today!
You can also check our guide on ‘How to invest in smallcase?’ for better understanding.
