Investments involving several inflows and outflows need a return measure that accounts for how money moves over time. Internal rate of return, or IRR, and extended internal rate of return, or XIRRs, are two commonly used measures.
The central distinction in IRR vs XIRR is the treatment of time. IRR assumes equally spaced cash flows, while XIRR uses the actual date of every transaction. Choosing the appropriate measure can materially affect how the return from an investment or project is represented.
Key Takeaways
- IRR calculates the rate at which the net present value of regularly spaced cash flows becomes zero.
- XIRR considers actual transaction dates and is therefore suitable for irregular cash flows.
- IRR produces a return for the assumed cash-flow period, while XIRR produces an annualised return using a 365-day year.
- Both calculations require at least one negative cash flow and one positive cash flow.
- Neither measure accounts independently for investment scale, risk or liquidity, so the result should not be assessed alone.
What is the internal rate of return?
The internal rate of return is the discount rate at which the net present value of a series of cash inflows and outflows equals zero. It is generally used for cash flows that occur at regular intervals, such as monthly, quarterly or annually.
IRR produces a return for the interval represented by the cash-flow series. If the intervals are annual, the result represents an annual rate. If they are monthly, the result is a monthly rate unless it is separately annualised.
How IRR relates to discounted cash flow and NPV
Discounted cash flow analysis estimates the present value of future cash flows by applying a discount rate. It reflects the time value of money, under which the same amount can have a different value depending on when it is received.
Net present value, or NPV, is the difference between the present value of cash inflows and outflows. IRR is the discount rate at which that difference becomes zero.
IRR formula and how to use it
IRR is represented as
0 = Σₜ₌₀ⁿ [CFₜ ÷ (1 + r)ᵗ]
Where
- CFt is the cash flow during period t
- r is the internal rate of return
- t represents each equally spaced period
- n is the total number of periods
The initial investment is normally entered as a negative cash flow, while receipts are entered as positive cash flows. The rate is usually calculated through iteration because the equation cannot ordinarily be rearranged to solve directly for IRR.
What is XIRR?
The extended internal rate of return is a modified form of IRR that uses the actual date of each cash flow. It is suitable when investments, withdrawals or receipts occur at irregular intervals.
XIRR is commonly used to calculate mutual fund returns involving SIP instalments, additional investments and redemptions on different dates. It is a money-weighted rate of return because both the amount and timing of investor-controlled cash flows influence the result.
XIRR formula and its application
XIRR is represented conceptually as
0 = Σᵢ₌₀ⁿ [CFᵢ ÷ (1 + r)^((dᵢ − d₀) ÷ 365)]
Where
- CFi is the amount of each cash flow
- r is the extended internal rate of return
- di is the date of each cash flow
- d0 is the date of the first cash flow
- n is the number of cash flows
XIRR discounts each cash flow according to the number of days between its transaction date and the first cash-flow date. Microsoft Excel calculates XIRR on a 365-day basis.
IRR vs XIRR Key differences
IRR and XIRR use the same underlying principle but differ in how they account for the timing of cash flows
| Parameter | IRR | XIRR |
| Full form | Internal rate of return | Extended internal rate of return |
| Cash-flow intervals | Assumes equal intervals | Accommodates irregular intervals |
| Date input | Not required | Required for every cash flow |
| Treatment of time | Uses consecutive periods | Uses the exact number of days |
| Result | Return for the assumed period | Annualised return |
| Common applications | Projects with fixed periodic cash flows | SIPs, redemptions, real estate and staggered investments |
| Excel syntax | =IRR(values, [guess]) | =XIRR(values, dates, [guess]) |
The appropriate measure depends on the cash-flow schedule, not on which calculation produces the higher result.
CAGR vs XIRR What is the difference?
CAGR and XIRR both express annualised returns, but they apply to different investment patterns
| Parameter | CAGR | XIRR |
| Cash-flow pattern | One initial investment and one final value | Multiple investments, withdrawals or receipts |
| Transaction timing | Does not account for intermediate cash flows | Uses the actual date of every cash flow |
| Common application | Lumpsum investment held over a period | SIPs and portfolios with multiple transactions |
| Calculation basis | Beginning value, ending value and tenure | Amount and date of every cash flow |
CAGR may be suitable for a lumpsum investment with no intermediate transactions. XIRR is more relevant when money is invested or withdrawn on multiple dates.
IRR vs XIRR example
A simple cash-flow example shows why IRR and XIRR may produce different results when transaction dates are not evenly spaced
| Transaction | Amount | Date |
| Initial investment | ₹-1,00,000 | 10 April 2024 |
| First receipt | ₹40,000 | 10 April 2025 |
| Final receipt | ₹70,000 | 5 November 2025 |
IRR treats the entries as occurring at equal intervals because it considers their sequence but not their actual dates. Based on this assumption, the cash flows produce an IRR of approximately 6.02% per assumed period.
XIRR uses the exact transaction dates and produces an annualised return of approximately 7.25%. The results differ because the final receipt occurs 209 days after the first receipt rather than after another complete year.
Since the transactions are not evenly spaced, XIRR provides the more relevant measure for this cash-flow pattern.
The figures shown are for illustrative purpose only.
IRR vs XIRR in Excel
Investments and other outflows should be entered as negative values, while receipts and other inflows should be entered as positive values.
How to calculate IRR in Excel
Enter regularly spaced cash flows in consecutive cells and use
=IRR(values, [guess])
For cash flows in cells B2 to B6
=IRR(B2B6)
The optional guess gives Excel a starting estimate for its iterative calculation. If the guess is omitted, Excel begins with 10%.
How to calculate XIRR in Excel
Enter the cash flows in one column and their corresponding dates in another. Then use
=XIRR(values, dates, [guess])
For cash flows in B2 to B6 and dates in A2 to A6
=XIRR(B2B6,A2A6)
The values and dates ranges must contain the same number of entries. At least one cash flow must be positive and one must be negative.
Source Microsoft Support, IRR and XIRR functions.
How to interpret IRR and XIRR results
The resulting percentage must be interpreted according to the cash-flow intervals and calculation method used
- An IRR of 10% represents a 10% return for each assumed cash-flow period. It represents an annual rate only when the intervals are annual.
- An XIRR of 10% represents a money-weighted annualised return based on the amounts and actual transaction dates entered.
- A higher IRR or XIRR does not automatically indicate a more suitable investment because neither measure independently accounts for risk, liquidity or absolute value created.
- The result describes the cash-flow data used in the calculation and does not assure the same return in future.
Practical applications of IRR and XIRR
The appropriate measure depends on whether the cash flows follow a fixed schedule or occur on different dates
| Application | Suitable measure | Reason |
| Project with fixed annual cash flows | IRR | Cash flows occur at equal intervals |
| Mutual fund SIP | XIRR | Every instalment has a separate transaction date |
| Portfolio with additional investments and redemptions | XIRR | Cash flows occur at irregular intervals |
| Real estate purchase and sale | XIRR | Payments and receipts may occur on different dates |
| Structured investment with fixed periodic payments | IRR | The cash-flow schedule is regular |
XIRR may provide a more representative annualised return when dates are irregular because it reflects how long each amount remained invested. It is not inherently more accurate when the cash flows are evenly spaced.
How to calculate XIRR for an ongoing mutual fund investment
An ongoing mutual fund investment may not have a final redemption amount. In this case, the investment’s market value on the chosen calculation date is entered as the final positive cash flow.
For an SIP investment
- Enter each SIP instalment as a negative cash flow on its transaction date.
- Enter any redemption or cash distribution received as a positive cash flow.
- Enter the investment’s current market value as a positive cash flow on the valuation date.
- Apply the XIRR function to the complete cash-flow and date ranges.
Leaving out the current market value would make the cash-flow series incomplete and prevent the calculation from representing the investment’s position on the valuation date.
Source Microsoft Support, XIRR function.
Is IRR the same as ROI?
IRR and return on investment, or ROI, measure returns differently. ROI compares the total gain or loss with the amount invested, while IRR considers a series of regularly spaced cash flows and the time periods between them.
ROI does not generally account for the timing of individual transactions. IRR incorporates the sequence of cash flows and expresses the result for the assumed period.
IRR vs XIRR Limitations, pitfalls and best practices
Understanding the limitations and correct use of IRR vs XIRR can help prevent misleading comparisons and calculation errors
Limitations of IRR and XIRR
Although IRR and XIRR measure cash-flow-based returns, neither provides a complete assessment of an investment on its own
- IRR assumes equal intervals and may misrepresent irregular cash flows.
- XIRR requires accurate dates for every transaction.
- Both calculations may produce more than one possible result when cash flows change between positive and negative several times.
- Neither measure shows the absolute value created by an investment.
- The results do not independently account for investment size, market risk or liquidity.
- Excel may return an error if its iterative calculation cannot find a result.
Common pitfalls
Errors in cash-flow amounts, dates or interpretation can materially affect IRR and XIRR calculations
- Using IRR when cash flows occur on irregular dates.
- Entering every cash flow with the same sign.
- Omitting transactions or using incorrect dates.
- Treating a higher result as proof that an investment is superior.
- Comparing investments of different sizes or durations without additional context.
Best practices
The following practices can improve the accuracy and interpretation of IRR and XIRR results
- Record every inflow and outflow using the correct sign.
- Use IRR for equally spaced cash flows and XIRR for actual transaction dates.
- Verify the cash-flow amounts and dates when a result appears unusual.
- Use NPV and other relevant measures alongside IRR or XIRR.
- Consider investment scale, duration, risk and liquidity before comparing results.
When to choose IRR or XIRR
Use IRR when cash flows occur at fixed and equal intervals. Use XIRR when their dates vary or when each transaction needs to be assessed according to the actual time for which the money remained invested.
For SIPs and mutual fund portfolios with multiple purchases or redemptions, XIRR is generally the more relevant measure. IRR may be sufficient for a project with fixed monthly, quarterly or annual cash flows.
Conclusion
IRR and XIRR both calculate the rate at which the present value of cash inflows equals the present value of cash outflows. IRR assumes equal periods, while XIRR uses actual transaction dates. The cash-flow pattern determines which measure is appropriate. The result should then be considered alongside NPV, investment size, duration, risk and liquidity.
FAQs
What is the difference between IRR and XIRR?
IRR assumes equal intervals between cash flows, while XIRR uses the actual date of each transaction. XIRR is therefore more suitable for irregular investments such as SIPs, additional purchases and staggered withdrawals.
When should I use XIRR instead of IRR?
Use XIRR when investments, withdrawals or receipts occur on different dates or at unequal intervals. Use IRR when cash flows are evenly spaced.
Can XIRR handle negative cash flows?
Yes. XIRR requires at least one negative cash flow and one positive cash flow. Investments and other outflows are normally entered as negative values, while redemptions and other inflows are entered as positive values.
How do I calculate XIRR in Excel?
Enter the cash flows in one column and their corresponding dates in another, then use =XIRR(values, dates, [guess]). The two ranges must contain the same number of entries.
Is XIRR always more accurate than IRR?
No. XIRR is more representative when cash flows occur at irregular intervals because it uses actual dates. For equally spaced cash flows, IRR can provide an appropriate result.
Why are my IRR and XIRR results different?
IRR and XIRR differ when the actual gaps between transactions are not equal. IRR treats them as consecutive periods, while XIRR calculates the return using the exact transaction dates.
Why does Excel show a #NUM! error for IRR or XIRR?
Excel may return #NUM! if the cash flows do not include at least one positive and one negative value or if the iterative calculation cannot find a result. Incorrect date ranges or a starting date later than another XIRR date may also cause an error.
Can IRR or XIRR be negative?
Yes. A negative result indicates that the cash inflows are insufficient to recover the outflows at a zero or positive rate over the measured period.
Is IRR the same as ROI?
No. ROI measures the total gain or loss relative to the initial investment, while IRR considers a series of regularly spaced cash flows and the periods between them.
