Excel XIRR Function
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:
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.
The value of XIRR is calculated as the value of the rate that satisfies the following equation:
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
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
The Internal Rate of Return for this investment is
XIRR = 3.84%
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.
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:
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