What is XIRR- Definition, Formula & Calculation
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. 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?
In this blog, we will delve into what XIRR is and its importance into how to calculate your overall ROI.
What is XIRR?
XIRR stands for Extended Internal Rate of Return. It is a financial metric used to calculate the annualised rate of return of an investment based on the timing and amount of cash flow.
Thus, this calculation is generally used to calculate returns of SIP (Systematic Invest Plan). For instance, you start an SIP for 12 months and skip 2 months, and then resume investing. Furthermore, you might also keep changing the investment amount at regular intervals. Therefore, in this case, XIRR will be used to calculate the returns of multiple transactions that were executed at different times.
Why is XIRR 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 on investment instead of an estimated return based on compounding. You can use this to determine whether your investment portfolio is giving you expected 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 in Mutual Funds?
The XIRR calculation formula is as follows:
XIRR = (NPV(Cash Flows, r) / Initial Investment) * 100
Where
- NPV: Net Asset Value
- r: Rate of Return
- Cash Flow: Series of investments & withdrawals over time
How to Calculate XIRR in Mutual Funds Using Excel?
Mathematically, XIRR return can be easily calculated using Microsoft Excel or Spreadsheet by using the following formula to execute the XIRR function in Excel.
XIRR Formula in Excel = XIRR (value, dates, guess)
Where
- 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 = Rs. 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) |
In the above table, the cash flows occur at irregular intervals.
- 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 Does XIRR in Mutual Fund Work?
XIRR calculates the annualised return on investments by considering the cash inflows and outflows at different time periods. Let’s understand how XIRR works with an example.
Suppose you are investing Rs. 5,000 on a monthly basis in a mutual fund plan for 5 years. Considering the volatility, your total value at the end of 5 years is Rs. 3,00,000
In this scenario, the initial contribution of Rs. 5,000 was invested for 5 months or 60 months. However, the annual return for the first month’s contribution will slightly differ because it was invested for the longest duration. As each contribution was made at a different time, the CAGR for every contribution can differ. As a result, calculating the CAGR of each contribution in a mutual fund scheme can be quite difficult.
Hence, to simplify the process, the CAGR of every contribution is amalgamated and adjusted to a common one. Thus, this adjusted CAGR is considered as XIRR in a mutual fund.
What is IRR & Why is it Important?
Internal Rate of Return or IRR is a financial metric used to evaluate the profitability of a potential investment. It is a discount rate that makes the net present value (NPV) of future cash flows equals zero. It is calculated using the same NPV concept, except it sets the NPV equal to zero. IRR is useful for corporations as it evaluates the stock buyback programs. Additionally, it is a key metric used to compare the profitability of establishing new operations with that of expanding existing operations.
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 enter 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 the 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 annualised return of an investment with irregular cash flows. | Measures the annualised growth of an investment with a fixed rate of return. |
| Limitations | Requires accurate cash flow data, assumes the 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, and does not account for volatility or fluctuations in returns. |
What are the Benefits of XIRR?
Here is a list of benefits of the extended internal rate of return method:
- Accurate Evaluation: The XIRR Calculator for SIP offers a detailed measure of an investment’s performance, taking into account the timing and amount of cash flows.
- Enables You to Compare their Options: The XIRR calculator for SIP allows investors to compare returns of multiple investment options over a period of time. This gives investors an overall understanding of their investments along with the alternatives.
- Manages Irregular Cash Flows: It can manage irregular cash flows. Thus, this makes the XIRR return calculator suitable for various financial scenarios like retirement savings, loans, etc.
- Adjustable Inputs: You can adjust the guess values and cash flow amounts without any hassle. This ensures a more realistic result with the help of the XIRR return calculator.
- Better Decision Making: By offering a more precise measure of investment returns, XIRR enables investors to make more informed decisions about their investment strategies, asset allocation, and financial goals.
What are the Limitations of XIRR?
Here is a list of limitations of the XIRR return method:
- 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 which can be challenging.
- Sensitive to Small Changes in Data: XIRR 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 better suited for investments with irregular cash flows, such as private equity or real estate investments. However, it may not be appropriate for investments with regular cash flows, such as bonds or annuities.
To Wrap It Up…
To conclude, with the help of the XIRR calculator you can assess the potential for future returns even at irregular intervals. By providing a precise measure of the annualised return, it enables informed decision-making and effective portfolio management. So whether you’re evaluating the performance of a Systematic Investment Plan (SIP), a National Pension System (NPS) account, or any other investment at irregular time intervals with multiple cash flows, the XIRR calculator can be a suitable option.
Frequently Asked Questions on XIRR
XIRR, or extended internal rate of return, is a financial metric used to calculate the annualised rate of return for investments with irregular cash flows. Unlike simpler return metrics such as ROI (Return on Investment) or CAGR (Compound Annual Growth Rate), which assume regular intervals between cash flows, XIRR accounts for different time periods based on cash flow transactions.
A good XIRR in mutual funds depends on factors like market condition, fund performance, risk tolerance, investor goals, and more. A good XIRR in mutual funds should be higher than average market return.
XIRR can be used to calculate returns on investments made in the National Pension Scheme (NPS). In other words, this method is used to calculate the annualised effective compounded return rate in a PRAN (Permanent Retirement Account Number) account.
