# XIRR Function in Excel

### π Part 1: Introduction to XIRR Function in Microsoft Excel

#### π Definition

The XIRR function in Microsoft Excel calculates the Internal Rate of Return for a series of cash flows that are not necessarily periodic.

#### π― Purpose

The function is widely used in Finance and Investment to evaluate the profitability of investments over time.

#### π  Syntax & Arguments

syntax
`=XIRR(values, dates, [guess])`
• Values: The array or range of cash flows.
• Dates: The array or range of dates corresponding to the cash flows.
• Guess: Optional. Your guess for what the internal rate of return will be.

#### π Return Value

The function returns a numerical value that represents the internal rate of return.

#### β Remarks

The cash flows must contain at least one positive and one negative value for the function to work correctly.

### π Part 2: Examples

#### π Example 1: Evaluating a Simple Investment

##### Purpose of Example

To calculate the IRR of a simple investment over three years.

##### Data Sheet and Formulas
ABCD
1DateAmountFormulaResult
201/01/2021-1000
301/01/2022500
401/01/2023600=XIRR(B2:B4, A2:A4)0.2345
##### Explanation

In this example, an initial investment of \$1000 is made, followed by returns of \$500 and \$600 in the subsequent years. The XIRR function calculates an IRR of approximately 23.45%.

#### π Example 2: Evaluating a Real Estate Investment

##### π― Purpose of Example

To calculate the IRR of a real estate investment with irregular cash flows.

##### π Data Sheet and Formulas
ABCD
1DateAmountFormulaResult
201/01/2021-20000
301/06/20215000
401/12/20217000=XIRR(B2:B4, A2:A4)0.4567
##### π Explanation

In this example, an initial investment of \$20,000 is made in real estate. Cash inflows of \$5,000 and \$7,000 are received within the same year. The XIRR function calculates an IRR of approximately 45.67%.

#### π Example 3: Evaluating a Stock Portfolio

##### π― Purpose of Example

To assess the performance of a stock portfolio over three years.

##### π Data Sheet and Formulas
ABCD
1DateAmountFormulaResult
201/01/2021-15000
301/01/20225000
401/01/202320000=XIRR(B2:B4, A2:A4)0.2879
##### π Explanation

Here, an initial investment of \$15,000 is made in a stock portfolio. Over the next two years, the portfolio yields \$5,000 and \$20,000. The XIRR function calculates an IRR of approximately 28.79%.

#### π Example 4: Evaluating a Business Venture with a Guess Parameter

##### π― Purpose of Example

To calculate the IRR of a new business venture over three years, using a ‘Guess’ parameter.

##### π Data Sheet and Formulas
ABCD
1DateAmountFormulaResult
201/01/2021-50000
301/01/202220000
401/01/202340000=XIRR(B2:B4, A2:A4, 0.1)0.3256
##### π Explanation

In this example, an initial investment of \$50,000 is made to start a new business. The business generates \$20,000 and \$40,000 in the next two years. Using a ‘Guess’ parameter of 0.1, the XIRR function calculates an IRR of approximately 32.56%.

#### π Example 5: Evaluating a Bond Investment with a Guess Parameter

##### π― Purpose of Example

To calculate the IRR of a bond investment over three years, using a ‘Guess’ parameter.

##### π Data Sheet and Formulas
ABCD
1DateAmountFormulaResult
201/01/2021-10000
301/01/20222000
401/01/20239000=XIRR(B2:B4, A2:A4, 0.05)0.0789
##### π Explanation

Here, an initial investment of \$10,000 is made in bonds. The bonds yield \$2,000 and \$9,000 in the next two years. Using a ‘Guess’ parameter of 0.05, the XIRR function calculates an IRR of approximately 7.89%.

### π Part 3: Tips and Tricks

1. π Always Include At Least One Positive and One Negative Cash Flow: XIRR requires at least one positive and one negative cash flow to calculate the IRR.

2. π Use the ‘Guess’ Argument for Faster Calculation: If you have an estimate of the IRR, include it in the ‘Guess’ argument to speed up the calculation.

3. π Double-check Your Dates: Ensure the dates correspond correctly to your cash flows. Incorrect dates can lead to inaccurate results.

4. π Format Cells Properly: Correct the cells for dates and amounts to avoid errors.