### Excel XIRR Function

##### Associated Files Download Links

In this Article

*This Excel tutorial demonstrates how to use the Excel XIRR function in Excel to calculate the internal rate of return of a set of cash flows, with formula examples.*

## XIRR Function Description:

The XIRR Function Calculates the internal rate of return for a series of cash flows. The cash flows do not need to be periodic.

To use the XIRR Excel Worksheet Function, select a cell and type:

(Notice how the formula inputs appear)

### XIRR Function Syntax and Inputs:

1 |
=XIRR(values,dates,guess) |

**values** – The values argument consists of an array or a reference to a range of cells representing the series of cash flows. And the values argument must contain at least one negative and one positive value for the calculation.

**dates** – This argument refers to the array of dates at which the cash flows occur. Its length should be the same as the length of the values array. The first date should be the initial investment’s/loan’s date and subsequent dates of further payments must occur later than the first date.

**guess** – OPTIONAL: It’s an estimate of what the internal rate of return should be. If this argument is omitted then it assumes the default value of 0.1 or 10%.

**What Is Excel XIRR Function?**

XIRR stands for Extended Internal Rate of Return. The Excel XIRR function is used to calculate the return on investment in which the series of cash flows occur at specified dates. XIRR Function always returns an annualized IRR value regardless of the intervals of the cash flows.

**XIRR Formula**

The value of XIRR is calculated as the value of the *rate *that satisfies the following equation:

Where:

n = number of time periods

j = period number

rate = discount rate or interest rate

dj = the jth payment or the last payment

d1 = the 0^{th} payment

Pj = the jth payment or the last payment

**Example 1**

Consider you made an investment of $20,000 in 2019 and expects to receive profits in the next five years. The cash flows and their corresponding dates are mentioned below:

Here the initial investment is entered with a negative sign as this an outgoing payment and profits are represented with positive values. The guess argument was omitted, so the XIRR function operated on the default value of 0.1 or 10%.

The initial investment is entered with a negative sign in the XIRR function, as it represents outgoing cash, and its corresponding dates are

**Value** 1 = -$20,000

**Date** 1 = 2/1/2019

The value and the date of the 1^{st} year profit is

**Value** 2 = $2,000

**Date** 2 = 3/7/2020

The value and the date of the 2^{nd} year profit is

**Value** 3 = $3,000

**Date** 3 = 1/1/2021

The value and the date of the 3^{rd} year profit is

**Value** 4 = $4,500

**Date** 4 = 4/3/2022

The value and the date of the 4^{th} year profit is

**Value** 5 = $6,000

**Date** 5 = 6/5/2023

The value and the date of the 5^{th} year profit is

**Value** 6 = $7,500

**Date** 6 = 2/12/2024

The Internal Rate of Return for this investment is

**XIRR** = 3.84%

**Example 2**

Suppose, you started to invest in mutual funds with an initial investment of $5,000 in the first year. After seeing good results on your initial investment, you increased your investment by 5% every year, for the next nine years. After nine years, you received a total return of $90,000 on your investments.

Here, the first nine values of the Values argument are in negative because it shows payment going out from our account into mutual funds.

The amount required to start the investment is

**Value** 1 = -$5,000

**Date** 1 = 3/1/2010

The additional investment made over the period are mentioned below along with their dates:

**Value** 2 = -$5,250

**Date **2 = 1/1/2011

**Value** 3 = -$5,513

**Date **3 = 3/1/2012

**Value** 4 = -$5,788

**Date **4 = 4/3/2013

**Value** 5 = -$6,078

**Date **5 = 6/5/2014

**Value** 6 = -$6,381

**Date **6 = 2/12/2015

**Value **7 = -$6,700

**Date **7 = 6/3/2016

**Value **8 = -$7,036

**Date **8 = 7/30/2017

**Value **9 = -$7,387

**Date **9 = 9/2/2018

The total return we get after nine years of the investment is

**Value **10 = $90,000

**Date **10 = 12/1/2019

After calculation, the Internal Rate of Return you got on your initial and additional investments is

XIRR = 9.15%

## XIRR** in Google Sheets**

All of the above examples work exactly the same in Google Sheets as in Excel.

## Additional Notes

Use the XIRR Function to calculate the Internal Rate of Return of an investment.

Cash Flows do not need to be periodic. Instead, you must enter the corresponding dates along with the cash flows.

The result of the XIRR Function may appear as a decimal. Change the cell Number Formatting to Percentage to display the function result as a percentage:

## XIRR Examples in VBA

You can also use the XIRR function in VBA. Type:

`application.worksheetfunction.xirr(values,dates,guess)`

For the function arguments (values, etc.), you can either enter them directly into the function, or define variables to use instead.

Return to the List of all Functions in Excel