In this blog, we will explain the rationale for using the **XIRR **function over the **IRR function**.

Let’s first try and understand how the **IRR** function works.

**BASIC – IRR Function**

** 1. The IRR function’s syntax and usage****:**

The **IRR** function returns the internal rate of return for a series of cash flows represented by the numbers in values.

**For example**: We want to find out the internal rate of return (IRR) in the purchase/sale of a stock (in US$).

We must understand the syntax of the IRR function to find out the answer to our problem.

** ****Formula****: **= IRR (values, guess)

**Values:**These refer to the series of numbers for which the IRR needs to be calculated. It is important to note that there must be at least one negative and one positive value for the IRR to be computed. The cash flows must also happen at the same interval – annually**Guess**: It is a rough estimate of what the IRR could be according to the user (not used normally)

Now, we will try and ascertain how to complete the **IRR** function in this case.

**Values:**An array of values starting from (670.0) and ending at 780.0 are considered**Guess:**We don’t put any value here

As shown in the above figure, the internal rate of return (IRR) is **6.1%**

**BASIC – XIRR Function**

**2. The XIRR function’s syntax and usage****:**

The **XIRR **function returns the internal rate of return for a schedule of cash flows that is **not necessarily periodic**.

**For example**: We want to find out the internal rate of return of cash flow in the purchase/sale of a stock (in US$).

We must understand the syntax of the XIRR function to find out the answer to our problem.

** ****Formula****: **= XIRR (values, dates, guess)

**Values:**These refer to the series of numbers for which the IRR needs to be calculated. It is important to note that there must be at least one negative and one positive value for the IRR to be computed. The cash flows must also happen at different time intervals**Dates:**Schedule of cash flow dates (**not necessary in the same time interval)****Guess:**It is a rough estimate of what the XIRR could be according to the user (not used normally)

Now, we will try and ascertain how to complete the **XIRR **function in this case.

**Values:**An array of values starting from (670.0) and ending at 780.0 are considered**Dates:**The first date is 31^{st}December 2014 and the last date is 31^{st}March 2018 (**Note: Time intervals are different between various dates**)**Guess:**We don’t put any value here

As shown in the above figure, the XIRR is **7.5%**

**3. Rationale of using XIRR instead of IRR****:**

**IRR doesn’t work in case of different time intervals****:**It is not possible to find the IRR if the gap between the dates of scheduled cash flows is not same.

**[hr style=”1,2,3,4″ margin=”40px 0px 40px 0px”] **

This function is widely used in financial modelling.

**About us: **Cians Academy is a division of Cians Analytics and provides training on financial topics to those interested in building or honing their financial skills. The courses are especially relevant for people who want to work in the financial services industry or become conversant with financial analysis. Cians Academy is based in Gurgaon, India.

The courses will be highly beneficial for people looking to make a career in finance (CA/CFA/Commerce Graduates/Working Professionals).

- Want to become an expert in Financial Modeling – Enroll in our Investment Banking Course
- Learn MS Excel Online for Free – Yes, I wish to learn

### 1 Comment

Comments are closed.

## Mohit Gupta

WOW..

Never heard of XIRR before in theory …

Good concept to know