Excel XIRR Function

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.

Excel Function XIRR

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:
xirr formula syntax

(Notice how the formula inputs appear)

XIRR Function Syntax and Inputs:

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:

xirr formula

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 0th 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 1st year profit is

Value 2 = $2,000

Date 2 = 3/7/2020

The value and the date of the 2nd year profit is

Value 3 = $3,000

Date 3 = 1/1/2021

The value and the date of the 3rd year profit is

Value 4 = $4,500

Date 4 = 4/3/2022

The value and the date of the 4th year profit is

Value 5 = $6,000

Date 5 = 6/5/2023

The value and the date of the 5th year profit is

Value 6 = $7,500

Date 6 = 2/12/2024

Excel Function XIRR Example 1

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

Excel Function XIRR Example 2

 

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.

XIRR Function in Google Sheets

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:
percentage formatting excel shortcut

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