XIRR vs IRR

What is XIRR vs IRR in Excel?

Understanding the distinction between IRR and XIRR functions in Excel is important for accurate financial analysis. While both are used to compute the rate of return for investments or projects, they operate differently and may yield different results from the same cash flow dataset.

The traditional IRR function in Excel simplifies calculations by assuming uniform time periods for cash flows. However, this assumption often doesn't align with real-world scenarios, particularly when there's an upfront investment or irregular time intervals between cash flows, which is typical in business contexts.

On the other hand, XIRR offers a more precise approach by allowing users to assign specific dates to individual cash flows. This flexibility better reflects the actual timing of cash inflows and outflows, resulting in a more accurate rate of return calculation.

What Is XIRR?

The XIRR function is classified as an Excel financial function. XIRR stands for "Extended Internal Rate of Return." It is a method of calculating the ROI of multiple trades made at different times.

It is a good function for calculating returns when cash flows (investments or returns) are spread over time. The XIRR function calculates the Extended Internal Rate of Return for a collection of cash flows with irregular time intervals.

It calculates the rate of return by considering the dates and corresponding cash flows, even when irregular. The advantage of using Excel's XIRR function is that you can accurately model these time-varying cash flows.

The function is an extended internal rate of return that considers cash flows, discount rates, and corresponding data to measure the return rate accurately.

In financial modeling, the characteristic is beneficial in finding out the cost of funding or perception of the feasibility of a challenge that does not typically have periodic money flows.

It helps us to identify or calculate the ROI price; therefore, it is often used to evaluate alternatives and decide between two or more additional investments.

How to calculate XIRR

The syntax for the function is:

XIRR(Values,Dates,[guess])

Where

• Values: Represent the cash flows and should include both positive and negative numbers to indicate inflows and outflows, respectively
• Dates (mandatory parameter): A date list matching the supplied values. Since the following dates are future dates of incoming payments or income and the first date is the start date, subsequent dates should be later than the first date.
• [guess] (viable argument): This parameter represents an initial guess or estimate of the Internal Rate of Return (IRR). If omitted, Excel uses the default value of 10%

Example of XIRR

Assuming you invest £4,000, £9,000, £5,000, £4,000, and £6,500 SIP over 5 years and earn £53,000 after 5 years, your ROI is 22%. The resulting value is called IRR. This concept determines how much money an investment will earn if its costs are evenly distributed over time.

However, assets are generally not evenly distributed with mutual funds. Mutual funds tend to invest and redeem at irregular intervals. Compute the rate of return of any investments or policy like SIP or LIC.

We have invested the amount in policy instruments at irregular intervals on different dates. Now, we want to know the return on the invested amount, so here we create a table and then solve the extended rate of return. Let's see

Now, open an Excel sheet to compute the XIRR. The steps are given below

• In column A, enter the dates of transactions
• In column B, enter the policy amounts as a cash flow
• Now type the formula of XIRR and select the policy instrument from B4 to B13; the dates are columns from A4 to A13
• After the above steps, multiply it by 100 and get the returned output

The resultant output is 10.90%.

What is Internal Rate of Return (IRR)?

IRR is used in financial analysis to calculate the rate of return that makes the net present value of cash flows from an investment equal to zero. In the Discounted Cash Flow (DCF) modeling analysis, the IRR is the discount rate at which the NPV of all cash flows is zero.

Projected cash flows for a project or investment are provided in the IRR calculation and have a net present value of zero. IRR is the initial cash investment equal to the present value of future cash flows from the investment.

The internal rate of return is often compared to a company's capital or equity cost. If the IRR is greater than or equal to the cost of capital, the company considers the project a worthwhile investment.

The value paid = present value of future cash flows; hence, net present value = zero.

Once the internal rate of return is decided, it is usually compared to the company's critical rate or cost of equity. If the IRR exceeds the cost of capital, the project may be considered for investment, but additional factors are typically considered before making a decision.

How to calculate IRR

The Internal Rate of Return (IRR) is a financial metric used to evaluate the profitability of investments by determining the discount rate at which the net present value (NPV) of cash flows equals zero.

The internal rate of return is a given direction for evaluating whether a project or investment should proceed. The IRR is also useful for companies evaluating stock buyback programs.

The manual formula for IRR is as follows:

IRR = R1 + [(NPV1 * (R2 - R1))/ (NPV1 - NPV2)]

Here:

• R1 = Lower discount rate
• R2 = Higher discount rate
• NPV1 = Higher Net Present Value
• NPV2 = Lower Net Present Value

The syntax for the function is:

IRR = IRR(Values,[guess])

Where

• Values ​​(mandatory parameter): This is an array of values representing the cash flow over time. It should include both initial investments and subsequent net income or cash flow amounts. The values can reference a range of cells containing these cash flow amounts.
• [guess] (optional parameter): This is the user's initial guess for the IRR calculation. It provides a starting point since the IRR function may have multiple solutions. If omitted, the function defaults to 0.1 (or 10%)

Example of IRR

Calculate IRR for monthly cash flow. Suppose you have been in business for ten months and now want to calculate the rate of return on cash flow. Finding IRR in Excel is very simple.

Enter your initial investment in a cell (B1 in this case). Since this is a payment, it must be negative.

Enter subsequent cash flows in the cells below or to the right of the initial investment (B1:B11 in this example). Because this money comes from sales, it is entered as a positive number.

We are now ready to calculate the project's IRR. = IRR(B1:B11,10%) The cash flows below for an initial investment.

Using the IRR Excel function, we get the internal rate of return of 11%.

Example 2

Here, we take another example of XIRR and IRR, both of the same amount. From this example, we can better understand the use of XIRR over IRR.

XIRR Vs. IRR Excel Financial Model

Understanding why we use XIRR versus IRR is critical through financial modeling and valuation. Using the simple IRR function in Excel can be misleading because it assumes that all periods of a cash flow series are equal.

So here is the distinction between XIRR and IRR to understand it concisely.

Researched and authored by Lekhika Sharma | Linkedin

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• Nonlinear Regression
• Real Estate Financial Analysis
• Scenario Analysis
• Scenario Analysis vs Sensitivity Analysis
• Sensitivity analysis: What is it?